EXHIBIT 1 A boat builder is planning its production mix for…
EXHIBIT 1 A boat builder is planning its production mix for next year. It produces luxury motor boats (x1) and sailboats (x2). The company has to decide how many of each type of boats to build in order to maximize its annual profit. A motor boat has a profit contribution of $50,000, and a sailboat has a profit contribution of $30,000. A motor boat requires 1,000 hours of labor and a sailboat requires 500 hours of labor. The company has a total of 80,000 hours of labor available. On average, the cost of advertising a motor boat is $2,000, and the cost of advertising a sailboat is $4,000 (more expensive because of different marketing channels). The company has an annual advertisement budget of maximum $400,000. Finally, the company has a maximum of $18,000,000 to invest in building these boats. A motor boat costs $200,000 to build, and a sailboat $150,000. The decision variables are defined as: x1 = Number of motor boats to build x2 = Number of sailboats to build Motor boat (x1) Sailboat (x2) Profit contribution $50,000 $30,000 Labor required 1,000 h 500 h Advertising cost $2,000 $4,000 Investment needed $200,000 $150,000 Please solve the problem described in this Exhibit using the Excel Solver. You will find the template for this problem in the file Final Exam_Exhibit 1_DATA.xlsm Important: When clicking on this link, if the file does not open you will find it in the “download” section of your browser (bottom left of the page) and you can open it from there. If it still does not open, make sure that you are not editing a formula in another Excel file (this sometimes prevents you from opening a new file). Please note that this file is protected against saving. Do not close this file until you submit the exam, otherwise you may loose its content. In case you inadvertently closed the file, please download it again.