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