-
In each of five focus areas (liquidity, profitability, ), provide your view/thoughts/analysis of Midwest Shocks HISTORICAL performance.
-
Create a list with the numbers 1, 2, and 3. In order of priority (#1 being the highest), rank the three greatest concerns (not necessarily focus areas and not necessarily individual ratios) you have about the HISTORICAL performance. Your rankings are not necessarily entire focus areas (e.g., not all of the profitability, but maybe one aspect of it) and should not necessarily be a ratio either. Briefly explain your thoughts.
-
Do the same as what you did in #3 above, except do it now for the one-year forecast period.
-
Use your answer in #4 (forecast concerns) to create your own forecast. In Excel, create a new, one-year forecast (balance sheet, income statement, and statement of cash flows) that combines the companys forecast with your concerns in #4 to reflect your adjusted forecast. Use Excel so that this is a mathematical exercise where your income statement adds/subtracts correctly, your balance sheet balances, and the statement of cash flows sums to the actual change in cash on the balance sheet.
Midwest Shocks Consolidated Balance Sheet 4 Group Forecast 12/31/20 Group Assumpitions 6 7 Management Assumptions Plug 45.75 doh 144.54 doh 100% growth ($ in 000s) Assets Cash A/R Net Inventory Other Current Assets Total Current Assets Land and Improvements Buildings Equipment Accum. Depreciation PP&E - Net Intangibles Other LT Assets Total Non-Current Assets Total Assets Actual 12/31/18 38,349 75.654 174.066 14.828 302.897 20,067 43.842 40.644 7.360 97.193 0 6.527 103.720 406.617 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Actual Management Forecast 12/31/19 12/30/20 24,224 27,913 69.829 72.703 157.134 153.172 16.721 33.314 267.908 287.102 18,486 18,486 37.805 37.805 61.162 81.264 19.450 32.550 98.003 105.005 3.088 2.779 7.541 1.307 108.632 109.091 376.540 396.193 No additions No additions -33% growth Deprec. Schedule S/L Amort. Disposition 58.626 15.593 40.550 10.200 54.431 15.333 50.701 912 62.858 18.941 49.246 7.576 59.32 doh based on pay date no meaningful chg. From Tax Attorney Liabilities and Equity Accounts Payable Accrued Compensation Other Accrued Expenses Taxes Payable CMLTD TOTAL CURRENT ABL - Revolving Loan Senior Debt (foreign) Senior Debt (#1) Senior Debt (#2) Other Long-Term Liabilities Deferred Income Taxes 138.621 67,247 124.969 50,000 11.928 35.122 0 4.434 2,537 121.377 43,000 11.928 32.976 20.981 2.722 4,831 Plug No change No change No change No meaningful chg From Tax Attorney 11.928 32.976 20.981 1.820 3,589 32 33 34 104,021 116,438 138,541 35 TOTAL NON-CURRENT LLADIS TOTAL LIABILITIES 228.990 237.815 277.162 36 37 38 39 40 41 42 43 44 45 No change No change EQUITY Common Stock Additoninal Paid-In Capital Retained Earnings Accum. Other Comp. Total Shareholder's Equity TOTAL LIABILITIES AND Check 29 37.870 136.521 3.207 177.627 406.617 29 38.232 103.113 (2.649) 138.725 376.540 29 38.232 85.238 (4.468) 119.031 396.193 Derivative estimate 46 47 48 Midwest Shocks Consolidated Statement of Cash Flows 49 50 Management Assumptions Group Assumptions 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 (S in OOos) Operating Activities Net Income Depreciation Loss On Sale of PP&E Bargain Purchase Option Other Non-Cash Items Deferred Income Tax Misc. Accounts Receivable Inventories Prepaid Expenses and Other Accounts Payable and Income Taxes Payable Accrued Compensation Other Current Other Long-Term Assets/Liab CEFO Actual 2018 160.911 7.687 0 (147.220) 1.453 (901) 0 17.259 16.681 7.105 18.938 3.180 3.406 0 2.509 91.008 Actual Management Forecast Group Forecast 2019 12/30/20 12/31/20 11.592 22.125 12.654 13.100 143 0 0 0 692 0 2.294 (1.242) 0 0 5.825 (2.874) 16.932 3.962 (1.893) (16.593) 5.956 6.972 (9.288) 6.664 (260) 3.608 0 (2.726 5.641 41.921 41.363 0 68 69 Investing Activities Capital Expenditures Proceeds From Sales of PP&E Other CEFIA (11.451) 6.785 (37.602 (42.268) (16.157) 2.550 (4.102 (17.709 (20.102) 0 0 (20.102 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 Financing Activities ABL Revolver Senior Debt (foreign) Senior Debt #1 Senior Debt #2 Capital Contributions Dividends Repayment of Preferred Stock Other CEFFA Effect of Foreign Currency CHANGE IN CASH Check (31.000) 0 35.122 0 37.602 (34.400) (31.099) 1418) (24.193) (1.119 23.428 (7.000) 0 (2.146 20.981 0 (45.000 0 (3.433 (36.598) (1.739) (14.125 0 24.247 0 0 0 0 (40.000) 0 (1.819 (17.572 0 3.689 0 Financials and Forecast DuPont + Ready Midwest Shocks Consolidated Income Statement ($ in 000) Management Assumptions 6.7% Growth Group Assumpitions Actual 12/31/17 628.800 548.499 80.301 12.77% 50,100 13.600 14.400 Actual 12/31/18 474.831 356.435 118.396 24.93% 55.971 7.687 Significant improvement from Sales Force Growth Deprec. Schedule Misc. Nearly Complete Contractual Net Sales Cost of Sales Gross Profit Gross Profit Margin SG&A Expense Depreciation Expense Other expenses Restructuring Management fees Total Operating Expenses Earnings Before Int. & Tax Interest Expense Interest Income Dividend on Preferred Stock FX Exchange Earnings Before Tax Income Tax Expense Effective Tax Rate Earnings Before Exo Item Bargain Purchase Gain Net Earnings Dividends - Common EBITDA NOPAT 3.3% average rate 78.100 2.201 (2.100) 0 0 1.500 1.601 10.200 637.1% (8,599) Actual Tanagement Forecas Group Forecast 12/31/19 12/30/20 12/31/20 544.111 580.000 397.386 386.800 146.725 193.200 26.97% 33.31% 92.205 134.502 12.654 13.100 5.280 3.000 9.431 2.598 1.591 5.000 121.161 158.200 25.564 35.000 (4.620) (4.000) 857 0 0 0 (2.307) (1.500) 19.494 29.500 7.902 7.375 40.5% 25.0% 11,592 22,125 0 0 11.592 22.125 (45.000) (40.000) 38.218 48, 100 15,201 26,250 19.764 3.976 87.398 30.998 (2.588) 620 ( 1.099) (4.772) 23.159 9.468 40.9% 13,691 147.220 160.911 (34.400) 38.685 18,325 Cross Border Benefits one-time event (8.599) 15.801 (11,822) 12/31/17 12/31/18 Midwest Shocks Financial Ratios Mangament 12/3119 Forecast 12/30/20 26.97% 33.31% 4.70% 6.03% 2.13% 3.81% Group Forecast 12/31/20 Profitability Gross Profit Margin Operating Profit Margin Net Profit Margin 12.77% 0.35% 1.367684478 24.93% 6.53% 33.89% Efficiency A/R doh Inventory doh A/P doh Total Asset turnover Fixed Asset Turnover 1.17 1.45 1.46 1.29 1.71 2.33 Leverage Funded Debt/Equity LTD/Total Capital Total Debt/Total Capital Funded Debt/EBITDA Times Interest Earned FCCR-EBITDAR/Int. Leases CML TO 2.42 2.21 2.07 Liquidity Current Ratio Quick Ratio Cash Conversion Cycle Returns ROE ROA ROC A B D E F G H I J 1 2 3 Midwest Shocks 2020 "PLAN" DuPont Model 4 Profitability X Efficiency x Leverage Return 5 6 Income Revenue X Revenue Assets x Assets Equity Income Equity 7 8 Income Revenue X " Revenue Assets X Assets Equity Income Equity 9 10 11 12 0 0 X X 0 13 14 #DIV/0! X[#DIV/0! X[#DIV/0! = [ #DIV/0! 15 16 17 18 Midwest Shocks 2019 DuPont Model 19 20 21 Profitability X Efficiency X Leverage Return Income Revenue X Revenue Assets X Assets Equity Income Equity 22 23 Income Revenue X Revenue x Assets Assets Equity = Income Equity 24 25 26 27 X Q 0 X = 28 29 #DIV/0! x#DIV/0! X #DIV/0! = [ #DIV/0! 30 31 32 33 Midwest Shocks 2018 DuPont Model 34 35 Profitability X Efficiency X Leverage Retum 36 Income Revenue X Revenue Assets X Assets Equity = Income Equity 37 38 Income Revenue X Revenue Assets X Assets Equity Income Equity 39 40 41 42 0 0 X X = 0 43 44 #DIV/0! | X #DIV/0! x #DIV/0! = #DIV/0! 45 46 47 48 49 50 Financials and Forecast DuPont Ready Midwest Shocks Consolidated Balance Sheet 4 Group Forecast 12/31/20 Group Assumpitions 6 7 Management Assumptions Plug 45.75 doh 144.54 doh 100% growth ($ in 000s) Assets Cash A/R Net Inventory Other Current Assets Total Current Assets Land and Improvements Buildings Equipment Accum. Depreciation PP&E - Net Intangibles Other LT Assets Total Non-Current Assets Total Assets Actual 12/31/18 38,349 75.654 174.066 14.828 302.897 20,067 43.842 40.644 7.360 97.193 0 6.527 103.720 406.617 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Actual Management Forecast 12/31/19 12/30/20 24,224 27,913 69.829 72.703 157.134 153.172 16.721 33.314 267.908 287.102 18,486 18,486 37.805 37.805 61.162 81.264 19.450 32.550 98.003 105.005 3.088 2.779 7.541 1.307 108.632 109.091 376.540 396.193 No additions No additions -33% growth Deprec. Schedule S/L Amort. Disposition 58.626 15.593 40.550 10.200 54.431 15.333 50.701 912 62.858 18.941 49.246 7.576 59.32 doh based on pay date no meaningful chg. From Tax Attorney Liabilities and Equity Accounts Payable Accrued Compensation Other Accrued Expenses Taxes Payable CMLTD TOTAL CURRENT ABL - Revolving Loan Senior Debt (foreign) Senior Debt (#1) Senior Debt (#2) Other Long-Term Liabilities Deferred Income Taxes 138.621 67,247 124.969 50,000 11.928 35.122 0 4.434 2,537 121.377 43,000 11.928 32.976 20.981 2.722 4,831 Plug No change No change No change No meaningful chg From Tax Attorney 11.928 32.976 20.981 1.820 3,589 32 33 34 104,021 116,438 138,541 35 TOTAL NON-CURRENT LLADIS TOTAL LIABILITIES 228.990 237.815 277.162 36 37 38 39 40 41 42 43 44 45 No change No change EQUITY Common Stock Additoninal Paid-In Capital Retained Earnings Accum. Other Comp. Total Shareholder's Equity TOTAL LIABILITIES AND Check 29 37.870 136.521 3.207 177.627 406.617 29 38.232 103.113 (2.649) 138.725 376.540 29 38.232 85.238 (4.468) 119.031 396.193 Derivative estimate 46 47 48 Midwest Shocks Consolidated Statement of Cash Flows 49 50 Management Assumptions Group Assumptions 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 (S in OOos) Operating Activities Net Income Depreciation Loss On Sale of PP&E Bargain Purchase Option Other Non-Cash Items Deferred Income Tax Misc. Accounts Receivable Inventories Prepaid Expenses and Other Accounts Payable and Income Taxes Payable Accrued Compensation Other Current Other Long-Term Assets/Liab CEFO Actual 2018 160.911 7.687 0 (147.220) 1.453 (901) 0 17.259 16.681 7.105 18.938 3.180 3.406 0 2.509 91.008 Actual Management Forecast Group Forecast 2019 12/30/20 12/31/20 11.592 22.125 12.654 13.100 143 0 0 0 692 0 2.294 (1.242) 0 0 5.825 (2.874) 16.932 3.962 (1.893) (16.593) 5.956 6.972 (9.288) 6.664 (260) 3.608 0 (2.726 5.641 41.921 41.363 0 68 69 Investing Activities Capital Expenditures Proceeds From Sales of PP&E Other CEFIA (11.451) 6.785 (37.602 (42.268) (16.157) 2.550 (4.102 (17.709 (20.102) 0 0 (20.102 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 Financing Activities ABL Revolver Senior Debt (foreign) Senior Debt #1 Senior Debt #2 Capital Contributions Dividends Repayment of Preferred Stock Other CEFFA Effect of Foreign Currency CHANGE IN CASH Check (31.000) 0 35.122 0 37.602 (34.400) (31.099) 1418) (24.193) (1.119 23.428 (7.000) 0 (2.146 20.981 0 (45.000 0 (3.433 (36.598) (1.739) (14.125 0 24.247 0 0 0 0 (40.000) 0 (1.819 (17.572 0 3.689 0 Financials and Forecast DuPont + Ready Midwest Shocks Consolidated Income Statement ($ in 000) Management Assumptions 6.7% Growth Group Assumpitions Actual 12/31/17 628.800 548.499 80.301 12.77% 50,100 13.600 14.400 Actual 12/31/18 474.831 356.435 118.396 24.93% 55.971 7.687 Significant improvement from Sales Force Growth Deprec. Schedule Misc. Nearly Complete Contractual Net Sales Cost of Sales Gross Profit Gross Profit Margin SG&A Expense Depreciation Expense Other expenses Restructuring Management fees Total Operating Expenses Earnings Before Int. & Tax Interest Expense Interest Income Dividend on Preferred Stock FX Exchange Earnings Before Tax Income Tax Expense Effective Tax Rate Earnings Before Exo Item Bargain Purchase Gain Net Earnings Dividends - Common EBITDA NOPAT 3.3% average rate 78.100 2.201 (2.100) 0 0 1.500 1.601 10.200 637.1% (8,599) Actual Tanagement Forecas Group Forecast 12/31/19 12/30/20 12/31/20 544.111 580.000 397.386 386.800 146.725 193.200 26.97% 33.31% 92.205 134.502 12.654 13.100 5.280 3.000 9.431 2.598 1.591 5.000 121.161 158.200 25.564 35.000 (4.620) (4.000) 857 0 0 0 (2.307) (1.500) 19.494 29.500 7.902 7.375 40.5% 25.0% 11,592 22,125 0 0 11.592 22.125 (45.000) (40.000) 38.218 48, 100 15,201 26,250 19.764 3.976 87.398 30.998 (2.588) 620 ( 1.099) (4.772) 23.159 9.468 40.9% 13,691 147.220 160.911 (34.400) 38.685 18,325 Cross Border Benefits one-time event (8.599) 15.801 (11,822) 12/31/17 12/31/18 Midwest Shocks Financial Ratios Mangament 12/3119 Forecast 12/30/20 26.97% 33.31% 4.70% 6.03% 2.13% 3.81% Group Forecast 12/31/20 Profitability Gross Profit Margin Operating Profit Margin Net Profit Margin 12.77% 0.35% 1.367684478 24.93% 6.53% 33.89% Efficiency A/R doh Inventory doh A/P doh Total Asset turnover Fixed Asset Turnover 1.17 1.45 1.46 1.29 1.71 2.33 Leverage Funded Debt/Equity LTD/Total Capital Total Debt/Total Capital Funded Debt/EBITDA Times Interest Earned FCCR-EBITDAR/Int. Leases CML TO 2.42 2.21 2.07 Liquidity Current Ratio Quick Ratio Cash Conversion Cycle Returns ROE ROA ROC A B D E F G H I J 1 2 3 Midwest Shocks 2020 "PLAN" DuPont Model 4 Profitability X Efficiency x Leverage Return 5 6 Income Revenue X Revenue Assets x Assets Equity Income Equity 7 8 Income Revenue X " Revenue Assets X Assets Equity Income Equity 9 10 11 12 0 0 X X 0 13 14 #DIV/0! X[#DIV/0! X[#DIV/0! = [ #DIV/0! 15 16 17 18 Midwest Shocks 2019 DuPont Model 19 20 21 Profitability X Efficiency X Leverage Return Income Revenue X Revenue Assets X Assets Equity Income Equity 22 23 Income Revenue X Revenue x Assets Assets Equity = Income Equity 24 25 26 27 X Q 0 X = 28 29 #DIV/0! x#DIV/0! X #DIV/0! = [ #DIV/0! 30 31 32 33 Midwest Shocks 2018 DuPont Model 34 35 Profitability X Efficiency X Leverage Retum 36 Income Revenue X Revenue Assets X Assets Equity = Income Equity 37 38 Income Revenue X Revenue Assets X Assets Equity Income Equity 39 40 41 42 0 0 X X = 0 43 44 #DIV/0! | X #DIV/0! x #DIV/0! = #DIV/0! 45 46 47 48 49 50 Financials and Forecast DuPont Ready
