For MichaelsSolutions Only

profilereneo.kena87
sample_chapter_9.xlsx
Home>Mathematics homework help>Numerical analysis homework help>For MichaelsSolutions Only

Problem and Data

Price Brand A Price Brand B Units Sold Brand A Units Sold Brand B
$ 500.00 $ 300.00 72 176
$ 100.00 $ 300.00 296 116
$ 150.00 $ 200.00 234 197
$ 498.00 $ 475.00 133 48
$ 250.00 $ 350.00 229 102
$ 300.00 $ 389.00 215 82
$ 350.00 $ 150.00 105 263
$ 400.00 $ 398.00 162 90
$ 450.00 $ 300.00 100 169
$ 500.00 $ 100.00 4 322
$ 550.00 $ 275.00 36 202
$ 380.00 $ 402.00 174 84
$ 210.00 $ 300.00 235 133
$ 330.00 $ 480.00 229 20
$ 700.00 $ 500.00 28 60
$ 475.00 $ 505.00 156 23

Data on sales of two popular brands of 46 inch LED TV’s at a local electronics store are shown below. The store can buy Brand A for $200 per unit from its manufacturer and Brand B for $175 per unit from its manufacturer. a. Using multiple regression (Excel Data Analysis or PHStat), develop an equation for the demand (number of units sold) of Brand A based upon the prices of both brands. Find a similar equation for the demand of Brand B using a second multiple regression. b. On a new worksheet, construct an Excel model for total revenue and total profit from sales of both brands. Name this worksheet "Part b: Model". c. Copy your Part b model to a new worksheet named "Part c: Goal Seek." Use Excel's Goal Seek tool to find the prices for both brands that will produce a total revenue of $75,000. d. Copy your original Part b model to a new worksheet named "Part d: Data Table." Develop a two-way data table to determine total profit for the prices of Brand A and Brand B between $200 and $600 with $50 intervals. e. Still on the Part d: Data Table worksheet, use Scenario Manager to investigate the impact on Total Profitability of changing the unit cost of Brand A and the unit cost of Brand B to the following combinations: A-$150, B-$250; A-$175, B- $200; A-$200, B-$175; A-$225, B-$250. Produce a Summary Report. Note: when you save your file, the Scenario Manager will save your four scenarios and I will be able to see how you set it up. f. Copy your original Part b model to a new worksheet named "Part f: Solver." With the unit cost of A of $200 and B of $175, use Excel Solver to find the prices for Brand A and Brand B that will maximize total profit given that the store can buy no more than 100 units of Brand B and must meet a minimum total revenue of $100,000. Submit your properly-named Excel file in the Bb M5A1 Assignment area.

multiple for brand A

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.9999954683
R Square 0.9999909366
Adjusted R Square 0.9999895423
Standard Error 0.2808669069
Observations 16
ANOVA
df SS MS F Significance F
Regression 2 113148.974479148 56574.4872395738 717165.655339513 1.6687243057128E-33
Residual 13 1.0255208523 0.0788862194
Total 15 113150
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 250.0042286875 0.2471150968 1011.6914421872 3.24786583478613E-33 249.4703689778 250.5380883972 249.4703689778 250.5380883972
Price Brand A -0.5603227601 0.0004772955 -1173.9536513315 4.69644588411911E-34 -0.5613538942 -0.5592916259 -0.5613538942 -0.5592916259
Price Brand B 0.3410757847 0.0006171914 552.6256675287 8.42443343179408E-30 0.3397424238 0.3424091455 0.3397424238 0.3424091455

multiple for brand B

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.9999943241
R Square 0.9999886482
Adjusted R Square 0.9999869017
Standard Error 0.3099848749
Observations 16
ANOVA
df SS MS F Significance F
Regression 2 110040.688321905 55020.3441609527 572588.069881365 7.20991018698978E-33
Residual 13 1.2491780945 0.0960906227
Total 15 110041.9375
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 320.2470578342 0.272733955 1174.2104417342 4.68311153165382E-34 319.6578519462 320.8362637223 319.6578519462 320.8362637223
Price Brand A 0.149201376 0.0005267775 283.2341289343 4.99962035181549E-26 0.1480633423 0.1503394097 0.1480633423 0.1503394097
Price Brand B -0.7288895609 0.0006811767 -1070.0448069854 1.56675502300678E-33 -0.7303611536 -0.7274179681 -0.7303611536 -0.7274179681

Part b Model

Price Brand A Price per unit Buying price per unit Unit sold Revenue Cost Profit
$ 500.00 Brand A $ 200.00 2408 $ - 0 $ 481,600.00 $ (481,600.00)
$ 100.00 Brand B $ 175.00 2087 $ - 0 $ 365,225.00 $ (365,225.00)
$ 150.00 Total $ - 0 $ 846,825.00 $ (846,825.00)
$ 498.00
$ 250.00 Price Brand A Price Brand B Units Sold Brand A Units Sold Brand B Buying price per unit of brand A Buying price per unit of brand A Revenue for brand A Revenue for brand B Cost for brand A Cost for brand B Profit for Brand A Profit for Brand B
$ 300.00 $ 500.00 $ 300.00 72 176 $ 200.00 $ 175.00 $ 36,000.00 $ 52,800.00 $ 14,400.00 $ 30,800.00 $ 21,600.00 $ 22,000.00
$ 350.00 $ 100.00 $ 300.00 296 116 $ 200.00 $ 175.00 $ 29,600.00 $ 34,800.00 $ 59,200.00 $ 20,300.00 $ (29,600.00) $ 14,500.00
$ 400.00 $ 150.00 $ 200.00 234 197 $ 200.00 $ 175.00 $ 35,100.00 $ 39,400.00 $ 46,800.00 $ 34,475.00 $ (11,700.00) $ 4,925.00
$ 450.00 $ 498.00 $ 475.00 133 48 $ 200.00 $ 175.00 $ 66,234.00 $ 22,800.00 $ 26,600.00 $ 8,400.00 $ 39,634.00 $ 14,400.00
$ 500.00 $ 250.00 $ 350.00 229 102 $ 200.00 $ 175.00 $ 57,250.00 $ 35,700.00 $ 45,800.00 $ 17,850.00 $ 11,450.00 $ 17,850.00
$ 550.00 $ 300.00 $ 389.00 215 82 $ 200.00 $ 175.00 $ 64,500.00 $ 31,898.00 $ 43,000.00 $ 14,350.00 $ 21,500.00 $ 17,548.00
$ 380.00 $ 350.00 $ 150.00 105 263 $ 200.00 $ 175.00 $ 36,750.00 $ 39,450.00 $ 21,000.00 $ 46,025.00 $ 15,750.00 $ (6,575.00)
$ 210.00 $ 400.00 $ 398.00 162 90 $ 200.00 $ 175.00 $ 64,800.00 $ 35,820.00 $ 32,400.00 $ 15,750.00 $ 32,400.00 $ 20,070.00
$ 330.00 $ 450.00 $ 300.00 100 169 $ 200.00 $ 175.00 $ 45,000.00 $ 50,700.00 $ 20,000.00 $ 29,575.00 $ 25,000.00 $ 21,125.00
$ 700.00 $ 500.00 $ 100.00 4 322 $ 200.00 $ 175.00 $ 2,000.00 $ 32,200.00 $ 800.00 $ 56,350.00 $ 1,200.00 $ (24,150.00)
$ 475.00 $ 550.00 $ 275.00 36 202 $ 200.00 $ 175.00 $ 19,800.00 $ 55,550.00 $ 7,200.00 $ 35,350.00 $ 12,600.00 $ 20,200.00
$ 380.00 $ 402.00 174 84 $ 200.00 $ 175.00 $ 66,120.00 $ 33,768.00 $ 34,800.00 $ 14,700.00 $ 31,320.00 $ 19,068.00
$ 210.00 $ 300.00 235 133 $ 200.00 $ 175.00 $ 49,350.00 $ 39,900.00 $ 47,000.00 $ 23,275.00 $ 2,350.00 $ 16,625.00
$ 330.00 $ 480.00 229 20 $ 200.00 $ 175.00 $ 75,570.00 $ 9,600.00 $ 45,800.00 $ 3,500.00 $ 29,770.00 $ 6,100.00
$ 700.00 $ 500.00 28 60 $ 200.00 $ 175.00 $ 19,600.00 $ 30,000.00 $ 5,600.00 $ 10,500.00 $ 14,000.00 $ 19,500.00
$ 475.00 $ 505.00 156 23 $ 200.00 $ 175.00 $ 74,100.00 $ 11,615.00 $ 31,200.00 $ 4,025.00 $ 42,900.00 $ 7,590.00

Part c Goal Seek

Price per unit Buying price per unit Unit sold Revenue
Brand A $ 31.15 $ 200.00 2408 $ 75,000.00
Brand B $ 35.94 $ 175.00 2087 $ 75,000.00

Part d Data Table

Brand A Brand B Total revenue $ 23,000.00
price $ 150 price $ 250
unit 70 unit 50
$ 10,500.00 $ 12,500
$ 10,500.00 200 250 300 350 400 450 550 600
72 14400 18000 21600 25200 28800 32400 39600 43200
296 59200 74000 88800 103600 118400 133200 162800 177600
234 46800 58500 70200 81900 93600 105300 128700 140400
133 26600 33250 39900 46550 53200 59850 73150 79800
229 45800 57250 68700 80150 91600 103050 125950 137400
215 43000 53750 64500 75250 86000 96750 118250 129000
105 21000 26250 31500 36750 42000 47250 57750 63000
162 32400 40500 48600 56700 64800 72900 89100 97200
100 20000 25000 30000 35000 40000 45000 55000 60000
4 800 1000 1200 1400 1600 1800 2200 2400
36 7200 9000 10800 12600 14400 16200 19800 21600
174 34800 43500 52200 60900 69600 78300 95700 104400
235 47000 58750 70500 82250 94000 105750 129250 141000
229 45800 57250 68700 80150 91600 103050 125950 137400
28 5600 7000 8400 9800 11200 12600 15400 16800
156 31200 39000 46800 54600 62400 70200 85800 93600
Total Revenue 481600 602000 722400 842800 963200 1083600 1324400 1444800
Brand B
$ 12,500.00 200 250 300 350 400 450 550 600
176 12500 12500 12500 12500 12500 12500 12500 12500
116 12500 12500 12500 12500 12500 12500 12500 12500
197 12500 12500 12500 12500 12500 12500 12500 12500
48 12500 12500 12500 12500 12500 12500 12500 12500
102 12500 12500 12500 12500 12500 12500 12500 12500
82 12500 12500 12500 12500 12500 12500 12500 12500
263 12500 12500 12500 12500 12500 12500 12500 12500
90 12500 12500 12500 12500 12500 12500 12500 12500
169 12500 12500 12500 12500 12500 12500 12500 12500
322 12500 12500 12500 12500 12500 12500 12500 12500
202 12500 12500 12500 12500 12500 12500 12500 12500
84 12500 12500 12500 12500 12500 12500 12500 12500
133 12500 12500 12500 12500 12500 12500 12500 12500
20 12500 12500 12500 12500 12500 12500 12500 12500
60 12500 12500 12500 12500 12500 12500 12500 12500
23 12500 12500 12500 12500 12500 12500 12500 12500
Total Revenue 200000 200000 200000 200000 200000 200000 200000 200000

Part f Solver

Price per unit cost price per unit Unit sold Revenue Cost Profit
Brand A $ 200.00 $ - 0 $ - 0 $ - 0
Brand B $ 175.00 $ - 0 $ - 0 $ - 0
Total $ - 0 $ - 0 $ - 0
the unit selling price will be 100 for brand B and the company should not sell brand A to get maximum profit
A B
unit cost price 200 175
unit selling price 0 100
unit sold 72 85
z 8500
100
Total revenue 8500 >= 100000