Excel Online Activity: Aggregate Planning

$-$

$Level$ Production

Consider the situation faced $by$ Golden Beverages, a producer $of$ two major products

$-$

$Old$ Fashioned and Foamy Delite root beers. Golden Beverages operates $as$ a continuous flow factory and must

plan future production for a demand forecast that fluctuates quite a bit over the year, with seasonal peaks $in$ the summer and winter holiday season. How should Golden Beverages plan its overall

production for the next

$1$

$2$

$monthsin$ the face $of$ such fluctuating demand $if$ the level demand strategy $is$ applied? The data has been collected $in$ the Microsoft Excel Online file below. Open the

spreadsheet and perform the required analysis $to$ answer the questions below.

Open spreadsheet

Questions

What $is$ the average monthly demand? Round your answer $to$ two decimal places.

barrels

What $is$ the maximum monthly ending inventory? $Ifan$ amount $is$ zero, enter

$"$

$0$

$"$

$.$

$Round$ your answer $to$ the nearest whole number.

barrels

What are the costs associated with level demand production plan? Round your answers $to$ the nearest dollar.

What $is$ the total cost? Round your answer $to$ the nearest dollar.Aggregate Planning: Level Production Strategy

Production cost $(\frac{\text{\$}}{u}$nit$)$ $$69\mathrm{.}00$

Inventory holding cost $(\frac{\text{\$}}{u}$nit$)$ $$1\mathrm{.}40$

Lost sales cost $(\frac{\text{\$}}{u}$nit$)$ $$86\mathrm{.}00$

Overtime cost $(\frac{\text{\$}}{u}$nit$)$ $$6\mathrm{.}20$

Undertime cost $(\frac{\text{\$}}{u}$nit$)$ $$3\mathrm{.}40$

Rate change cost $(\frac{\text{\$}}{u}$nit$)$ $$5\mathrm{.}40$

Normal production rate $(units)2,300$

Ending inventory $(previous$ Dec.$)1,200$

Cumulative Formulas Cumulative

Cumulative Product Ending Lost Cumulative Product Ending Lost

Month Demand Demand Production Availability Inventory Sales Month Demand Demand Production Availability Inventory Sales

January $1,600$ January $1,600$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

February $1,000$ February $1,000$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

March $2,000$ March $2,000$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

April $2,900$ April $2,900$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

May $3,200$ May $3,200$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

June $3,600$ June $3,600$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

July $3,700$ July $3,700$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

August $3,600$ August $3,600$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

September $2,700$ September $2,700$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

October $1,800$ October $1,800$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

November $2,600$ November $2,600$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

December $3,000$ December $3,000$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

Average Maximum Average #$\frac{N}{A}$ Maximum #$\frac{N}{A}$

Production Inventory Lost Sales Overtime Undertime Rate Change Production Inventory Lost Sales Overtime Undertime Rate Change

Month Cost Cost Cost Cost Cost Cost Month Cost Cost Cost Cost Cost Cost

January January #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

February February #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

March March #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

April April #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

May May #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

June June #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

July July #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

August August #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

September September #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

October October #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

November November #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

December December #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

Totals Totals #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$ #$\frac{N}{A}$

Total cost Total cost #$\frac{N}{A}$