*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.

- 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.
- Click on the Data Ribbon, and open solver.
- 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.
- Our variable cells will be the “# ordered” of each product level (Cells B10, B11, B12). Make sure to separate by a comma.
- Now we must set constraints for the # Ordered. Each count must be between the Minimum and Maximum demand on the sheet.
- 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
- Double check you have constraints for all demand levels, and Account spend limit, and then click “Solve.”

**Data Files**

## 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