Question

1. For a new product, sales volume in the first year is estimated to be 50,000...

1. For a new product, sales volume in the first year is estimated to be 50,000 units and is projected to grow at a rate of 7% per year. The selling price is $100 and will increase by $10 each year. Per-unit variable costs are $22 and annual fixed costs are $1,000,000. Per-unit costs are expected to increase 4% per year. Fixed costs are expected to increase 10% per year. Develop a spreadsheet model to predict the net present value of profit over a three-year period, assuming a 4% discount rate.

How to solve this question on excel, please show formulas as well. :)

Business   Accounting   
0 0
Add a commentTranscribed image text
Next >

Homework Answers

Answer #1
Net present Value of Profit
Particulars Year 1 Year 2 Year 3
No. of Units 50000 53500 57245
Selling Price / unit $100 $110 $120
Less: Variable Cost / unit $22 $22.88 $23.80
Contribution / Unit $78 $87 $96
Contribution $3,900,000 $4,660,920 $5,507,244
Fixed Cost $1,000,000 $1,100,000 $1,210,000
Net Profit $2,900,000 $3,560,920 $4,297,244
Present value factor= 1/(1+0.04)^n 0.961538462 0.924556213 0.888996359
Net present value of Profit $2,788,461.54 $3,292,270.71 $3,820,234.07
Total Present Value of Profit $9,900,966.32
0 0
Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
15. Excel hardware is introducing a new product on a new product line of capacity 800...
15. Excel hardware is introducing a new product on a new product line of capacity 800 units per week at a production cost of $50 per unit. Fixed costs are $22,400 per week. Variable selling and shipping costs are estimated to be $20 per unit. Excel plan to market the new product at $110 per unit. What is the break-even capacity per week? 16. Excel hardware is introducing a new product on a new product line of capacity 800 units...
The following information is available for year 1 for Dancer Components: Sales revenue (220,000 units) $...
The following information is available for year 1 for Dancer Components: Sales revenue (220,000 units) $ 6,160,000 Manufacturing cost Materials $ 363,000 Variable cash cost 308,000 Fixed cash cost 708,000 Depreciation (fixed) 2,159,000 Marketing and administrative costs Marketing (variable, cash) 913,000 Marketing depreciation 323,000 Administrative (fixed, cash) 1,099,000 Administrative depreciation 161,000 Total costs $ 6,034,000 Operating profit $ 126,000 Variable marketing costs to change with unit volume. Administrative cash cost is expected to increase by 3 percent. Inventories will be...
A small sporting goods company is considering investing $2500 in a project that will produce volleyballs...
A small sporting goods company is considering investing $2500 in a project that will produce volleyballs over the next five years. The company plans to produce and sell 300 volleyballs in the first year, and expects that volume to grow by 10% each year thereafter. The unit selling price forecast the company has developed is $21 in year 1, $22 in year 2, $25 in year 3, $28 in year 4, and $31.50 in year 5. Variable costs are forecast...
The following information is available for year 1 for Pepper Products:    Sales revenue (190,000 units)...
The following information is available for year 1 for Pepper Products:    Sales revenue (190,000 units) $ 3,800,000 Manufacturing costs Materials $ 224,000 Variable cash costs 190,000 Fixed cash costs 438,000 Depreciation (fixed) 1,336,000 Marketing and administrative costs Marketing (variable, cash) 565,000 Marketing depreciation 201,000 Administrative (fixed, cash) 684,000 Administrative depreciation 100,000 Total costs $ 3,738,000 Operating profits $ 62,000     All depreciation charges are fixed and are expected to remain the same for year 2. Sales volume is expected...
The following information is available for year 1 for Dancer Components: Sales revenues (210,000 units) $...
The following information is available for year 1 for Dancer Components: Sales revenues (210,000 units) $ 3,150,000 Manufacturing costs Materials $ 167,000 Variable cash costs 150,300 Fixed cash costs 322,500 Depreciation 994,000 Marketing and administrative costs Marketing (variable, cash) 426,800 Marketing depreciation 143,800 Administrative (fixed, cash) 518,600 Administrative depreciation 77,100 Total costs $ 2,800,100 Operating profits $ 349,900 All depreciation charges are fixed and are expected to remain the same for year 2. Sales volume is expected to increase by...
HAND CALCULATIONS ONLY. NO EXCEL Pepsico’s most recent dividend was $1. The dividend next year is...
HAND CALCULATIONS ONLY. NO EXCEL Pepsico’s most recent dividend was $1. The dividend next year is expected to increase 10%. The dividend is expected to continue growing at 10% for two addition years after that. After these three years of strong growth, the stock is then expected to grow at a constant 4% thereafter. Calculate the intrinsic value using the multi-stage dividend discount model. Pepsico’s required rate of return is 8%.
The following information is available for year 1 for Pepper Products:    Sales revenue (260,000 units)...
The following information is available for year 1 for Pepper Products:    Sales revenue (260,000 units) $ 7,020,000 Manufacturing costs Materials $ 413,000 Variable cash costs 351,000 Fixed cash costs 808,000 Depreciation (fixed) 2,464,000 Marketing and administrative costs Marketing (variable, cash) 1,041,000 Marketing depreciation 369,000 Administrative (fixed, cash) 1,255,000 Administrative depreciation 185,000 Total costs $ 6,886,000 Operating profits $ 134,000     All depreciation charges are fixed and are expected to remain the same for year 2. Sales volume is expected...
You are evaluating a product for your company. You estimate the sales price of product to...
You are evaluating a product for your company. You estimate the sales price of product to be $120 per unit and sales volume to be 10,200 units in year 1; 25,200 units in year 2; and 5,200 units in year 3. The project has a 3 year life. Variable costs amount to $45 per unit and fixed costs are $202,000 per year. The project requires an initial investment of $330,000 in assets which will be depreciated straight-line to zero over...
Please add the explanation. 4. Your company may introduce a new line of tennis shoes. You...
Please add the explanation. 4. Your company may introduce a new line of tennis shoes. You have been given the following projections: sales = 35,000 units @ $40 per unit; variable costs = $25 per unit; fixed costs = $125,000 per year; initial investment = $1,000,000; interest expense = $50,000 per year; project life = 10 years. What is the net income for this project if the corporate tax rate is 34%? You may assume straight-line depreciation and a discount...
Manager of a company is considering investing in a new $40,000 machine. Use of the new...
Manager of a company is considering investing in a new $40,000 machine. Use of the new machine is expected to generate a cash flow of about $8,500 per year for each of the next five years. However, the cash flow is uncertain, and the manager estimates that the actual cash flow will be normally distributed with a mean of $8,500 and a standard deviation of $600. The discount rate is set at 4% and assumed to remain constant over the...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT