Profit model, using best fitting power curve for estimating demand
Monetary inputs
Text Box: Range names used: UnitCost - B4 UnitPrice - B7 PredDemand - B10 TotRev - B11 TotCost - B12 Profit - B13 MaxProfit - B16 Prices - A22:A71 Profits - B22:B71 UnitCosts - A81:A165
Text Box: Best Price Formula =INDEX(Prices,MATCH(MaxProfit,Profits,0),1) =INDEX(Prices,28,1) The first argument is the Prices range, the second is a row index, and the third is a column index
Unit cost to produce $250
Decision variable
Unit price (trial value) $400
Profit model
Predicted demand 63.601
Total revenue $25,441
Total cost $15,900
Profit $9,540
Maximum profit from data table below, with corresponding best unit price
Maximum profit $10,409
Best price $530
Data table for Profit as a function of Unit price
Unit price Profit
$9,540
$260 $1,447
$270 $2,693
$280 $3,769
$290 $4,699
$300 $5,506
$310 $6,207
$320 $6,815
$330 $7,345
$340 $7,805
$350 $8,206
$360 $8,554
$370 $8,856
$380 $9,118
$390 $9,345
$400 $9,540
$410 $9,708
$420 $9,851
$430 $9,973
$440 $10,075
$450 $10,160
$460 $10,230
$470 $10,286
$480 $10,330
$490 $10,363
$500 $10,387
$510 $10,402
$520 $10,409
Text Box: Maximum profit
$530 $10,409
$540 $10,403
$550 $10,391
$560 $10,375
$570 $10,354
$580 $10,329
$590 $10,300
$600 $10,269
$610 $10,234
$620 $10,197
$630 $10,158
$640 $10,116
$650 $10,073
$660 $10,029
$670 $9,983
Text Box: The problem of finding a profit-maximizing price is an example of an optimization model. The optimization problem can be characterized by the following: 1. Decisions 2. Constraints 3. Objectives In optimization models, we try to maximize or minimize a specified output cell by changing the values of the decision variable cells.
$680 $9,936
$690 $9,887
$700 $9,838
$710 $9,788
$720 $9,738
$730 $9,686
$740 $9,635
$750 $9,583
Maximum profit for different unit costs (from data table below)
Unit cost $150 $200 $250 $300 $350
Maximum profit $16,552 $12,748 $10,409 $8,821 $7,669
Optimization
Corresponding best price $320 $420 $530 $630 $740
Sensitivity of maximum profit to unit cost (unit cost is along the top, unit price is along the side)
$9,540 $150 $200 $250 $300 $350
$160 $3,654 -$14,618 -$32,890 -$51,161 -$69,433
$170 $6,510 -$9,766 -$26,041 -$42,317 -$58,593
$180 $8,756 -$5,838 -$20,432 -$35,026 -$49,620
$190 $10,531 -$2,633 -$15,796 -$28,960 -$42,123
$200 $11,936 $0 -$11,936 -$23,872 -$35,808
$210 $13,050 $2,175 -$8,700 -$19,575 -$30,450
$220 $13,932 $3,980 -$5,971 -$15,922 -$25,873
$230 $14,627 $5,485 -$3,657 -$12,799 -$21,941
$240 $15,172 $6,743 -$1,686 -$10,115 -$18,543
$250 $15,594 $7,797 $0 -$7,797 -$15,594
$260 $15,917 $8,682 $1,447 -$5,788 -$13,023
$270 $16,157 $9,425 $2,693 -$4,039 -$10,772
$280 $16,330 $10,049 $3,769 -$2,512 -$8,793
$290 $16,447 $10,573 $4,699 -$1,175 -$7,049
$300 $16,518 $11,012 $5,506 $0 -$5,506
$310 $16,551 $11,379 $6,207 $1,034 -$4,138
$320 $16,552 $11,683 $6,815 $1,947 -$2,921
$330 $16,526 $11,935 $7,345 $2,754 -$1,836
$340 $16,478 $12,142 $7,805 $3,469 -$867
$350 $16,412 $12,309 $8,206 $4,103 $0
$360 $16,331 $12,442 $8,554 $4,666 $778
$370 $16,237 $12,547 $8,856 $5,166 $1,476
$380 $16,132 $12,625 $9,118 $5,611 $2,104
$390 $16,020 $12,682 $9,345 $6,007 $2,670
$400 $15,900 $12,720 $9,540 $6,360 $3,180
$410 $15,775 $12,742 $9,708 $6,674 $3,640
$420 $15,646 $12,748 $9,851 $6,954 $4,056
Text Box: Limitations of the Model: 1. There is no real reason why golf club prices should be restricted to be multiples of $10 2. Implicit assumption that price is the only factor that influences demand. PredDemand=PowConst*UnitPrice^PowExpon (y=axb). Other factors possible include: advertising, state of the economy, competitors' prices, strength of competition, and promotional expenses. 3. Demand might not equal sales. Ex: demand = 70,000, capacity = 50,000
$430 $15,513 $12,743 $9,973 $7,202 $4,432
$440 $15,377 $12,726 $10,075 $7,423 $4,772
$450 $15,240 $12,700 $10,160 $7,620 $5,080
$460 $15,101 $12,665 $10,230 $7,794 $5,358
$470 $14,961 $12,624 $10,286 $7,948 $5,610
$480 $14,821 $12,576 $10,330 $8,084 $5,839
$490 $14,681 $12,522 $10,363 $8,204 $6,045
$500 $14,542 $12,464 $10,387 $8,309 $6,232
$510 $14,402 $12,402 $10,402 $8,401 $6,401
$520 $14,264 $12,336 $10,409 $8,481 $6,554
$530 $14,127 $12,268 $10,409 $8,550 $6,692
$540 $13,990 $12,197 $10,403 $8,609 $6,816
$550 $13,855 $12,123 $10,391 $8,660 $6,928
$560 $13,722 $12,048 $10,375 $8,702 $7,028
$570 $13,590 $11,972 $10,354 $8,736 $7,118
$580 $13,459 $11,894 $10,329 $8,764 $7,199
$590 $13,330 $11,815 $10,300 $8,786 $7,271
$600 $13,203 $11,736 $10,269 $8,802 $7,335
$610 $13,077 $11,656 $10,234 $8,813 $7,391
$620 $12,953 $11,575 $10,197 $8,819 $7,441
$630 $12,831 $11,494 $10,158 $8,821 $7,485
$640 $12,710 $11,413 $10,116 $8,819 $7,522
$650 $12,592 $11,333 $10,073 $8,814 $7,555
$660 $12,475 $11,252 $10,029 $8,806 $7,583
$670 $12,360 $11,171 $9,983 $8,794 $7,606
$680 $12,246 $11,091 $9,936 $8,780 $7,625
$690 $12,134 $11,011 $9,887 $8,764 $7,640
$700 $12,024 $10,931 $9,838 $8,745 $7,652
$710 $11,916 $10,852 $9,788 $8,724 $7,660
$720 $11,809 $10,774 $9,738 $8,702 $7,666
$730 $11,704 $10,695 $9,686 $8,677 $7,668
$740 $11,601 $10,618 $9,635 $8,652 $7,669
$750 $11,499 $10,541 $9,583 $8,625 $7,666
$760 $11,399 $10,465 $9,531 $8,596 $7,662
$770 $11,301 $10,389 $9,478 $8,567 $7,655
$780 $11,204 $10,315 $9,425 $8,536 $7,647
$790 $11,108 $10,240 $9,373 $8,505 $7,637
$800 $11,014 $10,167 $9,320 $8,472 $7,625
$810 $10,922 $10,094 $9,267 $8,439 $7,612
$820 $10,831 $10,022 $9,214 $8,406 $7,598
$830 $10,741 $9,951 $9,161 $8,372 $7,582
$840 $10,653 $9,881 $9,109 $8,337 $7,565
$850 $10,566 $9,811 $9,056 $8,302 $7,547
$860 $10,480 $9,742 $9,004 $8,266 $7,528
$870 $10,396 $9,674 $8,952 $8,230 $7,508
$880 $10,313 $9,606 $8,900 $8,194 $7,487
$890 $10,231 $9,540 $8,849 $8,157 $7,466
$900 $10,151 $9,474 $8,797 $8,120 $7,444
$910 $10,071 $9,409 $8,746 $8,084 $7,421
$920 $9,993 $9,344 $8,695 $8,047 $7,398
$930 $9,916 $9,281 $8,645 $8,009 $7,374
$940 $9,841 $9,218 $8,595 $7,972 $7,349
$950 $9,766 $9,156 $8,545 $7,935 $7,324
$960 $9,692 $9,094 $8,496 $7,898 $7,299
$970 $9,620 $9,033 $8,447 $7,860 $7,274
$980 $9,549 $8,973 $8,398 $7,823 $7,248
$990 $9,478 $8,914 $8,350 $7,786 $7,221
$1,000 $9,409 $8,855 $8,302 $7,748 $7,195