Coxino-Art Company uses a 12 month rolling schedule, and this schedule is up-dated each month. The company demand has a relationship with their advertisement both on the web banner and Instagram as shown in the table:

It has been noticed that the labor cost is decreasing with the increasing volume of production as shown in the table:

The regular production hours limited to 3500 hours and the limit of the overtime is 1500 hours. The production of one unit can take 4 hours. Inventory holding costs are $100 per unit. The company plans production to minimize yearly production costs, but plan never lose a sale and to maintain a minimum of 500 units and a maximum of 1200 units in inventory at all times when they have a high demand to handle unexpected sales. The current inventory level is 500 units.

The company has a subcontractor who can handle their production. While the material costs are the same, the subcontracting cost is very high as it expected to be $120.00. The company wishes to keep most of their production in house but wonder if it would be economical to subcontract some of the production to satisfy the high demand.

Produce a plan for the next the year 2021 to help the manager to decide if it is useful to follow overtime/premium policy of subcontract?

I need urgent solution using Excel for the question above. Please

TIA

| | June 971 30 2020 Demand Web banner Instagram 2021 Demand Web banner Instagram January February 973 1119 0 40 January February 1044 914 50 0 March 875 25 25 March 1329 55 25 April 625 25 25 April 1330 55 25 May 910 30 30 May 1405 August September October 1177 882 982 35 40 35 25 25 August September October July 931 35 35 July 1521 65 November 1528 45 45 November | || December 1577 45 45 December 30 June 1436 70 60 30 60 30 0 35 70 40 : 0 45 80 45 35 m Cost 78 73 65 Production 500-700 701-900 901-1100 1101-1300 1301-1500 1500-1701 >1700 58 52 48 38 | | June 971 30 2020 Demand Web banner Instagram 2021 Demand Web banner Instagram January February 973 1119 0 40 January February 1044 914 50 0 March 875 25 25 March 1329 55 25 April 625 25 25 April 1330 55 25 May 910 30 30 May 1405 August September October 1177 882 982 35 40 35 25 25 August September October July 931 35 35 July 1521 65 November 1528 45 45 November | || December 1577 45 45 December 30 June 1436 70 60 30 60 30 0 35 70 40 : 0 45 80 45 35 m Cost 78 73 65 Production 500-700 701-900 901-1100 1101-1300 1301-1500 1500-1701 >1700 58 52 48 38