144|EXCEL – Solver

[Dallin_Gardner]

BYU Student Author: @Dallin_Gardner
Reviewers: @Carter_Lee, @Benjamin_Lau
Estimated Time to Solve: 10 Minutes

This is an intro challenge that is part of the Excel Learning Path.

We provide the solution to this challenge using:

• Excel

Need a program? Click here.

Overview
SmartHome Co. sells smart home systems of three levels (Base, Plus, and Full). SmartHome has an international salesforce and generates millions of sales each year. The Argentina branch of SmartHome Co. is historically profitable. However, its managers are exploring opportunities to increase profits even higher. As an analyst, you’ve been asked to find the most profitable sales mix given the range of demand for each level.

Instructions
Go to sheet “Sales Mix Analysis” to start the analysis.

1. Import the Solver Add-in. Use the search bar to search for “Tools.” In the dropdown menu click on the Gearbox labeled “Add-ins.” Check Solver Add-in and click ok. Solver will now be available in the far-right side of the Data Ribbon.
2. Click on the Data Ribbon, and open solver.
3. We’ve been asked to find an order combination that would maximize our profit. So, make sure we set our profit (Cell B17) as our objective.
4. Our variable cells will be the “# ordered” of each product level (Cells B10, B11, B12). Make sure to separate by a comma.
5. Now we must set constraints for the # Ordered. Each count must be between the Minimum and Maximum demand on the sheet.
6. The Argentina branch has an Account Payable limit of $200,000 with our suppliers, so we must also set a constraint, so we don’t get an answer that goes above that limit
7. Double check you have constraints for all demand levels, and Account spend limit, and then click “Solve.”

Data Files

• Challenge144_Data.xlsx

Suggestions and Hints

• Make sure to have a each product possible “#Ordered” constrained between “Min demand” and “Max demand.” Use both =< and >=.
• For the Account Limit Constraint, add a rule that says “E13 =< E14”

Solution

Challenge144_Solver_Solution.xlsx
Solution Video: Challenge 144EXCEL – Solver

[Benjamin_Lau]

Thanks Dallin for the nice little solver challenge. Here is my solution.

Challenge144_Solver.xlsx (12.3 KB)

[Carter_Lee]

Great short challenge that helped me refresh my skills in solver!
Solver Start_carter.xlsx (14.2 KB)

[Jared_Persson]

I liked the solver challenge! I got 248,500$ for the answer. It was a good refresher on how everything works! I especially enjoyed the extra constraint of not surpassing the value–sometimes, we need to remember that the constraints make it non-infinite!

[Jared_Persson]

Challenge144_Data.xlsx (110.8 KB)
Whoops, heres the file.

[Andrew_Christensen]

[Alexis_Larsen]

Here is my solution to your challenge! Thanks!

[Nathan_Orton]

Here is my solution

[Cade_Miller]

I got $248,500

[BenWaite]

The solution I got was $248,500.

[jrdangl]

Dallin Gardner, the Excel Solver Problem was interesting. I was glad to refresh my skills with the application.
Challenge144_Data.xlsx (85.4 KB)

[Maddie_Monroe]

Here is my solution! Thank you!

[Makenzie_Whitman]

Here is my solution

[Alex_Perry]

I got $248,500 using the following constraints:

$B$10:$B$12 <= $H$10:$H$12
$B$10:$B$12 >= $G$10:$G$12
$E$13 <= $E$14

[Michael_Nichols]

Here’s my solution, thank you for the challenge!

[Austin_Napierski]

Time to complete: 15 minutes
Difficulty: Easy
Solution: $248,500

[Adalyn_Curtis]

Time to complete: 20 min
Difficulty: medium-hard
Solution: $248,500.00

Thanks for the challenge!

[Avery_Evans]

I completed the survey in about 5 minutes. It was a great refresher on how to use Solver! Thank you!!

[Cooper_Kennedy]

here is my solution

[Taylor_Wall]

I enjoyed this! I got $248,500 as my answer.
Rating: Intro
Great refresher as to use solver.