In: Economics
The Ashton Group is developing a new product line. Initial costs for the line are $94693. Annual utilities will be $34518. The company plans to concentrate on marketing for the first 2 years at a cost of $11430 per year. Profits are anticipated to be zero for the first few years. It is estimated the product line will finally have a profit of $469315 at the end of year 6 and profits will continue to increase by 20% each subsequent year. The new product line will require 7 employees for the first 8 years. The company will then hire 5 additional employees for the remainder of the product line lifespan. Employees are paid an average of $59104 per year. Using a lifespan of 17 years and a nominal annual interest rate of 4% compounded annually, what is the equivalent uniform annual worth of the new product line?
Investment = 94693
Annual utilities = 34518
marketing cost for 2 year = 11430
Profit from 6th yr =469315 increasing at 20% per year
Cost of each employee = 59104
I =4% = 0.04
t = 17 yrs
We need to find net present value and then using factor (A/P, 4%,17yrs) convert it to annual series
using excel
Year | Invesetment | Employee payment | Utilities | Profit | Marketing costs | Net Cash Flow | Discount | Present value |
0 | -94,693.00 | -94,693.00 | 1.000000 | -94,693.00 | ||||
1 | -4,13,728.00 | -34,518.00 | -11,430.00 | -4,59,676.00 | 0.961538 | -4,41,996.15 | ||
2 | -4,13,728.00 | -34,518.00 | -11,430.00 | -4,59,676.00 | 0.924556 | -4,24,996.30 | ||
3 | -4,13,728.00 | -34,518.00 | -4,48,246.00 | 0.888996 | -3,98,489.06 | |||
4 | -4,13,728.00 | -34,518.00 | -4,48,246.00 | 0.854804 | -3,83,162.56 | |||
5 | -4,13,728.00 | -34,518.00 | -4,48,246.00 | 0.821927 | -3,68,425.54 | |||
6 | -4,13,728.00 | -34,518.00 | 4,69,315.00 | 21,069.00 | 0.790315 | 16,651.14 | ||
7 | -4,13,728.00 | -34,518.00 | 5,63,178.00 | 1,14,932.00 | 0.759918 | 87,338.87 | ||
8 | -4,13,728.00 | -34,518.00 | 6,75,813.60 | 2,27,567.60 | 0.730690 | 1,66,281.42 | ||
9 | -7,09,248.00 | -34,518.00 | 8,10,976.32 | 67,210.32 | 0.702587 | 47,221.08 | ||
10 | -7,09,248.00 | -34,518.00 | 9,73,171.58 | 2,29,405.58 | 0.675564 | 1,54,978.19 | ||
11 | -7,09,248.00 | -34,518.00 | 11,67,805.90 | 4,24,039.90 | 0.649581 | 2,75,448.23 | ||
12 | -7,09,248.00 | -34,518.00 | 14,01,367.08 | 6,57,601.08 | 0.624597 | 4,10,735.69 | ||
13 | -7,09,248.00 | -34,518.00 | 16,81,640.50 | 9,37,874.50 | 0.600574 | 5,63,263.12 | ||
14 | -7,09,248.00 | -34,518.00 | 20,17,968.60 | 12,74,202.60 | 0.577475 | 7,35,820.25 | ||
15 | -7,09,248.00 | -34,518.00 | 24,21,562.32 | 16,77,796.32 | 0.555265 | 9,31,620.74 | ||
16 | -7,09,248.00 | -34,518.00 | 29,05,874.78 | 21,62,108.78 | 0.533908 | 11,54,367.55 | ||
17 | -7,09,248.00 | -34,518.00 | 34,87,049.73 | 27,43,283.73 | 0.513373 | 14,08,328.48 | ||
Net Present value | 38,40,292.15 |
Now for annual worth = Present worth * (A/P,4%,17yrs)
= 3840292.15 * 0.0821985
= 315666.34
Showing formula in excel
Year | Invesetment | Employee payment | Utilities | Profit | Marketing costs | Net Cash Flow | Discount | Present value |
0 | -94693 | =B3+C3+D3+E3+F3 | =1/(1+0.04)^A3 | =G3*H3 | ||||
1 | =7*-59104 | -34518 | -11430 | =B4+C4+D4+E4+F4 | =1/(1+0.04)^A4 | =G4*H4 | ||
2 | =7*-59104 | -34518 | -11430 | =B5+C5+D5+E5+F5 | =1/(1+0.04)^A5 | =G5*H5 | ||
3 | =7*-59104 | -34518 | =B6+C6+D6+E6+F6 | =1/(1+0.04)^A6 | =G6*H6 | |||
4 | =7*-59104 | -34518 | =B7+C7+D7+E7+F7 | =1/(1+0.04)^A7 | =G7*H7 | |||
5 | =7*-59104 | -34518 | =B8+C8+D8+E8+F8 | =1/(1+0.04)^A8 | =G8*H8 | |||
6 | =7*-59104 | -34518 | 469315 | =B9+C9+D9+E9+F9 | =1/(1+0.04)^A9 | =G9*H9 | ||
7 | =7*-59104 | -34518 | =E9*(1+0.2) | =B10+C10+D10+E10+F10 | =1/(1+0.04)^A10 | =G10*H10 | ||
8 | =7*-59104 | -34518 | =E10*(1+0.2) | =B11+C11+D11+E11+F11 | =1/(1+0.04)^A11 | =G11*H11 | ||
9 | =(7+5)*-59104 | -34518 | =E11*(1+0.2) | =B12+C12+D12+E12+F12 | =1/(1+0.04)^A12 | =G12*H12 | ||
10 | =(7+5)*-59104 | -34518 | =E12*(1+0.2) | =B13+C13+D13+E13+F13 | =1/(1+0.04)^A13 | =G13*H13 | ||
11 | =(7+5)*-59104 | -34518 | =E13*(1+0.2) | =B14+C14+D14+E14+F14 | =1/(1+0.04)^A14 | =G14*H14 | ||
12 | =(7+5)*-59104 | -34518 | =E14*(1+0.2) | =B15+C15+D15+E15+F15 | =1/(1+0.04)^A15 | =G15*H15 | ||
13 | =(7+5)*-59104 | -34518 | =E15*(1+0.2) | =B16+C16+D16+E16+F16 | =1/(1+0.04)^A16 | =G16*H16 | ||
14 | =(7+5)*-59104 | -34518 | =E16*(1+0.2) | =B17+C17+D17+E17+F17 | =1/(1+0.04)^A17 | =G17*H17 | ||
15 | =(7+5)*-59104 | -34518 | =E17*(1+0.2) | =B18+C18+D18+E18+F18 | =1/(1+0.04)^A18 | =G18*H18 | ||
16 | =(7+5)*-59104 | -34518 | =E18*(1+0.2) | =B19+C19+D19+E19+F19 | =1/(1+0.04)^A19 | =G19*H19 | ||
17 | =(7+5)*-59104 | -34518 | =E19*(1+0.2) | =B20+C20+D20+E20+F20 | =1/(1+0.04)^A20 | =G20*H20 | ||
Net Present value | =SUM(I3:I20) |