144|PYTHON – Solver

Challenges
, ,
1

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

We provide the solution to this challenge using:

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

  1. Download Challenge144_Data.xlsx
  2. Create a new workspace in Python and import pandas, numpy, and scipy.optimize for linprog.
  3. Load the Excel file and read the “Sales Mix Analysis” sheet inot a dataframe
  4. Clean the data by removing irrelevant data (only include: Sales Price, Cost to Buy, Comissions, Min Demand, Max Demand) and extract the relevant values.
  5. Set profit to equal sales price - cost to buy - commission
  6. Set constraints between the minimum demand and maximum demand as well as the budget limit (total purchase cost <= $200,000)
  7. Solve the optimization problem using linprog. (make sure to make profit negative to maximize because the default of linprog is to minimize)
  8. Print the results using an if statement for a successful result and an else statement if it fails.

Data Files

Solution

Challenge144_Solution.ipynb
Solution Video: Challenge 144|PYTHON – Solver