Question

# Kindly, explaining is needed via solving it manually and in excel : Solve this Aggregate Planning...

Kindly, explaining is needed via solving it manually and in excel :

Solve this Aggregate Planning Problem by minimizing the cost of mathcing the capacity vaious options in various periods to the future demand?

Find inventory cost, regular time cost, overtime cosr and subcontract cost, and the total cost?

 Sales Period Mar Apr May Demand 700 700 1100 Capacity: Regular Time 700 700 700 OverTime 100 100 100 Subcontracting 200 200 100 Begening inventory is zero 0
 Cost Regular Time \$50 per tire OverTime \$60 per tire Subcontracting \$80 per tire Carrying Cost \$5 per tire per month

 Demand Supply From Period 1 ( Mar) Period 2 ( Apr) Period 3 ( may) Unused Capacity ( Dummy) Beginning Inventory 0 Period 1 Regular Time 700 200 OverTime Subcontracting Period 2 Regular Time OverTime Subcontracting Period 3 Regular Time OverTime Subcontracting Total Demand
 PERIOD 1 PERIOD 3 PERIOD 3 Inventoy Cost Regular Time Cost Overtime Cost Subcontracting Total

 Period 1 Period 2 Period 3 Excess Capacity Row Total Beginning inventory 0 0 0 0 0 Period 1 reg time 700 0 300 0 1000 Period 1 overtime 0 0 0 100 100 Period 1 subcontracting 0 0 0 200 200 Period 2 reg time 0 700 0 0 700 Period 2 overtime 0 0 0 100 100 Period 2 subcontracting 0 0 0 200 200 Period 3 reg time 0 0 700 0 700 Period 3 overtime 0 0 100 0 100 Period 3 subcontracting 0 0 0 100 100 Column Total 700 700 1100 700 3200

* Inventory Carring Cost = 300* (5+5)

= 3000

* Regular Cost = 700*50+300*50+700*50+700*50

= 1,20,000

* Overtime Cost = 100 *60

= 6000

* Subcontracting Cost = 0

Total Cost = 3000+1,20,000 +6000+ 0

=1,29,000