2024 – Spreadsheet mpodelling and decision analysis Question1 Sanderson Manufacturing produces ornate decorative wood frame doors and windows

 

Spreadsheet mpodelling and decision analysis

 

Question1. 

 

Sanderson Manufacturing produces ornate, decorative wood frame doors and windows. Each item produced goes through 3 manufacturing processes: cutting, sanding, and finishing. Each door produced requires 1 hour in cutting, 30 minutes in sanding, and 30 minutes in finishing. Each window requires 30 minutes in cutting, 45 minutes in sanding, and 1 hour in finishing. In the coming week Sanderson has 40 hours of cutting capacity available, 40 hours of sanding capacity, and 60 hours of finishing capacity. Assume that all doors produced can be sold for a profit of $500 and all windows can be sold for a profit of $400. 
a. Formulate an LP model for this problem.
b. Sketch the feasible region.
c. What is the optimal solution?

Question 2.

 

American Auto is evaluating their marketing plan for the sedans, SUVs, and trucks they produce. A TV ad featuring this SUV has been developed. The company estimates that each showing of this commercial will cost $500,000 and increase sales of SUVs by 3%, but reduce sales of trucks by 1%, and have no effect of the sales of sedans. The company also has a print ad campaign developed that it can run in various nationally distributed magazines at a cost of $750,000 per title. It is estimated that each magazine title the ad runs in will increase the sales of sedans, SUVs, and trucks by 2%, 1%, and 4%, respectively. The company desires to increase sales of sedans, SUVs, and trucks by at least 3%, 14%, and 4%, respectively, in the least costly manner. 
a. Formulate an LP model for this problem.
b. Sketch the feasible region.
c. What is the optimal solution?

i) Use the corner point method ; hand drawn graphs for the feasibility region are acceptable if clearly legible.

ii) solve using risk solver