See Exhibit 1 where it has Panel A and Panel B. Reproduce both Panels in Excel.

FOR PANEL A:

1. Create and name the columns just like what you see in Panel A;
2. Enter all the assumptions given on top of Panel A in related columns in your spreadsheet, including the initial Sale of $100.00;
3. Then start populating the columns by using any needed formula; including the FCF formula. NOTE; to get the EBIATs and FA+WCs, you have the related ratios and Sales for each year;
4. Now you have the FCFs for each of the 11 years and are ready to find the Intrinsic Value of the FCFs via Panel B that follows.

FOR PANEL B:

To find the Intrinsic Value (IV), all you need is to recall how you learned to take Present Values (PV) of a sent figures; that is, the figure in the FCF column in Panel A. To do so, you will need a Discount Rate, aka required ROR (k) which is a proxy for Risk and a Terminal Growth Rate (g); that is the g for years 11-on. In that Panel, you have many ks and gs.

But as a start, find the IV for ALL the FCFs given in Panel A and assume k=10% and g=6%. If you take the PV of such FCFs you will arrive at an IV of $408.03 (your decimals may differ a bit due to rounding). You can do this first manually and then using Time Value functions in Excel.

Finally, once you can find the IV for the 10% k and 6% g, you can use the same formulation for IV for ALL the given ks and gs. In other words, your job in the IA is also to reproduce Panel B!

Cash Flows and Valuation Exhibit 1 Key Assumptions - - Years 6 - 10: Terminal Years (Year 11 on): Years 1 - 5: initial Sales Growth Rate EB1AT/S (FA+WC)/S 100.0 20.0% 10.0% 50.0% Growth Rate EBIAT/S (FA+WC)/S 15.0% 10.0% 50.0% Terminal Growth Rate Terminal EBIAT/S Terminal (FA+UC)/5 6.0% 10.0% 50.0% Growth R ate EBIAT/ Sales FA+WC/ Sales Change FA+UCFA+WC C - ative FCF FCF/ EBIAT Year Sales EBIAT FCF NA NA 0.0% 10.0% 2.0 NA 2.0 4.4 7.3 10.7 Sanel 0 1 2 3 4 5 6 7 8 9 10 11 100.0 120.0 144.0 172.8 207.4 248.8 286.2 329.1 378.4 435.2 500.5 530.5 NA 20.0% 20.0% 20.0% 20.0% 20.0% 15.0% 15.0% 15.0% 15.0% 15.0% 6.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% 0.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% NA 12.0 16.4 17.3 20.7 24.9 28.6 32.9 37.8 43.5 50.0 53.1 50.0 60.0 72.0 86.4 103.7 124.4 143.1 164.5 189.2 217.6 250.2 265.3 NA 10.0 12.0 14.4 17.3 20.7 18.7 21.5 24.7 28.4 32.6 15.0 2.4 2.9 3.5 4.1 10.0 11.4 13.2 15.1 17.4 38.0 NA 16.77 16.15 16.7% 16.7% 16.7% 34.8% 34.82 34.8% 34.82 34.8% 71.7% 24.8 36.3 9.4 66.6 82.0 120.0 Total Present Value of Free Cash Flows Terminal Growth Rate Discount - Rate 0.0% 0.0% 2.0% 3.0% 6.0% 5.0% 6.0% 7.0% 8.0% 10.0% 12.0% 14.0% 16.0% 18.0% 20.0% 22.0% 24.0% 26.0% 28.0% 30.0% Panel 234.6 171.2 129.3 100.2 79.4 54.1 52.5 43.7 36.7 31.3 247.5 177.5 132.5 101.9 80.3 64.5 52.7 43.7 36.7 31.2 26.8 263.6 185.2 136.3 103.9 81.3 65.0 52. 9 43.8 36.7 31.1 26.7 284.2 194.5 140.8 106. 1 82.4 65.5 53.1 43.8 36.6 31.1 26.6 311.8 206.1 146.2 108.7 83.7 56.1 53.4 43.9 36.6 31.0 26.5 350.4 221.1 152.8 111.8 85. 1 56.8 53.6 408.3 241.0 161.1 115.5 86.9 67.5 53.9 44.0 36.5 30.8 26.3 504.8 268.9 171.7 120.1 88.9 68.4 54.3 44.1 36.5 30.7 26.2 697.7 310.8 185.8 125.7 91.3 69.5 56.7 44.2 36.4 30.5 26.0 36.6 30.9 26.4 EBIAT = Earnings Before Interest, But After Taxes FA - Net Fixed Assets WC = Operating Working Capital FCF = Total Free Cash Flow = EBIAT . (Change in FA and WC) Total Present Value = Present Value of FCF - Present Value of Terminal Value Terminal Value = FCF in year 11 divided by (Discount Rate - Terminal Growth Rate) Cash Flows and Valuation Exhibit 1 Key Assumptions - - Years 6 - 10: Terminal Years (Year 11 on): Years 1 - 5: initial Sales Growth Rate EB1AT/S (FA+WC)/S 100.0 20.0% 10.0% 50.0% Growth Rate EBIAT/S (FA+WC)/S 15.0% 10.0% 50.0% Terminal Growth Rate Terminal EBIAT/S Terminal (FA+UC)/5 6.0% 10.0% 50.0% Growth R ate EBIAT/ Sales FA+WC/ Sales Change FA+UCFA+WC C - ative FCF FCF/ EBIAT Year Sales EBIAT FCF NA NA 0.0% 10.0% 2.0 NA 2.0 4.4 7.3 10.7 Sanel 0 1 2 3 4 5 6 7 8 9 10 11 100.0 120.0 144.0 172.8 207.4 248.8 286.2 329.1 378.4 435.2 500.5 530.5 NA 20.0% 20.0% 20.0% 20.0% 20.0% 15.0% 15.0% 15.0% 15.0% 15.0% 6.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% 0.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% 50.0% NA 12.0 16.4 17.3 20.7 24.9 28.6 32.9 37.8 43.5 50.0 53.1 50.0 60.0 72.0 86.4 103.7 124.4 143.1 164.5 189.2 217.6 250.2 265.3 NA 10.0 12.0 14.4 17.3 20.7 18.7 21.5 24.7 28.4 32.6 15.0 2.4 2.9 3.5 4.1 10.0 11.4 13.2 15.1 17.4 38.0 NA 16.77 16.15 16.7% 16.7% 16.7% 34.8% 34.82 34.8% 34.82 34.8% 71.7% 24.8 36.3 9.4 66.6 82.0 120.0 Total Present Value of Free Cash Flows Terminal Growth Rate Discount - Rate 0.0% 0.0% 2.0% 3.0% 6.0% 5.0% 6.0% 7.0% 8.0% 10.0% 12.0% 14.0% 16.0% 18.0% 20.0% 22.0% 24.0% 26.0% 28.0% 30.0% Panel 234.6 171.2 129.3 100.2 79.4 54.1 52.5 43.7 36.7 31.3 247.5 177.5 132.5 101.9 80.3 64.5 52.7 43.7 36.7 31.2 26.8 263.6 185.2 136.3 103.9 81.3 65.0 52. 9 43.8 36.7 31.1 26.7 284.2 194.5 140.8 106. 1 82.4 65.5 53.1 43.8 36.6 31.1 26.6 311.8 206.1 146.2 108.7 83.7 56.1 53.4 43.9 36.6 31.0 26.5 350.4 221.1 152.8 111.8 85. 1 56.8 53.6 408.3 241.0 161.1 115.5 86.9 67.5 53.9 44.0 36.5 30.8 26.3 504.8 268.9 171.7 120.1 88.9 68.4 54.3 44.1 36.5 30.7 26.2 697.7 310.8 185.8 125.7 91.3 69.5 56.7 44.2 36.4 30.5 26.0 36.6 30.9 26.4 EBIAT = Earnings Before Interest, But After Taxes FA - Net Fixed Assets WC = Operating Working Capital FCF = Total Free Cash Flow = EBIAT . (Change in FA and WC) Total Present Value = Present Value of FCF - Present Value of Terminal Value Terminal Value = FCF in year 11 divided by (Discount Rate - Terminal Growth Rate)