-2345 1 A B 19 After tax Salvage Value in year 6 20 Cost of capital 21 Tax rate D G 2516 5 H F E Discounted Cash-Flow Analysis Estimating depreciation of an asset, calculating cash flows, and estimating NPV. Problem 1 I K L 6 7 8 Diltz Farms is B13 investing in an automated egg-sorting system to increase production for international (web-based) sales of Diltz Farms' products. The new system will cost $2516 including installation. It will be fully depreciated in 5 yrs.(straight-line) to zero and generate $107 after-tax gain at the end of the projected period (year 6). The initial working captital will be $304 and will be $558 in year one and increase each year thereafter by 5 percent. Assume that at year 0, there is no change in working capital. 9 Revenues generated from the egg-sorter are expected to be $867 in year one, and increase by five percent each year. Expenses are ten percent of revenues. Diltz Farms' cost 10 of capital is 6.7%. Using the discounted cash-flow analysis, should Diltz Farms invest in the machinery? What is the NPV of the egg-sorter project? Asume Tax rate as 35% 11 12 13 14 Cost of the Asset 15 Life of the Asset in Years 16 Book Value of the Asset after 5 years 17 Depreciable Basis 18 Yearly depreciation M Identify, the values from the questions for cells C14, C15, C16, C17, C19, C20 and C21. Depreciable Basis = Cost of the Asset - Book Value at the end of its life Sometimes, if we do not have information about Book Value of an asset at the end of its useful life, we might use Salvage Value in those cases to find depreciation. Initial Investment Salvage Value Working capital Change in Wk Cap Revenues Expenses Depreciation Pretax profit Tax (35%) Profit after tax Year: CF from capital investments CF from working capital CF from operations Total cash flows Discount factor PV of cash flow Net present value using Discount factor IRR using formula Cell C18 C37 Score for problem 1 Max points 5 Points Scored 0 2516 0 0 Cash Flow: 1 2 3 If you directly use NPV formula, you will not get points. If you use GoalSeek for IRR, you will not get points. 5 107 In cell C23 enter: =C14 In cell 124 enter: =C19 In cell C25 enter the initial working capital from the problem In cell D25 enter the working capital for year 1 from the problem In cell E25 enter :=1.05*D25 and then drag it till H25 In cell C26 enter 0 In cell D26 enter: =D25-C25 and then drag it till 126 In cell D27 enter the revenue for year 1 from the problem In cell E27 enter: =D27*1.05 and then drag it till H27 In cell D28 enter: = D27*0.1 In cell E28 enter: =D28*1.05 and then drag it till H28 In cell D29 enter: =$C$18 and then drag it till H29 In cell D30 enter: -D27-D28-D29 and then drag it till H30 In cell D31 enter: -SC$21*D30 and then drag it till H31 In cell D32 enter: =D30-D31 and then drag it till H32 In cell C34 enter: --C23 In cell C35 enter: --C26 and drag it till H35 In cell 135 enter: =-126+124 In cell D36 enter: =D32+D29 and drag it till H36 In cell C37 enter: =sum(C34,C35,C36) and drag it till 137 In cell C38 enter: =1/(1+SC$20)^C22 and then drag it till 138 In cell C39 enter: =C37*C38 and then drag it till 139 In cell C42 enter: =sum(C39:139) In cell C43 find IRR using only the formula (only use the formula but do not use GoalSeek function. You learnt those in Chapter 9 Spreadsheet Mastery). Hint: Use IRR formula for Total Cash Flows -2345 1 A B 19 After tax Salvage Value in year 6 20 Cost of capital 21 Tax rate D G 2516 5 H F E Discounted Cash-Flow Analysis Estimating depreciation of an asset, calculating cash flows, and estimating NPV. Problem 1 I K L 6 7 8 Diltz Farms is B13 investing in an automated egg-sorting system to increase production for international (web-based) sales of Diltz Farms' products. The new system will cost $2516 including installation. It will be fully depreciated in 5 yrs.(straight-line) to zero and generate $107 after-tax gain at the end of the projected period (year 6). The initial working captital will be $304 and will be $558 in year one and increase each year thereafter by 5 percent. Assume that at year 0, there is no change in working capital. 9 Revenues generated from the egg-sorter are expected to be $867 in year one, and increase by five percent each year. Expenses are ten percent of revenues. Diltz Farms' cost 10 of capital is 6.7%. Using the discounted cash-flow analysis, should Diltz Farms invest in the machinery? What is the NPV of the egg-sorter project? Asume Tax rate as 35% 11 12 13 14 Cost of the Asset 15 Life of the Asset in Years 16 Book Value of the Asset after 5 years 17 Depreciable Basis 18 Yearly depreciation M Identify, the values from the questions for cells C14, C15, C16, C17, C19, C20 and C21. Depreciable Basis = Cost of the Asset - Book Value at the end of its life Sometimes, if we do not have information about Book Value of an asset at the end of its useful life, we might use Salvage Value in those cases to find depreciation. Initial Investment Salvage Value Working capital Change in Wk Cap Revenues Expenses Depreciation Pretax profit Tax (35%) Profit after tax Year: CF from capital investments CF from working capital CF from operations Total cash flows Discount factor PV of cash flow Net present value using Discount factor IRR using formula Cell C18 C37 Score for problem 1 Max points 5 Points Scored 0 2516 0 0 Cash Flow: 1 2 3 If you directly use NPV formula, you will not get points. If you use GoalSeek for IRR, you will not get points. 5 107 In cell C23 enter: =C14 In cell 124 enter: =C19 In cell C25 enter the initial working capital from the problem In cell D25 enter the working capital for year 1 from the problem In cell E25 enter :=1.05*D25 and then drag it till H25 In cell C26 enter 0 In cell D26 enter: =D25-C25 and then drag it till 126 In cell D27 enter the revenue for year 1 from the problem In cell E27 enter: =D27*1.05 and then drag it till H27 In cell D28 enter: = D27*0.1 In cell E28 enter: =D28*1.05 and then drag it till H28 In cell D29 enter: =$C$18 and then drag it till H29 In cell D30 enter: -D27-D28-D29 and then drag it till H30 In cell D31 enter: -SC$21*D30 and then drag it till H31 In cell D32 enter: =D30-D31 and then drag it till H32 In cell C34 enter: --C23 In cell C35 enter: --C26 and drag it till H35 In cell 135 enter: =-126+124 In cell D36 enter: =D32+D29 and drag it till H36 In cell C37 enter: =sum(C34,C35,C36) and drag it till 137 In cell C38 enter: =1/(1+SC$20)^C22 and then drag it till 138 In cell C39 enter: =C37*C38 and then drag it till 139 In cell C42 enter: =sum(C39:139) In cell C43 find IRR using only the formula (only use the formula but do not use GoalSeek function. You learnt those in Chapter 9 Spreadsheet Mastery). Hint: Use IRR formula for Total Cash Flows
