The required analysis for this Case Assessment includes the preparation of ONE SET of annual financial projections over five years and an annual cash flow model. Review the options in Exhibit 3 of the case and change the yellow cells in the spreadsheet to your own assumption. Do not change any other cells. The projections should reflect a fully-financed Company that does not require additional Common Equity, and should be used to support your recommendation in the Case Assessment.
- What are your findings?
9-Feb-21 2:31 AM SAMPLE INPUTS Change these cells to your own assumptions! Do not change any other cells Do not change any other cells Actual FYE 12/31/2004 Actual FYE 12/31/2005 Actual FYE 12/31/2006 Projected FYE 12/31/2007 Projected FYE 12/31/2008 Projected FYE 12/31/2009 Projected FYE 12/31/2010 Projected FYE 12/31/2011 1,624.0 1,916.0 2,242.0 2,802.5 3,503.1 4,378.9 5,473.6 6,842.0 1,304.0 1,535.0 1,818.0 2,354.1 2,942.6 3,678.3 4,597.9 5,747.3 320.0 381.0 424.0 448.4 560.5 700.6 875.8 1,094.7 1 2 3 FE 455 - FINANCING NEW VENTURES 4 5 CASH FLOW ANALYSIS AS OF 12/31/2006 6 7 JONES ELECTRICAL DISTRIBUTION 8 9 WITH 2 PERCENT HISTORICAL DISCOUNT FOR NET 10 10 11 USD in Thousands 12 13 INCOME STATEMENT 14 15 16 17 Revenue 18 19 Cost of Sales 20 21 Gross Profit 22 23 Operating Expenses (Including Depreciation) 24 25 EBIT 26 27 Interest Expense 28 29 Pretax Income 30 31 Taxes 32 33 Net Income 34 35 36 INCOME STATEMENT ASSUMPTIONS 37 38 Revenue Growth 39 Gross Margin (Incl 2% Historical Discount for Net 10) 40 Operating Expenses / Revenue 41 Depreciation (See Delta in Accumulated Depr) 42 Depreciation / Revenue 43 Taxes / Pretax Income 272.0 307.0 347.0 434.4 543.0 678.7 848.4 1,060.5 48.0 74.0 77.0 14.0 17.5 21.9 27.4 34.2 27.0 30.0 31.0 33.4 26.3 16.9 18.5 20.6 21.0 44.0 46.0 (19.4) (8.8) 5.0 8.9 13.6 7.0 15.0 16.0 (6.8) (3.1) 1.7 3.1 4.7 14.0 29.0 30.0 (12.6) (5.7) 3.2 5.8 8.9 Remarks Implied Implied Implied Input Input Input Input Input 19.7% 16.7% 18.0% 19.9% 16.0% 25.0 1.3% 34.1% 17.0% 18.9% 15.5% 35.0 1.6% 34.8% 25.0% 16.0% 15.5% 35.3 1.5% 34.8% 25.0% 16.0% 15.5% 52.5 1.5% 34.8% 25.0% 16.0% 15.5% 65.7 1.5% 34.8% 25.0% 16.0% 15.5% 82.1 1.5% 34.8% 25.0% 16.0% 15.5% 102.6 1.5% 34.8% Depreciation Expense as % of Revenue is stable for industry Effective Tax Rate 33.3% 27.0 30.0 31.0 33.4 26.3 16.9 18.5 20.6 21.0 44.0 46.0 (19.4) (8.8) 5.0 8.9 13.6 7.0 15.0 16.0 (6.8) (3.1) 1.7 3.1 4.7 14.0 29.0 30.0 (12.6) (5.7) 3.2 5.8 8.9 Remarks Implied Implied Implied Input Input Input Input Input 19.7% 16.7% 18.0% 19.9% 16.0% 25.0 1.3% 34.1% 99.0 17.0% 18.9% 15.5% 35.0 1.6% 34.8% 112.0 25.0% 16.0% 15.5% 35.3 1.5% 34.8% 49.3 25.0% 16.0% 15.5% 52.5 1.5% 34.8% 70.1 25.0% 16.0% 15.5% 65.7 1.5% 34.8% 87.6 25.0% 16.0% 15.5% 82.1 1.5% 34.8% 109.5 25.0% 16.0% 15.5% 102.6 1.5% 34.8% 136.8 Depreciation Expense as % of Revenue is stable for industry Effective Tax Rate 33.3% 27 Interest Expense 28 29 Pretax Income 30 31 Taxes 32 33 Net Income 34 35 36 INCOME STATEMENT ASSUMPTIONS 37 38 Revenue Growth 39 Gross Margin (Incl 2% Historical Discount for Net 10) 40 Operating Expenses / Revenue 41 Depreciation (See Delta in Accumulated Depr) 42 Depreciation / Revenue 43 Taxes / Pretax Income 44 EBITDA 45 46 IMPLIED MARGINS 47 48 EBITDA / Revenue 49 EBIT/ Revenue 50 Net Income / Revenue 51 52 53 BALANCE SHEET 54 55 56 Assets 57 Cash & Equivalents 58 Accounts Receivable 59 Inventory 60 Subtotal - Current Assets 61 62 Gross Fixed Assets 63 Accumulated Depreciation 64 Net Fixed Assets 65 66 Total Assets 67 68 Liabilities & Equity 69 Accounts Payable 3.0% 0.9% 5.2% 3.9% 1.5% 5.0% 3.4% 1.3% 1.8% 0.5% -0.5% 2.0% 0.5% -0.2% 2.0% 0.5% 0.1% 2.0% 0.5% 0.1% 2.0% 0.5% 0.1% Actual FYE 12/31/2004 Actual FYE 12/31/2005 Actual FYE 12/31/2006 Projected FYE 12/31/2007 Projected FYE 12/31/2008 Projected FYE 12/31/2009 Projected FYE 12/31/2010 Projected FYE 12/31/2011 56.2 23.0 307.1 749.8 45.0 187.0 243.0 475.0 53.0 231.0 278.0 562.0 23.0 264.0 379.0 666.0 28.8 383.9 645.0 1,057.7 36.0 479.9 806.2 1.322.1 45.0 599.9 1,007.7 1,652.6 516.0 1,259.7 2,065.7 846.1 187.0 74.0 113.0 202.0 99.0 103.0 252.0 134.0 118.0 308.8 169.3 139.5 380.8 221.8 159.0 464.8 287.5 177.3 567.8 369.6 198.2 692.8 472.3 220.5 588.0 665.0 784.0 985.6 1,216.6 1,499.3 1,850.8 2,286.3 36.0 42.0 120.0 193.5 241.9 302.3 377.9 472.4 LOC Balance Input To Force Common Equity Inflow Need To Zero 36.0 149.0 13.0 24.0 222.0 42.0 214.0 14.0 24.0 294.0 120.0 249.0 14.0 24.0 407.0 193.5 350.0 13.7 24.0 581.2 241.9 107.1 17.1 24.0 390.1 302.3 147.9 21.4 24.0 495.6 377.9 196.5 26.7 24.0 625.1 472.4 253.1 33.4 24.0 782.9 182.0 158.0 134.0 110.0 86.0 62.0 38.0 14.0 B Liabilities & Equity Accounts Payable Line of Credit Payable (Drawdown Limit of $350K) Accrued Expenses 2 Long Term Debt - Current Portion 8 Subtotal - Current Liabilities 1 5 Long Term Debt 5 - Total Debt B Preferred Stock Series A (Input) Series B (Input) 2 Subtotal - Preferred Stock B Common Shareholders Equity (Net Worth) 5 Total Liabilities and Equity 355.0 396.0 407.0 484.0 217.1 233.9 258.5 291.1 300.0 300.0 184.0 213.0 243.0 294.5 740.5 941.7 1,187.6 1,189.4 588.0 665.0 784.0 985.6 1,216.6 1,499.3 1,850.8 2,286.3 7 2 BALANCE SHEET ASSUMPTIONS & CALCS Implied Implied Input Input Input Input Input 10.1 42.0 68.0 10.1 44.0 66.1 15.0 3.7 43.0 76.1 50.0 3.0 40.0 80.0 56.8 3.0 40.0 80.0 72.0 3.0 40.0 80.0 84.0 3.0 40.0 80.0 103.0 3.0 40.0 80.0 125.0 Cap Expenditure Input Set To Maintain Rev / Net Fixed Assets Ratio 10.1 17.4 10.0 16.6 24.1 14.7 30.0 11.5 30.0 11.5 30.0 11.5 30.0 11.5 30.0 11.5 Assume Employees Must Be Paid Twice per Month 7.6% Cash & Equivalents in Days of Revenue Accounts Receivable in Days of Revenue B Inventory in Days of COGS Capital Expenditures 5 Accounts Payable in Days of Cost of Sales Accrued Expenses in Days of Operating Expenses 3 Interest Expense / Total Debt Average Total Debt Balance Interest Expense Based on Average Total Debt 2 8 IMPLIED BALANCE SHEET RATIOS 1 5 Revenue / Inventory Revenue / Net Fixed Assets Revenue / Total Assets B 7.6% 375.5 7.6% 401.5 7.5% 445.5 33.4 7.5% 350.6 26.3 7.5% 225.5 16.9 7.5% 246.2 18.5 7.5% 274.8 20.6 6.7 14.4 2.8 6.9 18.6 2.9 5.9 19.0 2.9 5.4 20.1 2.8 5.4 22.0 2.9 5.4 24.7 2.9 5.4 27.6 3.0 5.4 31.0 3.0 Projected Actual FYE 12/312005 Actual FYE 12/312006 Projected FYE 12/312007 Projected FYE 12312008 Projected FYE 12/312009 Projected FYE 12/312010 FYE 12/312011 29.0 30.0 (12.6) (5.7) 3.2 5.8 8.9 25.0 35.0 35.3 52.5 65.7 82.1 102.6 54.0 65.0 22.7 46.8 68.9 87.9 111.5 (8.0) (44.0) (35.0) 6.0 1.0 30.0 (33.0) (101.0) 78.0 (0.0) (43.1) (137.0) 73.5 (0.3) (5.8) (76.8) (129.0) 48.4 3.4 (7.2) (96.0) (161.2) 60.5 4.3 (9.0) (120.0) (201.5) 75.6 5.3 (11.2) (150.0) (251.9) 94.5 6.7 (15.0) (50.0) (56.8) (72.0) (84.0) (103.0) (125.0) 41.0 11.0 77.0 (266.9) 16.8 24.6 32.6 0 CASH FLOW STATEMENT 11 12 13 14 15 Net Income (Loss) 16 17 Addbacks 18 8 Depreciation (See Delta in Accumulated Depr) 19 0 Operating Cash Flow 21 -2 Working Capital (Investments) 3 Cash & Equivalents =4 Accounts Receivable 5 Inventory 6 Accounts Payable -7 Accrued Expenses 8 9 Investing Activities 0 Capital Expenditures 31 2 Financing Activities 3 Total Net Debt Increase (Repayment) 4 Preferred Stock Issuance 5 Series A at 123V07 56 Series B =7 8 Net Cash Flow Available For (Required From) =9 Common Equity Holders -0 11 -2 RECONCILIATIONS 3 -4 -5 -6 7 Beginning Common Shareholders Equity -8 + Net Income (Loss) -9 - Net Cash Flow (Cash Payout to Common Holders 0 = Ending Common Equity 51 2 Ending Common Equity from Balance Sheet 3 -4 Difference 5 66 -7 Beginning Cash Balance 8 + Non Cash Sources of Cash 19 - Non Cash Uses of Cash (Including Cash Payou 0 = Ending Cash Balance 51 -2 Ending Cash from Balance Sheet -3 4 Difference 5 (64.1) (451.8) (197.9) (240.1) (292.9) Actual FYE 12/312005 Actual FYE 12/312006 Projected FYE 12/312007 Projected FYE 12/312008 Projected FYE 12/312009 Projected FYE 12/312010 Projected FYE 12/312011 184.0 29.0 213.0 30.0 243.0 (12.6) 64.1 294.5 294.5 (5.7) 451.8 740.5 740.5 3.2 197.9 941.7 941.7 5.8 240.1 1,187.6 1,187.6 8.9 292.9 1,489.4 213.0 243.0 213.0 243.0 294.5 740.5 941.7 1.187.6 1,189.4 (0.0) 0.0 0.0 (0.0) 300.0 45.0 102.0 194.0) 53.0 53.0 154.0 (184.0) 23.0 23.0 172.8 (172.8) 23.0 23.0 (168.3) 174.0 28.8 28.8 150.5 (143.3) 36.0 36.0 193.4 (184.4) 45.0 45.0 245.3 (234.0) 56.2 53.0 23.0 23.0 28.8 36.0 45.0 56.2 (0.0) (0.0) (0.0) 9-Feb-21 2:31 AM SAMPLE INPUTS Change these cells to your own assumptions! Do not change any other cells Do not change any other cells Actual FYE 12/31/2004 Actual FYE 12/31/2005 Actual FYE 12/31/2006 Projected FYE 12/31/2007 Projected FYE 12/31/2008 Projected FYE 12/31/2009 Projected FYE 12/31/2010 Projected FYE 12/31/2011 1,624.0 1,916.0 2,242.0 2,802.5 3,503.1 4,378.9 5,473.6 6,842.0 1,304.0 1,535.0 1,818.0 2,354.1 2,942.6 3,678.3 4,597.9 5,747.3 320.0 381.0 424.0 448.4 560.5 700.6 875.8 1,094.7 1 2 3 FE 455 - FINANCING NEW VENTURES 4 5 CASH FLOW ANALYSIS AS OF 12/31/2006 6 7 JONES ELECTRICAL DISTRIBUTION 8 9 WITH 2 PERCENT HISTORICAL DISCOUNT FOR NET 10 10 11 USD in Thousands 12 13 INCOME STATEMENT 14 15 16 17 Revenue 18 19 Cost of Sales 20 21 Gross Profit 22 23 Operating Expenses (Including Depreciation) 24 25 EBIT 26 27 Interest Expense 28 29 Pretax Income 30 31 Taxes 32 33 Net Income 34 35 36 INCOME STATEMENT ASSUMPTIONS 37 38 Revenue Growth 39 Gross Margin (Incl 2% Historical Discount for Net 10) 40 Operating Expenses / Revenue 41 Depreciation (See Delta in Accumulated Depr) 42 Depreciation / Revenue 43 Taxes / Pretax Income 272.0 307.0 347.0 434.4 543.0 678.7 848.4 1,060.5 48.0 74.0 77.0 14.0 17.5 21.9 27.4 34.2 27.0 30.0 31.0 33.4 26.3 16.9 18.5 20.6 21.0 44.0 46.0 (19.4) (8.8) 5.0 8.9 13.6 7.0 15.0 16.0 (6.8) (3.1) 1.7 3.1 4.7 14.0 29.0 30.0 (12.6) (5.7) 3.2 5.8 8.9 Remarks Implied Implied Implied Input Input Input Input Input 19.7% 16.7% 18.0% 19.9% 16.0% 25.0 1.3% 34.1% 17.0% 18.9% 15.5% 35.0 1.6% 34.8% 25.0% 16.0% 15.5% 35.3 1.5% 34.8% 25.0% 16.0% 15.5% 52.5 1.5% 34.8% 25.0% 16.0% 15.5% 65.7 1.5% 34.8% 25.0% 16.0% 15.5% 82.1 1.5% 34.8% 25.0% 16.0% 15.5% 102.6 1.5% 34.8% Depreciation Expense as % of Revenue is stable for industry Effective Tax Rate 33.3% 27.0 30.0 31.0 33.4 26.3 16.9 18.5 20.6 21.0 44.0 46.0 (19.4) (8.8) 5.0 8.9 13.6 7.0 15.0 16.0 (6.8) (3.1) 1.7 3.1 4.7 14.0 29.0 30.0 (12.6) (5.7) 3.2 5.8 8.9 Remarks Implied Implied Implied Input Input Input Input Input 19.7% 16.7% 18.0% 19.9% 16.0% 25.0 1.3% 34.1% 99.0 17.0% 18.9% 15.5% 35.0 1.6% 34.8% 112.0 25.0% 16.0% 15.5% 35.3 1.5% 34.8% 49.3 25.0% 16.0% 15.5% 52.5 1.5% 34.8% 70.1 25.0% 16.0% 15.5% 65.7 1.5% 34.8% 87.6 25.0% 16.0% 15.5% 82.1 1.5% 34.8% 109.5 25.0% 16.0% 15.5% 102.6 1.5% 34.8% 136.8 Depreciation Expense as % of Revenue is stable for industry Effective Tax Rate 33.3% 27 Interest Expense 28 29 Pretax Income 30 31 Taxes 32 33 Net Income 34 35 36 INCOME STATEMENT ASSUMPTIONS 37 38 Revenue Growth 39 Gross Margin (Incl 2% Historical Discount for Net 10) 40 Operating Expenses / Revenue 41 Depreciation (See Delta in Accumulated Depr) 42 Depreciation / Revenue 43 Taxes / Pretax Income 44 EBITDA 45 46 IMPLIED MARGINS 47 48 EBITDA / Revenue 49 EBIT/ Revenue 50 Net Income / Revenue 51 52 53 BALANCE SHEET 54 55 56 Assets 57 Cash & Equivalents 58 Accounts Receivable 59 Inventory 60 Subtotal - Current Assets 61 62 Gross Fixed Assets 63 Accumulated Depreciation 64 Net Fixed Assets 65 66 Total Assets 67 68 Liabilities & Equity 69 Accounts Payable 3.0% 0.9% 5.2% 3.9% 1.5% 5.0% 3.4% 1.3% 1.8% 0.5% -0.5% 2.0% 0.5% -0.2% 2.0% 0.5% 0.1% 2.0% 0.5% 0.1% 2.0% 0.5% 0.1% Actual FYE 12/31/2004 Actual FYE 12/31/2005 Actual FYE 12/31/2006 Projected FYE 12/31/2007 Projected FYE 12/31/2008 Projected FYE 12/31/2009 Projected FYE 12/31/2010 Projected FYE 12/31/2011 56.2 23.0 307.1 749.8 45.0 187.0 243.0 475.0 53.0 231.0 278.0 562.0 23.0 264.0 379.0 666.0 28.8 383.9 645.0 1,057.7 36.0 479.9 806.2 1.322.1 45.0 599.9 1,007.7 1,652.6 516.0 1,259.7 2,065.7 846.1 187.0 74.0 113.0 202.0 99.0 103.0 252.0 134.0 118.0 308.8 169.3 139.5 380.8 221.8 159.0 464.8 287.5 177.3 567.8 369.6 198.2 692.8 472.3 220.5 588.0 665.0 784.0 985.6 1,216.6 1,499.3 1,850.8 2,286.3 36.0 42.0 120.0 193.5 241.9 302.3 377.9 472.4 LOC Balance Input To Force Common Equity Inflow Need To Zero 36.0 149.0 13.0 24.0 222.0 42.0 214.0 14.0 24.0 294.0 120.0 249.0 14.0 24.0 407.0 193.5 350.0 13.7 24.0 581.2 241.9 107.1 17.1 24.0 390.1 302.3 147.9 21.4 24.0 495.6 377.9 196.5 26.7 24.0 625.1 472.4 253.1 33.4 24.0 782.9 182.0 158.0 134.0 110.0 86.0 62.0 38.0 14.0 B Liabilities & Equity Accounts Payable Line of Credit Payable (Drawdown Limit of $350K) Accrued Expenses 2 Long Term Debt - Current Portion 8 Subtotal - Current Liabilities 1 5 Long Term Debt 5 - Total Debt B Preferred Stock Series A (Input) Series B (Input) 2 Subtotal - Preferred Stock B Common Shareholders Equity (Net Worth) 5 Total Liabilities and Equity 355.0 396.0 407.0 484.0 217.1 233.9 258.5 291.1 300.0 300.0 184.0 213.0 243.0 294.5 740.5 941.7 1,187.6 1,189.4 588.0 665.0 784.0 985.6 1,216.6 1,499.3 1,850.8 2,286.3 7 2 BALANCE SHEET ASSUMPTIONS & CALCS Implied Implied Input Input Input Input Input 10.1 42.0 68.0 10.1 44.0 66.1 15.0 3.7 43.0 76.1 50.0 3.0 40.0 80.0 56.8 3.0 40.0 80.0 72.0 3.0 40.0 80.0 84.0 3.0 40.0 80.0 103.0 3.0 40.0 80.0 125.0 Cap Expenditure Input Set To Maintain Rev / Net Fixed Assets Ratio 10.1 17.4 10.0 16.6 24.1 14.7 30.0 11.5 30.0 11.5 30.0 11.5 30.0 11.5 30.0 11.5 Assume Employees Must Be Paid Twice per Month 7.6% Cash & Equivalents in Days of Revenue Accounts Receivable in Days of Revenue B Inventory in Days of COGS Capital Expenditures 5 Accounts Payable in Days of Cost of Sales Accrued Expenses in Days of Operating Expenses 3 Interest Expense / Total Debt Average Total Debt Balance Interest Expense Based on Average Total Debt 2 8 IMPLIED BALANCE SHEET RATIOS 1 5 Revenue / Inventory Revenue / Net Fixed Assets Revenue / Total Assets B 7.6% 375.5 7.6% 401.5 7.5% 445.5 33.4 7.5% 350.6 26.3 7.5% 225.5 16.9 7.5% 246.2 18.5 7.5% 274.8 20.6 6.7 14.4 2.8 6.9 18.6 2.9 5.9 19.0 2.9 5.4 20.1 2.8 5.4 22.0 2.9 5.4 24.7 2.9 5.4 27.6 3.0 5.4 31.0 3.0 Projected Actual FYE 12/312005 Actual FYE 12/312006 Projected FYE 12/312007 Projected FYE 12312008 Projected FYE 12/312009 Projected FYE 12/312010 FYE 12/312011 29.0 30.0 (12.6) (5.7) 3.2 5.8 8.9 25.0 35.0 35.3 52.5 65.7 82.1 102.6 54.0 65.0 22.7 46.8 68.9 87.9 111.5 (8.0) (44.0) (35.0) 6.0 1.0 30.0 (33.0) (101.0) 78.0 (0.0) (43.1) (137.0) 73.5 (0.3) (5.8) (76.8) (129.0) 48.4 3.4 (7.2) (96.0) (161.2) 60.5 4.3 (9.0) (120.0) (201.5) 75.6 5.3 (11.2) (150.0) (251.9) 94.5 6.7 (15.0) (50.0) (56.8) (72.0) (84.0) (103.0) (125.0) 41.0 11.0 77.0 (266.9) 16.8 24.6 32.6 0 CASH FLOW STATEMENT 11 12 13 14 15 Net Income (Loss) 16 17 Addbacks 18 8 Depreciation (See Delta in Accumulated Depr) 19 0 Operating Cash Flow 21 -2 Working Capital (Investments) 3 Cash & Equivalents =4 Accounts Receivable 5 Inventory 6 Accounts Payable -7 Accrued Expenses 8 9 Investing Activities 0 Capital Expenditures 31 2 Financing Activities 3 Total Net Debt Increase (Repayment) 4 Preferred Stock Issuance 5 Series A at 123V07 56 Series B =7 8 Net Cash Flow Available For (Required From) =9 Common Equity Holders -0 11 -2 RECONCILIATIONS 3 -4 -5 -6 7 Beginning Common Shareholders Equity -8 + Net Income (Loss) -9 - Net Cash Flow (Cash Payout to Common Holders 0 = Ending Common Equity 51 2 Ending Common Equity from Balance Sheet 3 -4 Difference 5 66 -7 Beginning Cash Balance 8 + Non Cash Sources of Cash 19 - Non Cash Uses of Cash (Including Cash Payou 0 = Ending Cash Balance 51 -2 Ending Cash from Balance Sheet -3 4 Difference 5 (64.1) (451.8) (197.9) (240.1) (292.9) Actual FYE 12/312005 Actual FYE 12/312006 Projected FYE 12/312007 Projected FYE 12/312008 Projected FYE 12/312009 Projected FYE 12/312010 Projected FYE 12/312011 184.0 29.0 213.0 30.0 243.0 (12.6) 64.1 294.5 294.5 (5.7) 451.8 740.5 740.5 3.2 197.9 941.7 941.7 5.8 240.1 1,187.6 1,187.6 8.9 292.9 1,489.4 213.0 243.0 213.0 243.0 294.5 740.5 941.7 1.187.6 1,189.4 (0.0) 0.0 0.0 (0.0) 300.0 45.0 102.0 194.0) 53.0 53.0 154.0 (184.0) 23.0 23.0 172.8 (172.8) 23.0 23.0 (168.3) 174.0 28.8 28.8 150.5 (143.3) 36.0 36.0 193.4 (184.4) 45.0 45.0 245.3 (234.0) 56.2 53.0 23.0 23.0 28.8 36.0 45.0 56.2 (0.0) (0.0) (0.0)
