for a new product sales volume in the first year is estimated to be 80,000 units and is projected to grow at a rate of 4 % per year the selling price is 12 and will increase by 0.50 each year. per unit variable costs are 3 and annual fixed costs are 4000,000. per-unit costs are expected to increase 5% per year. fixed costs are expected to increase 8% per year. develop a spreadsheet model to calculate the net present value of profit over a 3 year period, assuming a 4% discount rate.
for a new product sales volume in the first year is estimated to be 80,000 units and is projected to grow at a rate of 4 % per year the selling price is 12 and will increase by 0.50 each year. per unit variable costs are 3 and annual fixed costs are 4000,000. per-unit costs are expected to increase 5% per year. fixed costs are expected to increase 8% per year. develop a spreadsheet model to calculate the net present value of profit over a 3 year period, assuming a 4% discount rate.
Related questions
| Complete the incremental cash flows work sheet for each project. You are provided with an ExcelĆĀ® worksheet for each project that lists the initial variable costs per unit, total fixed costs, depreciation expense and number of units sold. These are incurred on a quarterly basis (i.e. m = 4). The sales price and almost all associated costs and expenses are expected to grow at an annual rate of 3% per year over the life of each project. Depreciation expense will remain constant. The number of units sold for Project 1 is expected decrease at an annual rate of 7.5000%. The number of units sold for Project 2 is expected increase at an annual rate of 9.5000%. The number of units sold for Project 3 is expected increase at an annual rate of 4.000%. The profits of all projects are taxed at an annual rate of 35%. Project 1 | |||||||||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| Variable Costs per Unit | |||||||||||||
| Raw Materials | $9.00 | ||||||||||||
| Manufactured Components | $22.00 | ||||||||||||
| Purchased Components | $35.00 | ||||||||||||
| Labor | $22.00 | ||||||||||||
| Internal Transp. & Storage | $0.50 | ||||||||||||
| Energy | $1.25 | ||||||||||||
| Total Variable Costs Per Unit | $89.75 | ||||||||||||
| Sales Price Per Unit | $150.25 | ||||||||||||
| Units Sold | 30,000 | ||||||||||||
| Total Sales | $4,507,500 | ||||||||||||
| Total Variable Costs | $2,692,500 | ||||||||||||
| Total Fixed Costs | $90,000 | ||||||||||||
| Total COGS | $2,782,500 | ||||||||||||
| Gross Profit | $1,725,000 | ||||||||||||
| Gen, Sales & Admin Expense | $5,000 | ||||||||||||
| Depreciation | $40,000 | ||||||||||||
| EBIT(Earn. Before Int. & Tax) | $1,680,000 | ||||||||||||
| Income Tax | $588,000 | ||||||||||||
| Incremental Earnings | $1,092,000 | ||||||||||||
| Depreciation Add Back | $40,000 | ||||||||||||
| Incremental Free Cash Flow | -$5,485,000 | $1,132,000 | |||||||||||
| Units Sold Growth per yr. | -7.50% | ||||||||||||
| Costs Growth/Decline per yr. | 3.00% | ||||||||||||
| Tax Rate | 35.00% | ||||||||||||
| Payment per year | 4 |
