Statistics Homework (NEED PhStat in Excel)
reneo.kena87
m5a1_problems.xlsxProblem 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.
