BYU Student Author: @Brett_Lowe
Reviewers: @Christian, @Erick_Sizilio, @Mike_Paulsin
Estimated Time to Solve: 15 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
You are the production manager for a high-end glass shop in Boise, Idaho. Your busy season is fast approaching and two of your best employees just retired, so you know you’ll be struggling to meet demand. With that in mind, you decide to carefully plan the production deliverables for each of your five remaining employees. This will ensure that you maximize your profits, despite any new constraints. Each employee specializes in at least three items, but is proficient in no more than four items. To help optimize your production, you decide to build out a spreadsheet and enlist the help of Excel’s solver tool.
Historical data provides the following:
- Product name
- Average profit per product
- Hours expended by each employee on each item
- Demand for each product
- Minimum number of products that should be made each week
Using this information paired with the scheduled hours that your employees have provided, answer the following questions:
- How many of each product should your employees make?
- What is the largest number of profits that you can expect to generate this week?
You may try to complete the challenge without using the instructions. However, some users may benefit from working through them step by step. Good luck!
Instructions
Preliminary Steps
- You’ll need to ensure that you’ve downloaded the solver tool. If you don’t have it yet, check Google or see the steps and video under “Suggestions and Hints” for help getting set up.
- Download the excel file. All the cells containing historical data are highlighted light green.
Build Solver Model
- Complete your solver model by adding formulas to the cells highlighted in yellow.
- Create a formula in cell D12 that sums the number of ornaments made by each employee (F12:J12). Note: The solver tool will eventually populate the number of products made by each employee. No need to fill in any blanks yet. Drag the formula in D12 down through D16.
- Create a formula in cell F19 that sums the number of hours that Eloise worked based on the number of products she created. Then, drag the formula from F19 over through J19.
- Create a formula in cell F23 that sums the total estimated amount of profit based on the number of products that were made.
- If you’d like to check your formulas, feel free to input a few random numbers in cells F12:J16 to gauge whether they’re working properly.
- If you’re still having issues check the “Suggestions and Hints” for help.
Run Solver Analysis
- Open the solver tool.
- Your objective is to maximize profits by changing the number of products made by each individual employee.
- Be sure that your solution doesn’t exceed demand but meets the minimum product requirements.
- Be sure that your solution doesn’t cause any of the employees to work in excess of their available hours.
- Be sure that your solution is restricted to only integers; you won’t receive profit on partial products.
- Be sure that your employees will make only products that they’ve developed a proficiency for.
- Use the Simplex LP solving method.
Data Files
Suggestions and Hints
- To download solver, click File, Options, and Add-ins. In the “Manage” dropdown, select “Excel Add-ins” and click go. Select the box next to the “Solver Add-in” and click “OK.” After doing so, you should be able to access the solver tool in the Data tab of excel. See the video for a walkthrough: Challenge 107|EXCEL – Solver Add-in Setup
- The SUM and SUMPRODUCT formulas will prove useful in this portion of the challenge
- Pay special attention to the constraints provided in the case. If the constraints are correct, your solution should hopefully match.
Solution
Challenge107_Solution.xlsx
Solution Video: Challenge 107|EXCEL – Mind the Fragiles!
