Product Profitability Optimization

Businesses typically have the capability to offer a variety of products. Given various factors such as the price at which the product may be sold, number of units that may be produced, resources required to produce each product, fixed and per unit overhead, etc., we would like to know which combination of products may produce the greatest profit for the company. This demonstration employs linear programming concepts to maximize profit for three products. The available resources for production are up to five employees. You may adjust the number of hours each resource is required for production per unit of each product type, as well as the hourly wage of the worker and the number of hours that worker is available per year. You may also specify the number of units you can produce each year, the selling price of the product, per unit and fixed annual overhead. As you adjust the parameters the two bar graphs will reflect the profit and number of each products to produce each year. 

Adjustable Parameters

Productn Price - unit price of Product #n
Employeem Hourly Wage - amount paid Employee #m per hour
Productn Employeem Hours - number of hours required of Employee #m for production of Product #n
Annual Overhead - Fixed annual overhead exclusive of Product 1-5 overhead and wages (e.g. office rent, etc.)
Productn Overhead - variable overhead for Product #n exclusive of Employee 1-5 wages (e.g. materials cost)
Employeen Max Hours/Yr - Maximum hours Employee #n is available per year (52 weeks * 40 hours = 2080)

Output Graphs

You may click the + symbol to the right of the slider to see the actual value. As a change is made the bar graphs displayed to the right will change, showing the potential profit and the numbers of each product that would need to be sold to generate that profit. You may not edit the value field directly but you may make a large change in the slider value by clicking to the left or right of the slider indicator bar.

Profit - Shows the total profit from sales of Products 1-3 with the given constraints
Product Sales - Shows the optimized number of Products 1-3 to sell

An Example

Memories Photography Studio photographs weddings and needs to determine which packages they should sell. They produce three types of wedding packages. Platinum is $5000, Gold is $1500, and Silver is $500. Memories has 5 employees. Bob, the owner, is a Master Photographer and is in high demand. His assistant Carol is a talented photographer but not yet in quite as high demand as Bob. Marcia is the photo editor and creates the photo montages and wedding albums. Monica is Memories' Customer Care Executive and keeps things running smoothly for each wedding that Memories photographs. Diane is the office manager and handles sales and other business functions.

Most weddings occur on weekends. Due to the work involved in producing the Platinum package, Memories can produce at most 50 Platinum packages per year. A Platinum package requires 12 hours of Bob's time, 12 hours of Carol's time, 12 hours of Marcia's time, 16 hours of Monica's time, and 4 hours of Diane's time.  A Gold package requires 2 hours of Bob's time, 8 hours of Carol's time, 6 hours of Marcia's time, 4 hours of Monica's time, and 3 hours of Diane's time. Memories can produce up to 100 Gold packages per year. Since Bob is in such high demand he is only booked for Platinum and Gold weddings, so Carol photographs all of the Silver weddings. Memories can photograph up to 100 Silver weddings per year. A Silver package requires 4 hours of Carol's time, 2 hours of Marcia's time, 2 hours of Monica's time, and 2 hours of Diane's time. 

The number of any one type of package that Memories can produce per year depends on the time required of the employees and the number of hours each employee is available each year. Bob is available 1000 hours per year, Carol 800 hours per year, Monica 2000 hours per year, Marcia 2000 hours per year, and Diane 2000 hours per year.

Each employee gets an hourly wage based on the amount of work they spend on each wedding. Bob receives $80 per hour, Carol $45 per hour, Marcia $35 per hour, Monica $20 per hour, and Diane $25 her hour. 

Memories has fixed overhead costs for office rent, advertising, phones, etc. of $20,000 per year. Each package has a production cost. For Platinum it is $200, for Gold it is $100, and Siver it is $50 for each package sold.

Bob has a good handle on the costs, labor, and scheduling requirements for his different packages, but he's not sure how many of each he should sell. His marketing and growth strategy depends on providing the most profitable prodcut mix. Bob decided to ask for help.

Solution

The Platinum, Gold, and Silver packages correspond to Products 1,2, and 3. Set the price of each product accordingly (5000, 1500, 500). Next, set the hourly wages of Employees 1-5 (Bob = 80, Carol = 45, Marcia = 35, Monica = 20, Diane = 25). Then set the time spent by each employee on each product (Product 1 Employee 1 corresponds to the 12 hours Bob spends on the Platinum package, etc.)  Set the fixed Annual Overhead (28000) and per package Product Overhead  for each product (250, 100, 50). Set the number of each package that Memories is capable of producing each year (50, 100, 100) and the number of hours each employee is available each year (1000,800,2000,2000,2000). The bar graphs show the profit potential and number of each package that would need to be sold to generate that profit. You may explore various scenarios by adjusting the available hours, hours spent on each package, selling price, etc. to determine what effect those parameters have on the optimization. 

This demonstration does not address potential scheduling conflicts between the packages that are sold other than taking into account the hours required by each employee and the maximum number of hours each employee can work each year. Further work could address scheduling and other pertinent issues.

Needs["BarCharts`"];

f[price1_,price2_,price3_, s1_,s2_,s3_,s4_,s5_,p1e1_,p1e2_,p1e3_,p1e4_,p1e5_,p2e1_,p2e2_,p2e3_,p2e4_,p2e5_,p3e1_,p3e2_,p3e3_,p3e4_,p3e5_,overhead_,e1hours_,e2hours_,e3hours_,e4hours_,e5hours_,p1capacity_,
p2capacity_,p3capacity_,p1overhead_,p2overhead_,p3overhead_] :=Maximize[{p1*(price1-(p1e1 s1+p1e2 s2+p1e3 s3+p1e4 s4+p1e5 s5+p1overhead))+p2*(price2-(p2e1 s1+p2e2 s2+p2e3 s3+p2e4 s4+p2e5 s5+p2overhead))+p3*(price3-(p3e1 s1+p3e2 s2+p3e3 s3+p3e4 s4+p3e5 s5+p3overhead))-overhead,p1 p1e1+p2 p2e1 + p3 p3e1≤e1hours,p1 p1e2+p2 p2e2 + p3 p2e3≤e2hours,p1 p1e3+p2 p2e3+p3 p3e3≤e3hours,p1 p1e4+p2 p2e4+p3 p3e4≤e4hours,p1 p1e5+p2 p2e5+p3 p3e5≤e5hours,p1≤p1capacity,p2≤p2capacity,p3≤p3capacity,p1≥ 0,p2 ≥ 0, p3 ≥ 0,+p1overhead ≥ 0, +p2overhead ≥0,p3overhead ≥ 0},{p1,p2,p3}];


(*<<BarCharts`*)

Manipulate [
f0 =f[price1x,price2x,price3x, s1x,s2x,s3x,s4x,s5x,p1e1x,p1e2x,p1e3x,p1e4x,p1e5x,p2e1x,p2e2x,p2e3x,p2e4x,p2e5x,p3e1x,p3e2x,p3e3x,p3e4x,p3e5x,overheadx,e1hoursx,e2hoursx,e3hoursx,e4hoursx,e5hoursx,p1capacityx,
p2capacityx,p3capacityx,p1overheadx,p1overheadx,p1overheadx];
GraphicsColumn[{BarChart[{f0[[1]]},BarStyle->Directive[Green,Opacity[.5]],BarLabels->{},Frame->True,FrameLabel->{"All Products","Profit"},ImageSize->{200,200}], BarChart[{{p1,p2,p3}/.f0[[2]]},BarStyle->Directive[Yellow,Opacity[.5]],BarLabels->{"Product 1","Product 2","Product 3"},Frame->True,FrameLabel->{"Product Sales","Qty"},ImageSize->{200,200}]}],
{{price1x,5000,"Product1 Price"},0,5000,100,Appearance->"Labeled"},{{price2x,1500,"Product2 Price"},0,5000,100,Appearance->"Labeled"},
{{price3x,500, "Product3 Price"},0,5000,100,Appearance->"Labeled"},{{s1x,80,"Employee1 Hourly Wage"},5,200,1,Appearance->"Labeled"},{{s2x,45,"Employee2 Hourly Wage"},5,200,1,Appearance->"Labeled"},{{s3x,35,"Employee3 Hourly Wage"},5,200,1,Appearance->"Labeled"},{{s4x,20,"Employee4 Hourly Wage"},5,200,1,Appearance->"Labeled"},{{s5x,25,"Employee5 Hourly Wage"},5,200,1,Appearance->"Labeled"},{{p1e1x,12,"Product1 Employee1 Hours"},0,20,1,Appearance->"Labeled"},{{p2e1x,2,"Product2 Employee1 Hours"},0,20,1,Appearance->"Labeled"},{{p3e1x,0,"Product3 Employee1 Hours"},0,20,1,Appearance->"Labeled"},{{p1e2x,12,"Product1 Employee2 Hours"},0,20,1,Appearance->"Labeled"},{{p2e2x,8,"Product2 Employee2 Hours"},0,20,1,Appearance->"Labeled"},{{p3e2x,4,"Product3 Employee2 Hours"},0,20,1,Appearance->"Labeled"},{{p1e3x,12,"Product1 Employee3 Hours"},0,20,1,Appearance->"Labeled"},{{p2e3x,6,"Product2 Employee3 Hours"},0,20,1,Appearance->"Labeled"},{{p3e3x,2,"Product3 Employee3 Hours"},0,20,1,Appearance->"Labeled"},{{p1e4x,16,"Product1 Employee4 Hours"},0,20,1,Appearance->"Labeled"},{{p2e4x,8,"Product2 Employee4 Hours"},0,20,1,Appearance->"Labeled"},{{p3e4x,2,"Product3 Employee4 Hours"},0,20,1,Appearance->"Labeled"},{{p1e5x,4,"Product1 Employee5 Hours"},0,20,1,Appearance->"Labeled"},{{p2e5x,3,"Product2 Employee5 Hours"},0,20,1,Appearance->"Labeled"},{{p3e5x,2,"Product3 Employee5 Hours"},0,20,1,Appearance->"Labeled"},{{overheadx,20000,"Annual Overhead"},0,100000,1000,Appearance->"Labeled"},{{e1hoursx,1000,"Employee1 Max Hours/Yr"},100,2000,100,Appearance->"Labeled"},{{e2hoursx,1000,"Employee2 Max Hours/Yr"},100,2000,100,Appearance->"Labeled"},{{e3hoursx,1000,"Employee3 Max Hours/Yr"},100,2000,100,Appearance->"Labeled"},{{e4hoursx,2000,"Employee4 Max Hours/Yr"},100,2000,100,Appearance->"Labeled"},{{e5hoursx,2000,"Employee5 Max Hours/Yr"},100,2000,100,Appearance->"Labeled"},{{p1capacityx,50,"Product1 Annual Capacity"},10,200,10,Appearance->"Labeled"},{{p2capacityx,100,"Product2 Annual Capacity"},10,200,10,Appearance->"Labeled"},{{p3capacityx,100,"Product3 Annual Capacity"},10,200,10,Appearance->"Labeled"},
{{p1overheadx, 200, "Product1 Overhead"},0,1000,10,Appearance->"Labeled"},
{{p2overheadx, 100, "Product2 Overhead"},0,1000,10,Appearance->"Labeled"},
{{p3overheadx, 50, "Product3 Overhead"},0,1000,10,Appearance->"Labeled"},
SaveDefinitions->True]

Thank You!

Thank you for trying this demonstration. Did you find it useful? Did it help you solve a problem? If so, please email us and let us know how you used it to solve your problem.

We are looking for ideas for new demonstrations to help people solve real problems. If you have an idea for a demonstration or would like to sponsor an application, don't hesitate to contact us via email at analyst@10squaredcorp.com or phone (404) 271-1907.

This demonstration is provided courtesy of 10 Squared Corporation, an Atlanta, Georgia based consulting firm.