A company sells a product in four markets. The manager of decided to focus only on two
Question:
A company sells a product in four markets. The manager of decided to focus only on two markets instead on four markets.
• The Quantity Produced should be 7500 per market in the first year and it is expected to grow by 6% every year.
• The Cost Per Unit is $90 and expected to decrease by 5% every year.
• The Price Per Unit is $180 and expected to decrease by 4% per year.
The mangers Knows that the Quantity Sold and its Growth Rate is different in every market. So, he decided to develop a Planning Model and calculate the NPV for every market to find out which markets are going to be more profitable “best two markets”.
Qusetion1: Develop an excel model that calculate the profits over 5 years for the First Market. Knowing that the Total Quantity Sold for year-1 should be forecasted and calculated from the below table (the price is given for each month, and the sale is depending on the period and price). Any $5 dollar change in price will change 45 units sold.
Month | n | Price | Qty Sold |
January | 1 | $183 | 400 |
February | 2 | $200 | 275 |
March | 3 | $180 | 422 |
April | 4 | $177 | 450 |
May | 5 | $196 | 289 |
June | 6 | $186 | 450 |
July | 7 | $136 | ? |
August | 8 | $133 | ? |
September | 9 | $136 | ? |
October | 10 | $180 | ? |
November | 11 | $153 | ? |
December | 12 | $160 | ? |
decision support system
**Please solve in excel sheet with detailed steps**
solve it in excel using excel functions and formulas such as linear, moving average, exponential smoothing etc..
Financial Reporting and Analysis
ISBN: 978-0078025679
6th edition
Authors: Flawrence Revsine, Daniel Collins, Bruce, Mittelstaedt, Leon