PLS GIVE EXCEL SOLUTIONS

The last spreadsheet shows the effect of reducing the holding cost rate to .22 or 22%. Simply change the holding cost rate and the new solution appears as shown.

American Technology Corporation can save $487.80 per year by implementing both options and taking advantage of the 10% discount and ordering once a year.

	A	B	C	D	E
1	American Technology Corporation	EOQ	2% disc.	5% disc.	10% disc.
2	Using 22% rate
3
4	Annual Demand	1600	1600	1600	1600
5	Ordering Cost	25	25	25	25
6	Holding Cost Rate	0.22	0.22	0.22	0.22
7	Cost per item	8	7.84	7.6	7.2
8	Days per year	320	320	320	320
9	Lead-time	6	6	6	6
10	Required minimum Q	0	400	800	1600
11	Current total cost	13300	13300	13300	13300
12
13	Unit holding cost	1.76	1.7248	1.672	1.584
14	Minimum cost EOQ	213.2007	215.3652	218.7393	224.7333
15	Order quantity to use	213.2007	400	800	1600
16
17	Annual holding cost	187.6166	344.96	668.8	1267.2
18	Annual ordering cost	187.6166	100	50	25
19	Annual item cost	12800	12544	12160	11520
20	Annual total cost	13175.23	12988.96	12878.8	12812.2
21
22	Savings over base case	124.7667	311.04	421.2	487.8
23
24	Cycle time days	42.64014	80	160	320
25	Reorder point	30	30	30	30
26	Average inventory	106.6004	200	400	800
27	Number of orders/year	7.504665	4	2	1