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 we went over in class and you see in the handout given to you in class. 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!

Exh ibit Cash Flows and Valuation Key Assumptions Terminal Years CYear 11 on) Years 1 5 nitial Sales Years 6 10: 100.0 15.0% Terminal Growth Rate 6.0% Grouth Rate 20.0% Grouth Rate 10.0% 10.0% 10.0% Terminal EBIAT/S ESIAT/S EBIAT/S CFA uC)/S Terminal (FA uC)/S (FA UC)/S 50.0% 50.0% 50.0% Cunul Grouth EBIAT/ Change FCF FA-WC/ ative FA C FCF FCF EBIAT Sales Rate Sales Sales EBIAT FA+UC Year 50.0 0 50.0% 100.0 NA NA NA NA NA NA NA 16.7 50.0% 12.0 60.0 10.0 2.0 2.0 120.0 20.0% 10.0% 1 16.7% 50.0% 14.4 72.0 12.0 2.4 2 144.0 20.0% 10.0% 4.4 50.0% 17.3 86.4 14.4 2.9 7.3 16.7% 3 172.8 20.0% 10.0% 20.7 17.3 3.5 10.7 16.7% 207.4 10.0% 50.0% 103.7 4 20.0% ane 16.7% 20.0% 10.0% 24.9 124.4 20.7 4.1 14.9 5 248.8 50.0% 28.6 10,0 34.8% 286.2 15.0% 10.0% 50.0% 143. 18.7 24.8 A 15.0% 10.0% 50.0% 32.9 164.5 21.5 11.4 36.3 34.82 7 329.1 10.0% 50.0% 37.8 24.7 8 378.4 15.0 189.2 13.2 9.4 34.8% 34.82 15.0% 10.0% 50.02 43.5 28.4 15.1 64.6 9 435.2 217.6 10.0% 50.0 82.0 10 500.5 15.0% 50.0% 250.2 32.6 17.4 34.8% 6.0% 10.0% 50.0% 53.1 15.0 38.0 120.0 71.T 11 530.5 265.3 Total Present Value of Free Cash Flows Terminal Growth Rate Discount Rate 1.0% 3.0% 5.0% 6.02 7.0% 8.0% 0.0% 2.0% 4.0% 10.0% 234.6 247.5 263.6 284.2 311.8 350.4 408.3 504.8 697.7 177.5 194.5 206.1 221.1 241.0 12.0% 171.2 185.2 268.9 310.8 14.0% 129.3 132.5 140.8 146.2 152.8 161.1 171.7 185.8 136.3 108.7 111.8 16.0% 100.2 101.9 103.9 106.1 115.5 120.1 125.7 18.02 79.4 80.3 81.3 82.4 83.7 85.1 anel 86.9 88.9 91.3 64.5 65.5 67.5 20.02 64.1 65.0 66.1 66.8 68.4 69.5 22.0% 52.5 52.7 52.9 53. 53.4 53.6 53.9 54.7 54.3 43.7 43.8 43.9 43.9 44.0 44.2 24.0% 43.7 43.8 44.1 26.0% 36.7 36.7 36.7 36.6 36.6 36.6 36.5 36.4 36.5 28.0% 31.2 31.1 31.1 31.0 30.9 30.8 30.5 31.3 30.7 30.0% 26.8 26.7 26.6 20.5 26.2 zo.9 26.4 26.3 26.0 EBIAT Earnings Before Interest, But After Taxes FA Net Fixed Assets c Operating uorking Capital FCF Total Free Cash Flow EBIAT CChange in FA and WC) Total Present Value Present Value of FCF Present Value of Termi nal Value Terminal Value FCF in year 11 divi ded by (Discount Rate Terminal Growth Rate) Exh ibit Cash Flows and Valuation Key Assumptions Terminal Years CYear 11 on) Years 1 5 nitial Sales Years 6 10: 100.0 15.0% Terminal Growth Rate 6.0% Grouth Rate 20.0% Grouth Rate 10.0% 10.0% 10.0% Terminal EBIAT/S ESIAT/S EBIAT/S CFA uC)/S Terminal (FA uC)/S (FA UC)/S 50.0% 50.0% 50.0% Cunul Grouth EBIAT/ Change FCF FA-WC/ ative FA C FCF FCF EBIAT Sales Rate Sales Sales EBIAT FA+UC Year 50.0 0 50.0% 100.0 NA NA NA NA NA NA NA 16.7 50.0% 12.0 60.0 10.0 2.0 2.0 120.0 20.0% 10.0% 1 16.7% 50.0% 14.4 72.0 12.0 2.4 2 144.0 20.0% 10.0% 4.4 50.0% 17.3 86.4 14.4 2.9 7.3 16.7% 3 172.8 20.0% 10.0% 20.7 17.3 3.5 10.7 16.7% 207.4 10.0% 50.0% 103.7 4 20.0% ane 16.7% 20.0% 10.0% 24.9 124.4 20.7 4.1 14.9 5 248.8 50.0% 28.6 10,0 34.8% 286.2 15.0% 10.0% 50.0% 143. 18.7 24.8 A 15.0% 10.0% 50.0% 32.9 164.5 21.5 11.4 36.3 34.82 7 329.1 10.0% 50.0% 37.8 24.7 8 378.4 15.0 189.2 13.2 9.4 34.8% 34.82 15.0% 10.0% 50.02 43.5 28.4 15.1 64.6 9 435.2 217.6 10.0% 50.0 82.0 10 500.5 15.0% 50.0% 250.2 32.6 17.4 34.8% 6.0% 10.0% 50.0% 53.1 15.0 38.0 120.0 71.T 11 530.5 265.3 Total Present Value of Free Cash Flows Terminal Growth Rate Discount Rate 1.0% 3.0% 5.0% 6.02 7.0% 8.0% 0.0% 2.0% 4.0% 10.0% 234.6 247.5 263.6 284.2 311.8 350.4 408.3 504.8 697.7 177.5 194.5 206.1 221.1 241.0 12.0% 171.2 185.2 268.9 310.8 14.0% 129.3 132.5 140.8 146.2 152.8 161.1 171.7 185.8 136.3 108.7 111.8 16.0% 100.2 101.9 103.9 106.1 115.5 120.1 125.7 18.02 79.4 80.3 81.3 82.4 83.7 85.1 anel 86.9 88.9 91.3 64.5 65.5 67.5 20.02 64.1 65.0 66.1 66.8 68.4 69.5 22.0% 52.5 52.7 52.9 53. 53.4 53.6 53.9 54.7 54.3 43.7 43.8 43.9 43.9 44.0 44.2 24.0% 43.7 43.8 44.1 26.0% 36.7 36.7 36.7 36.6 36.6 36.6 36.5 36.4 36.5 28.0% 31.2 31.1 31.1 31.0 30.9 30.8 30.5 31.3 30.7 30.0% 26.8 26.7 26.6 20.5 26.2 zo.9 26.4 26.3 26.0 EBIAT Earnings Before Interest, But After Taxes FA Net Fixed Assets c Operating uorking Capital FCF Total Free Cash Flow EBIAT CChange in FA and WC) Total Present Value Present Value of FCF Present Value of Termi nal Value Terminal Value FCF in year 11 divi ded by (Discount Rate Terminal Growth Rate)