Question
[Company] expects that free cash flow will increase at a constant rate of 2%/year starting from Year 7 into the indefinite future (e.g., Year 7
[Company] expects that free cash flow will increase at a constant rate of 2%/year starting from Year 7 into the indefinite future (e.g., Year 7 FCF=(1+2%)*Year 6 FCF, Year 8 FCF= (1+2%)*Year 7 FCF, etc., but NOT Year 6 FCF=(1+2%)*Year 5 FCF). Calculate PV(terminal value that captures the value of future free cash flows in Year 6 and beyond) (Yes, Year 6 and beyond, NO typo here). That is, calculate the terminal value first, then find its value in Year 0 (today). If you need to calculate free cash flow for Year 7 or later, use the free cash flow growth rate and the previous years free cash flow.
*I've calculated everything else and I only need assistance with b56&57. I'm just getting stuck on finding the pv(terminal value) that I've highlighted on the excel document. Can you also include the excel formula you used to find the answer for cells b56&57
B C D E F G H I J K Year 0 Year Year Year Year Year Year Year 1 1 2 3 4 5 6 7 30,000,000 31,800,000 33,708,000 35,730,480 37,874,309 40,146,767 40,949,703 % 6% 6% 6% 6% 6% 2% 21,600,000 22,896,000 24,269,760 25,725,946 27,269,502 28,905,672 29,483,786 5,000,000 5,000,000 5,000,000 5,000,000 5,000,000 5,000,000 5,000,000 1,800,000 1,800,000 1,800,000 1,800,000 1,800,000 0 0 1,600,000 2,104,000 2,638,240 3,204,534 3,804,806 6,241,095 6,465,917 480,000 631,200 791,472 961,360 1,141,442 1,872,328 1,939,775 1,120,000 1,472,800 1,846,768 2,243,174 2,663,365 4,368,766 4,526,142 0 22 23 Unlevered Income Statements 24 Sales 25 y-O-y growth 26 COGS 27 S, G, & A expense 28 Depreciation 29 EBIT 30 Taxes 31 Unlevered Net Income 32 33 34 35 36 Working Capital Calculations 37 Inventory 38 Accounts Receivable 39 Accounts Payable 40 NWC Level 41 Change in NWC 42 CF from Change in NWC 43 44 Unlevered Cash Flows 45 Unlevered Net Income 46 Add Back: Depreciation 47 CF from Change in NWC 48 CF from Capital Expenditure 49 Free Cash Flow 50 Terminal Value (FCFs in Year 6 and beyond) 51 52 Discount Factor (1/(1+r)^n) 53 PV(FCF) 54 NPV 55 in which 56 PV(Terminal Value) 57 PV(Terminal Value)/NPV 4,579,200 4,770,000 3,434,400 5,914,800 -85,200 85,200 4,853,952 5,056,200 3,640,464 6,269,688 354,888 -354,888 5,145,189 5,359,572 3,858,892 6,645,869 376,181 -376,181 5,453,900 5,681,146 4,090,425 7,044,621 398,752 -398,752 5,781,134 6,022,015 4,335,851 7,467,299 422,677 -422,677 5,896,757 6,142,455 4,422,568 7,616,645 149,346 - 149,346 6,000,000 6,000,000 -6,000,000 0 0 0 -7,616,645 7,616,645 1,120,000 1,800,000 85,200 1,472,800 1,800,000 -354,888 1,846,768 1,800,000 -376,181 2,243, 174 1,800,000 -398,752 2,663,365 1,800,000 -422,677 4,368,766 0 0 -149,346 4,526,142 0 7,616,645 -6,000,000 -9,000,000 -15,000,000 3,005, 200 2,917,912 3,270,587 3,644,422 4,219,420 12,142,786 4,040,687 23,441,225 0.833 2,504,333 Discount Factor 0.694 2,026,328 0.579 1,892,701 0.482 1,757,534 0.402 1,623,862 1 0.335 PV = FV X (1+r)" 1,413,075 -15,000,000 4,225,259
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started