Question

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

Answer

		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

Answer 1

	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

Please please Upvote if you like the answer.