Use the file to answer the questions below

Show transcribed image text

B M 1 E Historical Results 2012 2013 OK 8 Corporate Finance inste. Al rights reserved FINANCIAL STATEMENTS Balance Sheet Check 2 2011 OK 2014 OK 2015 OK 2016 OK Forecast Period 2017 2018 OK OK 2019 OK 2020 OK 3 OK 4 Revenue Growth 0% 5 6 Assumptions 23 24 Income Statement 40 41 Balance Sheet 62 63 Cash Flow Statement 85 85 Supporting Schedules 10B 109 DCF Model 155 156 Sensitivity Analysis 157 158 159 Share Price Senstivity 160 161 162 10.0% 163 164 Discount Rate 165 165 167 16B 169 170 Revenue Growth +/-5% 171 Change $/Share 172 0.0% 173 174 175 176 Assumption 177 Revenue Growth +/-5% 178 COGS +/-5% 179 Discount Rate +/-5% 160 EV/EBITDA +/-5% 181 182 183 184 COGS +/-5% Change 0.0% $/Share Discount Rate +/-5% Change $/Share 0.0% EV/EBITDA +/-5% Change $/Share 0.0% -5% A +5% A Rank ABS Output Driver Pos Neg Impact on Share Price By Change in Assumption B 9 6 Assumptions 7 LIVE SCENARIO Revenue Growth (% Change) 10 Cost of Goods Sold 1% of Revenue) 11 Salaries and Benefits % of Revenue) 12 Rent and Overhead ($000's) 13 Depreciation & Amortization (% of PP8E) 14 Interest % of Debt) 15 Tax Rate(% of Earnings Before Tax) 15 Accounts Receivable (Days) 17 Inventory (Days) 18 Accounts Payable (Days) 19 Capital Expenditures ($000's) 20 Debt Issuance (Repayment) ($000's) 21 Equity Issued (Repaid) ($000's) 22 23 10.0% 42.0% 17.0% 15,000 35.0% 10.0% 25.0% 18 80 37 15,000 10.0% 42.0% 17.0% 15,000 35.0% 10.0% 25.0% 18 90 37 15,000 10.0% 42.0% 17.0% 15,000 35.0% 10.0% 25.0% 18 90 37 15,000 (20,000) 10.0% 42.0% 17.0% 15,000 35.0% 10.0% 25.0% 18 90 37 15,000 10.0% 42.0% 17.0% 15,000 35.0% 10.0% 25.0% 18 90 37 15,000 A B D G L M 1 Corporate Finance Institute. Al rights reserved. 2 FINANCIAL STATEMENTS 3 Balance Sheet Check E F Historical Results 2012 2013 OK OK j Forecast Period 2017 2018 OK OK 2011 OK 2014 OK 2015 OK 2016 OK 2019 OK 2020 OK 4. 102,007 39,023 62,984 118,086 48,004 70,082 131,345 49,123 82,222 142,341 52,654 89,687 150,772 56,710 94,062 165,849 69,657 96,193 182,434 76,622 105,812 200,678 84,285 116,393 220,745 92,713 128,032 242,820 101,984 140,835 26,427 10,963 19,500 2.500 59,390 3,594 22,658 10,125 18,150 2,500 53,433 16,649 23,872 10,087 17,205 1,500 52,664 29,558 23,002 11,020 16,544 1,500 52,066 37,622 25,245 11,412 16,080 1,500 54,237 39,825 28,194 15,000 13,132 3,000 59,327 36,866 31,014 15,000 13,786 3,000 62,800 43,012 34,115 15,000 14,211 1,000 64,326 52,067 37 527 15,000 14,487 1,000 68,014 60,018 41,279 15,000 14,667 1,000 71,946 68,890 1,120 2,474 4,858 11,791 8,483 21,075 10,908 26,713 11,598 28,227 9,216 27,649 10,753 32,259 13,017 39,050 15,005 45,014 17.222 51,667 23 24 Income Statement 25 26 Reveneue 27 Cost of Goods Sold (COGS) 28 Gross Profit 29 Expenses 30 Salaries and Benefts 31 Rent and Overhead 32 Depreciation & Amortization 33 Interest 34 Total Expenses 35 Earnings Before Tax 36 37 Taxes 38 Net Earnings 39 40 41 Balance Sheet 42 43 Assets 44 Cash 45 Accounts Receivable 46 Inventory 47 Property & Equipment 48 Total Assets 49 50 Liabilities 51 Accounts Payable 52 Debt 53 Total Liabilities 54 Shareholder's Equity 55 Equity Capital 56 Retained Earings 57 Shareholder's Equity 58 Total Liabilities & Shareholder's Equity 59 60 Check 61 62 67,971 5,100 7,805 45,500 126,376 81,210 5,904 9,601 42,350 139,065 83,715 6,567 9,825 40,145 140,252 111,069 7,117 10,531 38,602 167,319 139,550 7,539 11,342 37,521 195,951 162,156 8,179 15,267 39,389 224,991 189,463 8,997 18,893 40,603 257,956 205,712 9,896 20,782 41,392 277,783 247,999 10,886 22,861 41,905 323,651 296,898 11,975 25,147 42,238 376,258 3,902 50,000 53,902 4,800 50,000 54,800 4,912 30,000 34,912 5,265 30,000 35,265 5,671 30,000 35,671 7,061 30,000 37,061 7,767 30,000 37,767 8,544 10,000 18,544 9,398 10,000 19,398 10,338 10,000 20,338 70,000 70,000 2.474 72,474 126,376 70,000 14,265 84,265 139,065 70,000 35,340 105,340 140,252 70,000 62,053 132,053 167,319 70,000 90,280 160,280 195,951 70,000 117 930 187930 224,991 70,000 150,188 220.188 257,956 70,000 189,239 259,239 277,783 234,252 304,252 323,651 70,000 285,920 355,920 376,258 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 D E G L M N 1 F Historical Results 2012 OK OK j Forecast Period 2017 2018 OK OK 2013 2015 2020 2011 OK 2014 OK 2016 OK 2019 OK OK OK 2,474 19,500 9,003 12,971 11,791 18,150 1,702 28,239 21,075 17,205 775 37,505 26,713 16,544 903 42,354 28,227 16,080 827 43,480 27,649 13,132 3,175 37,606 32,259 13,786 3,738 42,307 39,050 14,211 2012 51,249 45,014 14,487 2,214 57,287 51,667 14,667 2,435 63,899 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 (20,000) (20,000) 170,000 170,000 (20,000) (20,000) 167,971 A B Corporate Finance Institute. Al rights reserved. 2 FINANCIAL STATEMENTS 3 Balance Sheet Check 4 63 Cash Flow Statement 64 65 Operating Cash Flow 66 Net Eamings 67 Plus: Depreciation & Amortization 68 Less: Changes in Working Capital 69 Cash from Operations 70 71 Investing Cash Flow 72 Investments in Property & Equipment 73 Cash from Investing 74 75 Financing Cash Flow 76 Issuance (repayment) of debt 77 Issuance (repayment) of equity 78 Cash from Financing 79 80 Net Increase (decrease) in Cash 81 Opening Cash Balance 82 Closing Cash Balance 83 84 85 86 Supporting Schedules 87 88 Working Capital Schedule 89 Accounts Receivable 90 Inventory 91 Accounts Payable 92 Net Working Capital (NWC) 93 Change in NWC 94 95 Depreciation Schedule 96 PPE Opening 97 Plus Capex 98 Less Depreciation 99 PPE Closing 100 101 Debt & Interest Schedule 102 Debt Opening 103 Issuance (repayment) 104 Debt Closing 105 Interest Expense 106 13,239 67,971 81.210 2,505 81,210 83,715 27,354 83,715 111,069 28,480 111,069 139,550 22,606 139,550 162,156 27,307 162.156 189,463 16,249 189,463 205,712 42,287 205,712 247,999 48,899 247 999 296,898 167,971 5,100 7,805 3,902 9,003 9,003 5,904 9,601 4.800 10,705 1,702 6,567 9,825 4,912 11,480 775 7,117 10,531 5.265 12,382 903 7,539 11,342 5,671 13,210 827 8,179 15,267 7,061 16,385 3,175 8,997 18,893 7,767 20,123 3,738 9,896 20,782 8,544 22,135 2,012 10,886 22,861 9,398 24,349 2,214 11,975 25,147 10,338 26,783 2,435 50,000 15,000 19,500 45,500 45,500 15,000 18,150 42,350 42,350 15,000 17,205 40,145 40,145 15,000 16,544 38,602 15,000 16,080 37,521 37,521 15,000 13,132 39,389 39,389 15,000 13,786.04 40,603 40,603 15,000 14,210.93 41,392 41,392 15,000 14,487.10 41,905 41,905 15,000 14.666.62 42,238 38,602 50,000 50,000 30,000 30,000 30,000 30,000 10,000 10,000 50,000 (20,000) 30,000 1,500 30,000 30,000 (20,000) 10,000 1,000 50,000 2,500 50,000 2,500 30,000 1,500 30,000 1,500 30,000 3,000 3,000 10,000 1,000 10,000 1,000 H M 2019 OK 2020 OK 2016 2018 2019 676,449 676,449 B c D E 1 8 Corporate Financeinture. Al rights reserved. Historical Results Forecast Period 2 FINANCIAL STATEMENTS 2011 2012 2013 2014 2015 2016 2017 2018 3 Balance Sheet Check OK OK OK OK OK OK OK OK 4 109 DCF Model 110 111 Assumptions 112 Tax Rate 25% 113 Discount Rate 12% 114 Perpetural Growth Rate 4% 115 EV/EBITDA Multiple 8.Ox 116 Transaction Date 31/12/2015 117 Current Price 16.00 118 Shares Outstanding 20,000 119 120 121 Discounted Cash Flow Entry 2017 2020 Exit Terminal Value 122 Date 31/12/2015 31/12/2016 31/12/2017 31/12/2018 31/12/2019 31/12/2020 31/12/2020 Perpetural Grow 123 EV/EBITDA 124 EBT 36,866 43,012 52,067 60,018 68,890 Average 125 Interest 3,000 3,000 1,000 1,000 1,000 126 EBIT 39,866 46,012 53,067 61,018 69,890 127 Less: Cash Taxes 9,966 11,503 13,267 15,255 17,472 128 Plus: D8A 13,132 13,786 14,211 14,487 14,667 129 EBITDA 52,998 59,798 67,278 75,506 84,556 130 Less: Capex 15,000 15,000 15,000 15,000 15,000 F 131 Less: Changes in NWC 3,175 3,738 2,012 2,214 2,435 132 Unlevered FCF 24,856 29,557 36,999 43,037 49,649 133 (Entry Exit 676,449 134 Net FCF 24,856 29,557 36,999 43,037 49,649 676,449 135 IRR (210,450) 24,856 29,557 36,999 43,037 49,649 676,449 136 137 Present Value 13B 22,186 4% 24,856 139 23,556 5% 29,557 140 26,327 5% 36,999 27,343 5% 43,037 142 28,155 6% 49,649 383,597 75% 676,449 144 511,163 100% 24,856 29,557 36,999 43,037 726,098 145 146 Intrinsic Value Market Value Rate of Return 147 Enterprise Value 511,163 Market Cap 320,000 Current Price 16.00 148 Plus: Cash 139,550 Plus: Debt 30,000 Target Price 31.04 149 Less: Debt 30,000 Less: Cash 139,550 Target Price Upside 94% 150 Equity Value 620,712 EV 210,450 IRR 37% 151 152 Equity Value/Share 31.04 Equity Value/Share 16.00 153 141 143 5 What is the share price when the discount rate is 13% and revenue growth is 1596? Review Later 35.47 36.83 36.70 39.70 6 What is the share price when COGS increases by 5%? Review Later 30.47 22.71 29.29 26.87 7 H Historical Results 2012 2013 OK OK 2014 OK 2015 OK 2016 OK Forecast Period 2017 2018 OK 2019 OK 2020 OK C D 1 Cara 2 FINANCIAL STATEMENTS 2011 3. Balance Sheet Check OK 4 176 Assunoon 177 Revenue Grow-45% 178 COGS 65 179 Discount Rale 5% 180 EV EBITDA +15% 781 Rank ABS Output Deliver Pos Nog The formula contained in the YELLOW cell (G179) is Hint: do not forget the $ when using absolute references. Use absolute reference so this formula can be copied over to other cells in this section without any manual modification. =SMALL(E177:E180,$F$179) =SMALL(F177:F180,$E$179) =SMALL($F$177:$F$180,E179) =SMALL($E$177:$E$180,F179) 00 D M Cura Fred FINANCIAL STATEMENTS 3 Balance Sheet Check E Historical Results 2012 2013 OK 2011 OK 2014 OK 2011 2015 OK 2016 OK Forecast Period 2018 OK OK 2019 OK 2020 OK +5% Rank ABS Output Driver Pos Nag 176 Assumption 177 Revenue Grow 5% 178 DOGS 55 179 Discount Rate 54 100 EVEBITDA +-5% 181 The formula contained in the ORANGE cell (H178) is = ($A$177:$G$180, $F$177:$F$180,0), MATCH($A$177,$A$177: $F$177,0)) Hint: do not forget the $ when using absolute references. 9 What type of chart did we use to build a tornado chart? Tornado chart Column chart Clustered bar chart Stacked bar chart 10 Rank the assumptions (drivers) according to sensitivity from lowest to highest. Revenue growth +/-5% EV/EBITDA Exit +/-596 COGS +/-5% Discount rate +/-5% B M 1 E Historical Results 2012 2013 OK 8 Corporate Finance inste. Al rights reserved FINANCIAL STATEMENTS Balance Sheet Check 2 2011 OK 2014 OK 2015 OK 2016 OK Forecast Period 2017 2018 OK OK 2019 OK 2020 OK 3 OK 4 Revenue Growth 0% 5 6 Assumptions 23 24 Income Statement 40 41 Balance Sheet 62 63 Cash Flow Statement 85 85 Supporting Schedules 10B 109 DCF Model 155 156 Sensitivity Analysis 157 158 159 Share Price Senstivity 160 161 162 10.0% 163 164 Discount Rate 165 165 167 16B 169 170 Revenue Growth +/-5% 171 Change $/Share 172 0.0% 173 174 175 176 Assumption 177 Revenue Growth +/-5% 178 COGS +/-5% 179 Discount Rate +/-5% 160 EV/EBITDA +/-5% 181 182 183 184 COGS +/-5% Change 0.0% $/Share Discount Rate +/-5% Change $/Share 0.0% EV/EBITDA +/-5% Change $/Share 0.0% -5% A +5% A Rank ABS Output Driver Pos Neg Impact on Share Price By Change in Assumption B 9 6 Assumptions 7 LIVE SCENARIO Revenue Growth (% Change) 10 Cost of Goods Sold 1% of Revenue) 11 Salaries and Benefits % of Revenue) 12 Rent and Overhead ($000's) 13 Depreciation & Amortization (% of PP8E) 14 Interest % of Debt) 15 Tax Rate(% of Earnings Before Tax) 15 Accounts Receivable (Days) 17 Inventory (Days) 18 Accounts Payable (Days) 19 Capital Expenditures ($000's) 20 Debt Issuance (Repayment) ($000's) 21 Equity Issued (Repaid) ($000's) 22 23 10.0% 42.0% 17.0% 15,000 35.0% 10.0% 25.0% 18 80 37 15,000 10.0% 42.0% 17.0% 15,000 35.0% 10.0% 25.0% 18 90 37 15,000 10.0% 42.0% 17.0% 15,000 35.0% 10.0% 25.0% 18 90 37 15,000 (20,000) 10.0% 42.0% 17.0% 15,000 35.0% 10.0% 25.0% 18 90 37 15,000 10.0% 42.0% 17.0% 15,000 35.0% 10.0% 25.0% 18 90 37 15,000 A B D G L M 1 Corporate Finance Institute. Al rights reserved. 2 FINANCIAL STATEMENTS 3 Balance Sheet Check E F Historical Results 2012 2013 OK OK j Forecast Period 2017 2018 OK OK 2011 OK 2014 OK 2015 OK 2016 OK 2019 OK 2020 OK 4. 102,007 39,023 62,984 118,086 48,004 70,082 131,345 49,123 82,222 142,341 52,654 89,687 150,772 56,710 94,062 165,849 69,657 96,193 182,434 76,622 105,812 200,678 84,285 116,393 220,745 92,713 128,032 242,820 101,984 140,835 26,427 10,963 19,500 2.500 59,390 3,594 22,658 10,125 18,150 2,500 53,433 16,649 23,872 10,087 17,205 1,500 52,664 29,558 23,002 11,020 16,544 1,500 52,066 37,622 25,245 11,412 16,080 1,500 54,237 39,825 28,194 15,000 13,132 3,000 59,327 36,866 31,014 15,000 13,786 3,000 62,800 43,012 34,115 15,000 14,211 1,000 64,326 52,067 37 527 15,000 14,487 1,000 68,014 60,018 41,279 15,000 14,667 1,000 71,946 68,890 1,120 2,474 4,858 11,791 8,483 21,075 10,908 26,713 11,598 28,227 9,216 27,649 10,753 32,259 13,017 39,050 15,005 45,014 17.222 51,667 23 24 Income Statement 25 26 Reveneue 27 Cost of Goods Sold (COGS) 28 Gross Profit 29 Expenses 30 Salaries and Benefts 31 Rent and Overhead 32 Depreciation & Amortization 33 Interest 34 Total Expenses 35 Earnings Before Tax 36 37 Taxes 38 Net Earnings 39 40 41 Balance Sheet 42 43 Assets 44 Cash 45 Accounts Receivable 46 Inventory 47 Property & Equipment 48 Total Assets 49 50 Liabilities 51 Accounts Payable 52 Debt 53 Total Liabilities 54 Shareholder's Equity 55 Equity Capital 56 Retained Earings 57 Shareholder's Equity 58 Total Liabilities & Shareholder's Equity 59 60 Check 61 62 67,971 5,100 7,805 45,500 126,376 81,210 5,904 9,601 42,350 139,065 83,715 6,567 9,825 40,145 140,252 111,069 7,117 10,531 38,602 167,319 139,550 7,539 11,342 37,521 195,951 162,156 8,179 15,267 39,389 224,991 189,463 8,997 18,893 40,603 257,956 205,712 9,896 20,782 41,392 277,783 247,999 10,886 22,861 41,905 323,651 296,898 11,975 25,147 42,238 376,258 3,902 50,000 53,902 4,800 50,000 54,800 4,912 30,000 34,912 5,265 30,000 35,265 5,671 30,000 35,671 7,061 30,000 37,061 7,767 30,000 37,767 8,544 10,000 18,544 9,398 10,000 19,398 10,338 10,000 20,338 70,000 70,000 2.474 72,474 126,376 70,000 14,265 84,265 139,065 70,000 35,340 105,340 140,252 70,000 62,053 132,053 167,319 70,000 90,280 160,280 195,951 70,000 117 930 187930 224,991 70,000 150,188 220.188 257,956 70,000 189,239 259,239 277,783 234,252 304,252 323,651 70,000 285,920 355,920 376,258 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 D E G L M N 1 F Historical Results 2012 OK OK j Forecast Period 2017 2018 OK OK 2013 2015 2020 2011 OK 2014 OK 2016 OK 2019 OK OK OK 2,474 19,500 9,003 12,971 11,791 18,150 1,702 28,239 21,075 17,205 775 37,505 26,713 16,544 903 42,354 28,227 16,080 827 43,480 27,649 13,132 3,175 37,606 32,259 13,786 3,738 42,307 39,050 14,211 2012 51,249 45,014 14,487 2,214 57,287 51,667 14,667 2,435 63,899 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 (20,000) (20,000) 170,000 170,000 (20,000) (20,000) 167,971 A B Corporate Finance Institute. Al rights reserved. 2 FINANCIAL STATEMENTS 3 Balance Sheet Check 4 63 Cash Flow Statement 64 65 Operating Cash Flow 66 Net Eamings 67 Plus: Depreciation & Amortization 68 Less: Changes in Working Capital 69 Cash from Operations 70 71 Investing Cash Flow 72 Investments in Property & Equipment 73 Cash from Investing 74 75 Financing Cash Flow 76 Issuance (repayment) of debt 77 Issuance (repayment) of equity 78 Cash from Financing 79 80 Net Increase (decrease) in Cash 81 Opening Cash Balance 82 Closing Cash Balance 83 84 85 86 Supporting Schedules 87 88 Working Capital Schedule 89 Accounts Receivable 90 Inventory 91 Accounts Payable 92 Net Working Capital (NWC) 93 Change in NWC 94 95 Depreciation Schedule 96 PPE Opening 97 Plus Capex 98 Less Depreciation 99 PPE Closing 100 101 Debt & Interest Schedule 102 Debt Opening 103 Issuance (repayment) 104 Debt Closing 105 Interest Expense 106 13,239 67,971 81.210 2,505 81,210 83,715 27,354 83,715 111,069 28,480 111,069 139,550 22,606 139,550 162,156 27,307 162.156 189,463 16,249 189,463 205,712 42,287 205,712 247,999 48,899 247 999 296,898 167,971 5,100 7,805 3,902 9,003 9,003 5,904 9,601 4.800 10,705 1,702 6,567 9,825 4,912 11,480 775 7,117 10,531 5.265 12,382 903 7,539 11,342 5,671 13,210 827 8,179 15,267 7,061 16,385 3,175 8,997 18,893 7,767 20,123 3,738 9,896 20,782 8,544 22,135 2,012 10,886 22,861 9,398 24,349 2,214 11,975 25,147 10,338 26,783 2,435 50,000 15,000 19,500 45,500 45,500 15,000 18,150 42,350 42,350 15,000 17,205 40,145 40,145 15,000 16,544 38,602 15,000 16,080 37,521 37,521 15,000 13,132 39,389 39,389 15,000 13,786.04 40,603 40,603 15,000 14,210.93 41,392 41,392 15,000 14,487.10 41,905 41,905 15,000 14.666.62 42,238 38,602 50,000 50,000 30,000 30,000 30,000 30,000 10,000 10,000 50,000 (20,000) 30,000 1,500 30,000 30,000 (20,000) 10,000 1,000 50,000 2,500 50,000 2,500 30,000 1,500 30,000 1,500 30,000 3,000 3,000 10,000 1,000 10,000 1,000 H M 2019 OK 2020 OK 2016 2018 2019 676,449 676,449 B c D E 1 8 Corporate Financeinture. Al rights reserved. Historical Results Forecast Period 2 FINANCIAL STATEMENTS 2011 2012 2013 2014 2015 2016 2017 2018 3 Balance Sheet Check OK OK OK OK OK OK OK OK 4 109 DCF Model 110 111 Assumptions 112 Tax Rate 25% 113 Discount Rate 12% 114 Perpetural Growth Rate 4% 115 EV/EBITDA Multiple 8.Ox 116 Transaction Date 31/12/2015 117 Current Price 16.00 118 Shares Outstanding 20,000 119 120 121 Discounted Cash Flow Entry 2017 2020 Exit Terminal Value 122 Date 31/12/2015 31/12/2016 31/12/2017 31/12/2018 31/12/2019 31/12/2020 31/12/2020 Perpetural Grow 123 EV/EBITDA 124 EBT 36,866 43,012 52,067 60,018 68,890 Average 125 Interest 3,000 3,000 1,000 1,000 1,000 126 EBIT 39,866 46,012 53,067 61,018 69,890 127 Less: Cash Taxes 9,966 11,503 13,267 15,255 17,472 128 Plus: D8A 13,132 13,786 14,211 14,487 14,667 129 EBITDA 52,998 59,798 67,278 75,506 84,556 130 Less: Capex 15,000 15,000 15,000 15,000 15,000 F 131 Less: Changes in NWC 3,175 3,738 2,012 2,214 2,435 132 Unlevered FCF 24,856 29,557 36,999 43,037 49,649 133 (Entry Exit 676,449 134 Net FCF 24,856 29,557 36,999 43,037 49,649 676,449 135 IRR (210,450) 24,856 29,557 36,999 43,037 49,649 676,449 136 137 Present Value 13B 22,186 4% 24,856 139 23,556 5% 29,557 140 26,327 5% 36,999 27,343 5% 43,037 142 28,155 6% 49,649 383,597 75% 676,449 144 511,163 100% 24,856 29,557 36,999 43,037 726,098 145 146 Intrinsic Value Market Value Rate of Return 147 Enterprise Value 511,163 Market Cap 320,000 Current Price 16.00 148 Plus: Cash 139,550 Plus: Debt 30,000 Target Price 31.04 149 Less: Debt 30,000 Less: Cash 139,550 Target Price Upside 94% 150 Equity Value 620,712 EV 210,450 IRR 37% 151 152 Equity Value/Share 31.04 Equity Value/Share 16.00 153 141 143 5 What is the share price when the discount rate is 13% and revenue growth is 1596? Review Later 35.47 36.83 36.70 39.70 6 What is the share price when COGS increases by 5%? Review Later 30.47 22.71 29.29 26.87 7 H Historical Results 2012 2013 OK OK 2014 OK 2015 OK 2016 OK Forecast Period 2017 2018 OK 2019 OK 2020 OK C D 1 Cara 2 FINANCIAL STATEMENTS 2011 3. Balance Sheet Check OK 4 176 Assunoon 177 Revenue Grow-45% 178 COGS 65 179 Discount Rale 5% 180 EV EBITDA +15% 781 Rank ABS Output Deliver Pos Nog The formula contained in the YELLOW cell (G179) is Hint: do not forget the $ when using absolute references. Use absolute reference so this formula can be copied over to other cells in this section without any manual modification. =SMALL(E177:E180,$F$179) =SMALL(F177:F180,$E$179) =SMALL($F$177:$F$180,E179) =SMALL($E$177:$E$180,F179) 00 D M Cura Fred FINANCIAL STATEMENTS 3 Balance Sheet Check E Historical Results 2012 2013 OK 2011 OK 2014 OK 2011 2015 OK 2016 OK Forecast Period 2018 OK OK 2019 OK 2020 OK +5% Rank ABS Output Driver Pos Nag 176 Assumption 177 Revenue Grow 5% 178 DOGS 55 179 Discount Rate 54 100 EVEBITDA +-5% 181 The formula contained in the ORANGE cell (H178) is = ($A$177:$G$180, $F$177:$F$180,0), MATCH($A$177,$A$177: $F$177,0)) Hint: do not forget the $ when using absolute references. 9 What type of chart did we use to build a tornado chart? Tornado chart Column chart Clustered bar chart Stacked bar chart 10 Rank the assumptions (drivers) according to sensitivity from lowest to highest. Revenue growth +/-5% EV/EBITDA Exit +/-596 COGS +/-5% Discount rate +/-5%