A company manufactures three products $-A,B$ and $C.$ The historical demands for the three items are provided in the excel named "Product Data". An analyst from the operations team suggested the forecasts for the first four months of the next year. These forecasts are given in the same excel $("$Product Data"$).$

The chief operations officer $($COO$)$ believes the forecast is not good. Overestimating demand can cause a loss of $$4500$ per product for product $A,$$$3500$ for product $B$ and $$6500$ for product $C.$ These are the costs of not selling the products in time. The products are perishable and have limited shelf life. Therefore, once a product expires, there is a cost. On the other hand, underestimation can cost the company $$3500$ per unit of product A$,$ $$2500$ per unit of product $B$ and $$4500$ per unit of product C$.$ Essentially, these are the profits made from selling each unit of product. Assume that the COO hires your team $($or you$)$ to get some help.

Help the COO in the following ways $($these are the questions that you should answer$)$:

Hint: For part $2,$ think about what can give you average underestimation and overestimation.

$3.$ The COO also wants to know the optimal product mix quantities for each product for the first four months of the coming year. This can vary from month to month, but as of now the COO is happy to have just the answer for January $2024$ and this will be the solution for every month till April. Note that in the coming year the company must adhere to the following constraints regarding their budget, supplier raw materials and demand.

a$.$ Demand constraints: Produce according to your forecast.

b$.$ Budget: Production costs are $$1200$ per product for product $A,$$$1500$ for product B and $$1400$ for product $C.$ Total budget is for production is $$4000000.$

c$.$ The three products need raw materials $x,Y$ and $Z.$ the following table gives how much raw material is need $($in lbs$)$ for each unit of a product. Maximum available quantity of X$,$ Yand Z is $12000lbs$ each.

$\backslash$table$[[,$Products$],[\backslash$table$[[$Raw$],[$materials$]],$A$,$B$,$C$],[x,0,5,7],[Y,1,1,1],[Z,3,3,2]]$

$\backslash$table$[[,$A$,$B$,$C$,$D$,E,F,$G$,$H$],[1,,,$Actual Sales Data,Analyst's Forecast$],[2,$Year,Month,Product A$,$Product B$,$Product C$,$Product A$,$Product B$,$Product C$],[3,2022,$Jan,$121,5130,596,141,4901,646],[4,2022,$Feb,$124,5045,575,167,4907,574],[5,2022,$Mar,$118,5095,613,143,4908,695],[6,2022,$Apr,$127,4905,624,120,4933,556],[7,2022,$May,$126,5075,591,123,4919,540],[8,2022,$Jun,$130,4960,601,177,4908,549],[9,2022,$Jul,$132,4990,573,143,4917,638],[10,2022,$Aug,$130,4770,580,153,4967,537],[11,2022,$Sep,$139,4890,578,130,4933,618],[12,2022,$Oct,$143,4710,591,188,4991,556],[13,2022,$Nov,$138,5050,585,150,4964,534],[14,2022,$Dec,$139,4745,572,185,4923,509],[15,2023,$Jan,$147,4840,599,170,4997,605],[16,2023,$Feb,$151,4515,591,141,4919,583],[17,2023,$Mar,$145,4810,608,169,4916,593],[18,2023,$Apr,$155,4540,611,185,4905,696],[19,2023,$May,$152,4750,624,149,4989,621],[20,2023,$Jun,$153,4430,579,138,4949,540],[21,2023,$Jul,$161,4730,621,149,4912,509],[22,2023,$Aug,$158,4465,576,121,4992,681],[23,2023,$Sep,$166,4715,572,120,4900,579],[24,2023,$Oct,$164,4440,602,156,4954,636],[25,2023,3$Nov,$169,4550,606,143,4959,560],[26,2023,$Dec,$169,4295,596,183,4993,534],[27,2024,,,,,164,4963,661],[28,2024,,,,,163,4910,645],[29,2024,,,,,153,4910,502],[30,2024,,,,,147,4926,573],[31,,,,,,,,]]$