Jake is the regional head of Modern Automobile Inc. Jake is responsible for selling and manufacturing vehicles for the region. Jake has two products in his product portfolio: one model of SUV (SUV) and one model of family sedan (Car). The plans for production and sales are prepared in the quarterly basis. Although Jake has a considerable amount of autonomy on how to run his business in the region, Modern Automobile Inc. places a guideline on the maximum number of vehicles can be produced in each quarter. The maximum numbers of SUV and Car allowed for Jake to manufacture for each quarter are given below: Maximum Allowed SUV in units Car in units 1st Quarter 200 400 2nd Quarter  300 900 3rd Quarter 100 700 4th Quarter 400 1000
The production of one unit of SUV consumes 1.8 ton of steel, where as one unit production of Car consumes 0.8 ton of steel. Due to the lack of steel mills in the region, Jake only manages to obtain 1000 ton of steel supply for each quarter. Since last year, the regional government has imposed an environmental regulation on fuel efficiency. The fuel efficiency is measured by mile-per-gallon (mpg). The average mpg for all vehicles Jake manufactures this year must be at least 40 mpg. Petrol guzzling SUV only achieves 20 mpg, whereas more fuel efficient Car can deliver 45 mpg. When a vehicle is produced, it is either directly sent to dealers or kept at factory parking lot for later delivery (to dealers). The general accounting practice for Jake is that any vehicle still at the factory parking lot at the end of each quarter incurs an inventory holding cost. Inventory holding costs are $200 per unit per quarter for SUV and $100 per unit per quarter for Car. Since Jake’s background is in production management, he understands very well that certain costs occur if production volume increases (partly due to over worn production facility) or decreases (partly due to cost related to idling capacity) from a quarter to the next. One unit increase in the production volume of SUV from one quarter to the next incurs $100, whereas one unit increase in the production volume of Car from one quarter to the next incurs $50. On the other hand, one unit decrease in the production volume of SUV from one quarter to the next incurs $80, whereas one unit decrease in the production volume of Car from one quarter to the next incurs $30. It is not a secret to anybody that SUV is a lot more profitable product than Car. Excluding the inventory holding cost and costs related to the increase or decrease in production volume, each unit of SUV contributes $2000 to the profit, whereas each unit of Car only contributes $150 to the profit.

. (a)  Describe the decision making problem for Jake. Apply linear programming model to develop a formulation for the decision making problem of Jake. 
(20 marks) 
. (b)  Use Microsoft Excel to solve the formulation in Question 1(a). Your answer must include the sensitivity report. 
(5 marks) 
. (c)  Imagine you are working for Jake. Interpret the sensitivity report you obtained in Question 1(b) and prepare an analysis report for Jake in 500 words. Your analysis report may include subjects like, for example, whether it would be beneficial for the company if they try to persuade the regional government to relax the mpg requirement or what would be the impact on the production plan if profit contribution of SUV falls in future. 
(15 marks)