Question

Answer Part B using excel and show equations that were used. explain if the problem is suffiecent or not.

You would like to construct an aggregate production plan for four quarters of 2021. Product name: TRX3010 Cost of regular production = 50 $/u Cost of overtime production = 65 $/u Inventory holding cost = 5 $/u/qtr Cost of increasing production = 40 $/u Cost of decreasing production = 45 $/u Previous quarter's regular production = 4200 u Beginning inventory level = 250 u The forecasted demand for the next four quarters are: 3700, 4000, 2600, and 3900 units. PART A: You want to use chase demand strategy without overtime production. You will keep inventory as low as possible but it must be at least 260 units per quarter. Stockouts are not allowed. In other words, quarterly demand must always be satisfied. You must perform all necessary calculation and summarize results in tabular form. You must show clearly the regular production, overtime production, inventory, changes in regular production and costs. Briefly explanation is sufficent. PART B: You want to set the quarterly regular production constant at 3200 units. When needed, you will use overtime production. You will keep inventory as low as possible but it must be at least 260 units per quarter. Stockouts are not allowed. In other words, quarterly demand must always be satisfied. You must perform all necessary calculation and summarize results in tabular form. You must show clearly the regular production, overtime production, inventory, changes in regular production and costs. Briefly explanation is sufficent.

Answer 1

As required, we solve part B as shown below:

Parameter Regular Overtime Ending Inventory Quarter Demand (D) Production (RP) Production (OCP) El(t) = El(t-1) + RP(t) +OCP - D(t) Increase Decrease 4200 250 1 3,700 3,200 510 260 0 1,000 2 4,000 3,200 800 260 0 0 3 2,600 3,200 0 860 0 0 4 3,900 3,200 100 260 0 0 Inventory holding cost Regular Production cost Overtime Production cost Cost of increasing production Cost of decreasing production Beginning inventory Total Increase cost Total Inventory Holding Cost Total Production cost Total Overtime Cost Total Decrease Cost Total Cost Value Formula / Symbol h $ 5 PC $ 50 OPC $ 65 HC $ 40 FC $ 45 BI 2501 THC = Increase *HC $ - IH = Total Ending Inventory * h $ 8,200 TPC = Total RP * PC $6,40,000 TOC = Total OCP * OPC $ 91,650 TDC = Total Decrease * FC $ 45,000 TC $ 7,84,850 Total 14,200 12,800 1410 1,640 0 1000

The above solution in the form of formulas is shown below for better understanding and reference:

A B C J - K Parameter Increase Decrease 1 E F G H Regular Overtime Quarter Demand (D) Ending Inventory Production (RP) Production (OCP) El(t) = El(t-1) + RP(t) +OCP - D(t) 4200 =C7 1 3700 3200 1=250+260 =12+G3+H3-F3 2 4000 3200 =F4-G4 =13+G4+H4-F4 3 2600 3200 0 1=14+G5+H5-F5 4 3900 3200 100 =15+G6+H6-F6 0 =G3-G4 =G4-G5 =G5-G6 =G2-G3 =G3-G4 =G4-G5 =G5-G6 2 Inventory holding cost 3 Regular Production cost 4 Overtime Production cost 5 Cost of increasing production 6 Cost of decreasing production 7 Beginning inventory 8 Total Increase cost 9 Total Inventory Holding Cost 10 Total Production cost 11 Total Overtime Cost 12 Total Decrease Cost 13 Total Cost Value Formula / Symbol h 5 PC 50 OPC 65 HC 40 FC 45 BI 250 THC = Increase * HC =J9*C5 IH = Total Ending Inventory * h =19*C2 TPC = Total RP * PC =G9*C3 TOC = Total OCP * OPC =H9*C4 TDC = Total Decrease * FC =K9*C6 TC =SUM(C8:C12) Total =SUM(F3:F8) =SUM(G3:68) =SUM(H3:48) =SUM(13:18) =SUM(13:18) =SUM(K3:K8)

As seen from above, we summarize the answer as:

Total Increase cost = 0

Total Decrease cost = $45,000

Total Inventory Holding cost = $8200

Total Production cost = $640,000

Total Overtime cost = $91,650

Total Cost = Total Increase cost + Total Decrease cost + Total Inventory Holding cost + Total Production cost + Total Overtime cost = 0 + 45000 + 8200 + 640000 + 91650 = $784,850

-----------------------------------------------------------------------------------------------------------------------

In case of any doubt, please ask through the comment section before Upvote/downvote.

If you liked the answer, please give an upvote. This will be quite encouraging for me, thank-you!!!