08 Jul Quantitative methods for Caspiano
Problem 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.
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.