solution

Alset Motor Inc. is determining its production schedule for the next four quarters. Demands for electric motorcycles are forecasted to be 400 in quarter 1; 700 in quarter 2; 500 in quarter 3; and 200 in quarter 4. Alset incurs four types of costs, as described below:

1. It costs Alset $800 to manufacture each motorcycle
2. At the end of each quarter, a holding cost of $100 per motorcycle left in inventory is incurred
3. When production is increased from one quarter to the next, a cost is incurred, primarily for training employees. If the increase in production is X motorcycles, the cost is $700X
4. When production is decreased from one quarter to the next, a cost is incurred, primarily for severance pay and decreased morale. If the decrease in production is X motorcycles, the cost is $600X
5. A quarter’s production can be used to meet demand for the current quarter (as well as future quarters).
6. All demands must be met on time

During the quarter immediately preceding quarter 1, 500 motorcycles were produced. Assume that at the beginning of quarter 1, no motorcycles are in inventory.

• Formulate and solve a linear spreadsheet model to help this company solve its problem. Label your Excel file as “Q1”
• Alset is about to finalize a contract for an extra sale of 1000 motorcycles during quarters 3 and 4. The sales manager is confident that she can convince the new client to (i) receive 1000 units in quarter 3 or (ii) receive 500 units in quarter 3 and quarter 4, respectively. As the assistant to the production manager which option would you recommend, and why? Furthermore, provide an explanation as to why one option is better than the other? (Note: you are asked for an exact justification of the new solution, e.g., using the Solver again in light of the new information, rather than just an intuitive solution.)
• Suppose that item (6) in the above list is changed as “All demand must be met by the end of quarter 4”. Modify your model to handle backlogging at a cost of $150 per unit per quarter. Label your Excel file as “Q2” Note that you are asked to build a linear model, that is, you need to avoid using IF functions in your spreadsheet model.

PS. Please include data tabulations and analysis done using solver as screenshots.