Solve Optimization Problems Using Excel Solver - BUAD 283

Question 1: Blizzard Robots

BUAD 283: Excel Solver Assignment In this assignment, you are asked to solve three optimization problems using Excel Solver. To get started, open a new Excel workbook and prepare three worksheets, naming the tabs Question 1, Question 2, Question 3. Question 1: Blizzard Robots Blizzard Robots Inc. has a production issue. Fortunately, with high demand for their products, they can sell whatever they manufacture. Still, they want to make the best use of their existing resources to generate the highest possible profit. The company has asked you to help plan their upcoming production levels for the next quarter. The company has provided the following data to help get you started. To begin, the company manufactures three types of robots, as shown below. As part of their contractual obligation, they must manufacture and distribute at least 10 of each robot to their retail partner. The table below shows the profit per unit, and the resources required per unit in the manufacturing process. The last column (furthest right) shows the total resources that are available for the upcoming quarter’s production. Your task is to determine how many of each robot they should produce (given the production constraints), in order to generate the highest profit. Robots Pup Wolf Lynx Resources Available Profit per Unit $500 $1,150 $800 Resources Required per Unit ?Labour Hours 5 15 10 850 hours ?Machine Hours 8 20 13 1,200 hours ?Compressors (units) 16 32 24 850 units ?Motors (units) 4 8 6 250 units ?Batteries (units) 3 12 4 200 units

Question 2: Lake Country Tiny Homes Lake Country Tiny Homes is an Okanagan success story. The company manufactures highly durable tiny homes for sale across North America, and demand continues to be strong for the upcoming year. The CEO, however, is concerned that the company’s payroll costs are getting out of hand. He has asked you to determine the optimal, full-time staffing levels that will keep his annual payroll below $3 million. To meet the forecasted demand, the company needs at least 62 full-time production staff. Unfortunately, due to some recent insurance claims, the WCB has required that the company also hire at least one supervisor for every 10 production staff. Furthermore, there needs to be at least one project manager to oversee every three supervisors. And, lastly, since the two Vice Presidents are the CEO's children, you will need to account for their salaries as well. Your task is to determine how many personnel should be hired for each position, given these objectives and constraints. The CEO has provided the following annual salary information to help get you started. ? Production Staff earn $37,500 each ? Supervisors earn $48,000 each ? Project Managers earn $56,000 each ? Vice Presidents earn $75,000 each

Question 3: Parsec Distribution Inc. Parsec Distribution wants to minimize the total shipping costs between their factories and their regional warehouses. Each factory has a limited supply of product to ship and each region has a specific demand projection that they need to fulfill. Given the information provided in the table below, how should the company distribute their product? In other words, how many units should each factory ship to each regional warehouse in order to minimize their costs? Production Shipping Costs per Unit to Regional Warehouses Capacity (units) Vancouver Calgary Winnipeg Toronto Halifax Western 74,500 $1.75 $2.00 $2.05 $2.06 $2.03 Central 44,500 $1.97 $1.84 $1.93 $1.87 $1.65 Eastern 65,000 $2.17 $2.45 $2.50 $1.73 $1.70 Regional Demand in Units 42,500 37,500 26,000 39,000 35,000