Quantitative methods for Caspiano only
pcwc0506
m5a1-v11-28-16-problems.xlsxProblem and Data
| Price Brand A | Price Brand B | Units Sold Brand A | Units Sold Brand B |
| $ 500.00 | $ 300.00 | 176 | 72 |
| $ 100.00 | $ 300.00 | 116 | 296 |
| $ 150.00 | $ 200.00 | 197 | 234 |
| $ 498.00 | $ 475.00 | 48 | 133 |
| $ 250.00 | $ 350.00 | 102 | 229 |
| $ 300.00 | $ 389.00 | 82 | 215 |
| $ 350.00 | $ 150.00 | 263 | 105 |
| $ 400.00 | $ 398.00 | 90 | 162 |
| $ 450.00 | $ 300.00 | 169 | 100 |
| $ 500.00 | $ 100.00 | 322 | 4 |
| $ 550.00 | $ 275.00 | 202 | 36 |
| $ 380.00 | $ 402.00 | 84 | 174 |
| $ 210.00 | $ 300.00 | 133 | 235 |
| $ 330.00 | $ 480.00 | 20 | 229 |
| $ 700.00 | $ 500.00 | 60 | 28 |
| $ 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 $215 per unit from its manufacturer and Brand B for $190 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 "Model". Set Price A to $200 and Price B to $250. c. Copy your Part b model to a new worksheet named "Goal Seek A." Use Excel's Goal Seek tool to find the prices for both brands: 1. Set Price A to be $200 and find the Price B that will produce a total revenue of $90,000. 2. Copy your model to a new worksheet named " Goal Seek B." Set Price B to be $175 and find the Price A that will yield a total profit of $90,000. d. Copy your original Part b model to a new worksheet named "Data Table & Scenario" 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 "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 must sell at least 100 units of Brand A and can buy no more than 200 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.
