Question

Q2) Chemical engineers at a Coleman Industries plant in the Midwest have determined that a small amount of a newly available chemical additive will increase the water repellency of Coleman's tent fabric by 20%. The plant superintendent has arranged to purchase the additive through a 7 year contract at $9000 per year, starting 1 year from now. He expects the annual price to increase by 12% per year starting in the 8th year and thereafter through year 15. Additionally, an initial investment of $45,000 was made now to prepare a site suitable for the contractor to deliver the additive. Use i-15% per year to determine the equivalent total present worth for all these cash flows.

Answer 1

Book1 - Microsoft Excel - 0 X Home Insert Page Layout Formulas Data RP View Developer Caitri General copy 11 . XX . . D A Font --= F > Wrap Test F Merge Center Alignment Normal Conditional Formatas Neutral Formatting Table Bad Calculation Good check Cell . E 28: - % Number - 3 Insert Delete Format Cel Autasum - Y A Care Sorte Find & Editing Fiter - Select Styles Paste Format Painter Clipboard 122 A B C D E F G H I J K L M N O rate 15% year initail cost 45000 cash flows pv as on year o $ 45,000.00 9000.00 $ 7,826.09 9000.00 $ 6,805.29 9000.00 $ 5,917.65 9000.00 $ 5,145.78 9000.00 $ 4,474.59 9000.00 $ 3,890.95 9000.00 $ 3,383.43 10080.00 $ 3,295.17 11289.60 $ 3,209.21 12644.35 $ 3,125.49 14161.67 3,043.96 15861.08 $ 2,964.55 17764.40 $ 2,887.21 19896.13 $ 2,811.89 22283.67 $ 2,738.54 total pv of project $ 1,06,519.80 20 21 22 * * Sheet1 Sheetz Sheets Ready 0 1408 @ on AOC) ENG 05 Type here to search de 05-03-2020

Given rate = 15% per year

nper = 15

initial cost = 45000, which happens in year 0

9000 paid one year from now means, the payment was made in year 1. This 9000 is annual payments made till year 7. From year 8 this is going to increase by 12% per year till year 15.

As seen in the image above, i have plotted the cash flows year wise. From 8th year cash flow is going to increase by 12%. To show that in the table use the following formula

=F12*(1+12%)

=10080.00

now drag this formula till year 15. We will get cash flow till year 15.

Now discount each of these cash flows to year 0. In the image i have showed this in the column pv as on year 0

We dont have to discount initial cost because it is happening in year 0. So pv of 45000 will be 45000 itself. (no time value of money)

To discount year 1 cash flow to year 0 use the following formula

=-PV($E$2,D6,0,F6,0)

=7826.09

drag this formula till year 15 to get pv of all the cash flows.

To find total present worth of the project sum all these pv of cash flows as on year 0. i.e

total pv of the project =SUM(G5:G20)

=106519.80