122|EXCEL – Solving Problems with Solver

BYU Student Author: @Mike_Paulsin
Reviewers: @Erick_Sizilio, @Nate
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

  • Excel

Need a program? Click here.

Overview
You are taking on the role of a recently hired manager at a local store. The store sells three products: Product A, Product B, and Product C. At the beginning of each month, it is your job to place an order to your wholesaler, to restock the store’s inventory. You have a budget of $10,000 every month. Your job is to decide how many units of each product you should buy, given your $10,000 budget, to maximize profit for the store. You have the following information regarding the purchase price and selling price of these products.

  • Product A is bought for $60.00 and sold for $83.00
  • Product B is bought for $51.00 and sold for $70.50
  • Product C is bought for $24.00 and sold for $32.00
  • The store can only purchase at most 100 units of Product A.
  • The store can only purchase at most 80 units of Product B.
  • The store can only purchase at most 60 units of Product C.
  • The store must purchase at least 50 units of Product A.
  • The store must purchase at least 40 units of Product B.
  • The store must purchase at least 30 units of Product C.
  • The store’s total costs must be less than the allocated budget of $10,000

Instructions
On a blank Excel sheet, use Excel Solver to find the optimal number of units for each product to maximize the profit of the business.

Suggestions and Hints
  • Create a table with each product as the row, and the following columns: Purchase Price, Selling Price, Order Quantity, Total Cost, Total Revenue and Total Profit.
  • On that table, create a Totals row and sum up the values from the rows above.
  • Fill in the table with the relevant information given in the problem.
  • Remember that you cannot purchase partial units. If your solution continues to result in decimals, make sure you have unselected “Ignore Integer Constraints” under “Options”

Solution

Certainly not the prettiest, but it got the job done!
Solver.xlsx (12.5 KB)

Time Spent: 10 minutes
Difficulty: Easy
Fun Practice

Solver Tech Hub.xlsx (11.5 KB)

Time: 10ish minutes
Rating: Easy
Comments: Fun pracrice!
SONA Solver.xlsx (9.9 KB)

Here is another solution that gets the job done:

Solver Tech Hub.xlsx (11.2 KB)

time to complete: 15 min
difficulty: intermediate
good business warm up

122 EXCEL – Solving Problems with Solver.xlsx (10.6 KB)

Time Spent: 15 minutes
Difficulty: Beginner
Comments: Good practice!

122 Excel - Solving Problems with Solver SOLUTION.xlsx (10.3 KB)

Time to Complete: 20 min
Difficulty: Beginner
Comment: Never used Solver before, cool feature!
Challenge 122 - Excel Solver.xlsx (10.1 KB)