Production Planning Suppose that you are operating a factory which produces two types of bicycles A and B. You will need to plan how many bicycles of each type to be produced for the next three months (May, June, and July). Producing bicycles require two types of labors, one from machine and the other one from manpower (employees). Producing one bicycle of type A requires 1.5 hours from the machine and 1.1 hours from the employees, while producing one bicycle of type B requires 1.6 hours from the machine and 1.2 hours from the employees. Available capacities of the machine and the employee, in hours in each month, is given in the table below. The table also shows monthly demands of each bicycle type. Your production plan needs to satisfy the following: Month Machine Capacity (hours) Employee Capacity (hours) Demand-type A Demand-Type B May June July 3,000 2,800 3,600 2,500 2,300 2,400 700 900 1,000 800 600 900 Table 1: Capacity and Demand Information for Production Planning Problem. Currently (at the beginning of May), you have 100 units of bicycle of type A and 120 units of bicycle of type B. After three months (at the end of July), you need to have available stocks of both types of bicycles. In particular, it is required to have 130 units of bicycle of type A and 110 units of bicycle of type B which are produced but unsold at the end of July. It costs $20 to produce one unit of bicycle of type A, and $25 for one unit of bicycle of type B. At each month, the factory can produce at most 1,000 units of bicycles of type A and 1,000 units of bicycles of type B. You need to determine the amount of bicycles of each type to be produced at each month, in order to minimize the total production cost. (a) Formulate this problem as an optimization problem. State clearly what your decision variables are, the objective function, and explain your constraints. 1 (b) Using Microsoft Excel, solve this optimization problem and report optimal solutions and ob- jective value. (e) Under the optimal solution, how many bicycles are in stock (produced but not sold) at the end of May and June? (d) Suppose that due to the storage space, you want to keep the total number of bicycles (both type A and B) in stock at the end of May and June no more than 400. Add proper constraint(s) to your problem and report how your optimal solution changes.