Pricing Problem (Revenue optimization, Price-Demand curve) Based on extensive research and surveying we have acquired ten data points of prices and their corresponding demands for four products, A, B,...

1 answer below »
EXCEL SOLVER PROBLEM


Pricing Problem (Revenue optimization, Price-Demand curve) Based on extensive research and surveying we have acquired ten data points of prices and their corresponding demands for four products, A, B, C, and D. This data is available in “Pricing - Data.xlsx”. Note that we do not know a-priori which pair of products are substitute or complement with each other. a. Assuming we have a linear joint price demand curve for these four products (similar to the phone problem), find the best fitting curve using minimum error squared method. (Save your solution in the tab “Pricing_Curves”.) 1. Hint: you must have 20 decision variables in total; 5 for the linear priced and curve of each product. 2. Turn off “automatic scaling” in the options. Since the numbers are large but errors in the correct solution are relatively small, having that option on will mess up your solution. 3. Set the precisions and the convergences at 0.000001. 4. It is alright if your predicted demands turn out to be non-integer. Do not round them. b. Using the price-demand curves you found in part a, find the optimal prices to maximize profit. The unit cost of production for each of these products are available in the data file. (Save your solution in the tab “Pricing_Optimal”.) DATA: Price Demand Scenario # A B C D A B C D 1 $ 12.99 $ 18.99 $ 21.99 $ 11.99 550 1006 718 493 2 $ 12.99 $ 12.99 $ 23.99 $ 31.99 871 1565 319 76 3 $ 12.99 $ 24.99 $ 22.99 $ 16.99 527 701 683 405 4 $ 12.99 $ 20.99 $ 23.99 $ 21.99 650 997 510 294 5 $ 13.99 $ 19.99 $ 21.99 $ 14.99 524 953 712 448 6 $ 12.99 $ 24.99 $ 18.99 $ 29.99 636 772 873 178 7 $ 14.99 $ 14.99 $ 20.99 $ 17.99 560 1262 696 398 8 $ 10.99 $ 13.99 $ 20.99 $ 39.99 1027 1564 476 -82 9 $ 14.99 $ 23.99 $ 18.99 $ 25.99 516 782 905 281 10 $ 16.99 $ 26.99 $ 18.99 $ 34.99 462 656 897 126 Production Cost $ 10.00 $ 12.50 $ 12.50 $ 15.00
Answered Same DayMay 21, 2021

Answer To: Pricing Problem (Revenue optimization, Price-Demand curve) Based on extensive research and surveying...

Himanshu answered on May 22 2021
132 Votes
Sheet1
            Price                Demand
        Scenario #    A    B    C    D    A    B    C    D
        1    $ 12.99    $ 18.99    $ 21.99    $ 11.99    550    1006    718    493
        2    $ 12.99    $ 12.99    $ 23.99    $ 31.99    871    1565    319    76
        3    
$ 12.99    $ 24.99    $ 22.99    $ 16.99    527    701    683    405
        4    $ 12.99    $ 20.99    $ 23.99    $ 21.99    650    997    510    294
        5    $ 13.99    $ 19.99    $ 21.99    $ 14.99    524    953    712    448
        6    $ 12.99    $ 24.99    $ 18.99    $ 29.99    636    772    873    178
        7    $ 14.99    $ 14.99    $ 20.99    $ 17.99    560    1262    696    398
        8    $ 10.99    $ 13.99    $ 20.99    $ 39.99    1027    1564    476    -82
        9    $ 14.99    $ 23.99    $ 18.99    $ 25.99    516    782    905    281
        10    $ 16.99    $ 26.99    $ 18.99    $ 34.99    462    656    897    126
            Production Cost
            $ 10.00    $ 12.50    $ 12.50    $ 15.00
Pricing_Curves
Product A
12.99    12.99    12.99    12.99    13.99    12.99    14.99    10.99    14.99    16.989999999999998    550    871    527    650    524    636    560    1027    516    462    Price
Demand
Product B
18.989999999999998    12.99    24.99    20.99    19.989999999999998    24.99    14.99    13.99    23.99    26.99    1006    1565    701    997    953    772    1262    1564    782    656    Price
Demand
Product C
21.99    23.99    22.99    23.99    21.99    18.989999999999998    20.99    20.99    18.989999999999998    18.989999999999998    718    319    683    510    712    873    696    476    905    897    Price
Demand
Product D
11.99    31.99    16.989999999999998    21.99    14.99    29.99    17.989999999999998    39.99    25.99    34.99    493    76    405    294    448    178    398    -82    281    126    Price
Demand
Pricing_Optimal
    Product A                Product B                Product C                Product D
    Price     Demand            Price     Demand            Price     Demand            Price     Demand
    $ 12.99    550            $ 18.99    1006            $ 21.99    718            $ 11.99    493
    $ 12.99    871            $ 12.99    1565            $ 23.99    319            $ 31.99    76
    $ 12.99    527            $ 24.99    701            $ 22.99    683            $ 16.99    405
    $ 12.99    650            $ 20.99    997            $ 23.99    510            $ 21.99    294
    $ 13.99    524            $ 19.99    953            $ 21.99    712            $ 14.99    448
    $ 12.99    636            $ 24.99    772            $ ...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here