please answer with excel model

47. Shoemakers of America forecasts the following demand for the next six months: 5000 pairs in month 1; 6000 pairs in month 2; 7000 pairs in month 3; 9000 pairs in month 4; 6000 pairs in month 5; 5000 pairs in month 6. It takes a shoemaker 20 minutes to produce a pair of shoes. Each shoemaker works 150 hours per month plus up to 40 hours per month of overtime. A shoemaker is paid a regular salary of $2000 per month plus $20 per hour for overtime. At the beginning of each month, Shoemakers can either hire or fire workers. It costs the company $1000 to hire a worker and $1200 to fire a worker. The monthly holding cost per pair of shoes is 5% of the cost of producing a pair of shoes with regular-time labor. The raw materials in a pair of shoes cost $10. At the beginning of month 1, Shoemakers has 15 workers and 500 pairs of shoes in inventory. Deter- mine how to minimize the cost of meeting (on time) the demands of the next six months. Aegregate planning of shoe production Hours/worker/pair of shoes Regular time hours/month Maximum overtime hours/month Regular salary per month Overtime hourly pay Cost to hire a worker Cost to fire a worker $2,000 $20 $1,000 $1.200 2 Raw material cost/pair of shoes 3 Holding cost (% of regular time cost) 5 Initial number of workers 6 Initial inventory 8 Production/hiring/firing schedule 9 Month 0 Workers from previous month 1 Workers hired 2 Workers fired 3 Workers available -4 Regular-time hours 5 Overtime hours 27 Maximum overtime hours 29 Total hours available 31 Hours used for producing shoes 32 Pairs of shoes produced 33 Shoes available to meet demand 35 Demand 36 Ending Inventory 37 38 Regular salary cost 39 Overtime cost 40 Hiring cost 41 Firing cost 42 Raw material cost 43 Holding cost 44 Total cost 45 47. Shoemakers of America forecasts the following demand for the next six months: 5000 pairs in month 1; 6000 pairs in month 2; 7000 pairs in month 3; 9000 pairs in month 4; 6000 pairs in month 5; 5000 pairs in month 6. It takes a shoemaker 20 minutes to produce a pair of shoes. Each shoemaker works 150 hours per month plus up to 40 hours per month of overtime. A shoemaker is paid a regular salary of $2000 per month plus $20 per hour for overtime. At the beginning of each month, Shoemakers can either hire or fire workers. It costs the company $1000 to hire a worker and $1200 to fire a worker. The monthly holding cost per pair of shoes is 5% of the cost of producing a pair of shoes with regular-time labor. The raw materials in a pair of shoes cost $10. At the beginning of month 1, Shoemakers has 15 workers and 500 pairs of shoes in inventory. Deter- mine how to minimize the cost of meeting (on time) the demands of the next six months. Aegregate planning of shoe production Hours/worker/pair of shoes Regular time hours/month Maximum overtime hours/month Regular salary per month Overtime hourly pay Cost to hire a worker Cost to fire a worker $2,000 $20 $1,000 $1.200 2 Raw material cost/pair of shoes 3 Holding cost (% of regular time cost) 5 Initial number of workers 6 Initial inventory 8 Production/hiring/firing schedule 9 Month 0 Workers from previous month 1 Workers hired 2 Workers fired 3 Workers available -4 Regular-time hours 5 Overtime hours 27 Maximum overtime hours 29 Total hours available 31 Hours used for producing shoes 32 Pairs of shoes produced 33 Shoes available to meet demand 35 Demand 36 Ending Inventory 37 38 Regular salary cost 39 Overtime cost 40 Hiring cost 41 Firing cost 42 Raw material cost 43 Holding cost 44 Total cost 45