Please follow the directions in the word document and only use the template (excel). No hard coded numbers unless necessary. P
Major Task: Your major task in this assignment is to construct projected financial statements (Income Statement, Balance Sheet and Statement of Cash Flows) for CKZ Corporation over the period 2021-2025, after building Working Capital, Fixed Asset, and Debt & Shareholder Equity Schedules to provide the appropriate inputs to the Income Statement and the Balance Sheet for each projected period. After you complete the three-statement model, you will be conducting a short risk rating analysis. Given: Your starting point is the CKZ Start File.xlsx. This file provides historical financial statement data through the end of 2020 as well as assumptions to help you make projections from 2021-2025. Based on CKZ’s 2020 Debt Note, the company long-term debt of $6.704 billion at end of 2020 ($598 million current portion; $6.106 billion non-current portion). The total debt consists of a variety of issuances of Notes and Debentures, Industrial Development Revenue bonds, and Bank loans with varying maturities and interest rates. Using this aggregate information, I list a number of hypothetical existing and future issuances in the Debt Schedule sheet (row 6 – 18), with principal amounts, interest rates, and maturity dates. In its 2020 Debt Note, CKZ also discloses information on its revolving credit facilities. In this project, you will assume that CKZ uses this revolving line of credit if it requires cash beyond that provided by its operating and other activities, and that the credit line limit is $2.0 billion for your entire projected period (cell E22 in debt schedule). Further, if the required cash exceeds the $2.0 billion credit line, you will assume CKZ will take on short-term debt that it can refinance on the same terms year after year. Interest rate assumptions relating to both the revolving line of credit and any required short-term debt are in the Debt Schedule (row 22 – 23). Assume that if CKZ takes on Short Term Debt at any point during the projected period, any subsequent available cash will pay down this Short Term Debt before paying down the revolving line of credit. Three-Statement Instructions: · When completing the Fixed Asset Schedule, please use straight-line depreciation for all asset layers. · Use the information provided in the Debt & Shareholder Schedule to calculate long-term debt repayment (issuance) and balance each year from 2021 to 2025. Then follow the /steps used in class to calculate cash available for the Revolver and Short Term Debt, and the ending balance in each of these accounts for each year. Make the appropriate links to the Balance Sheet. · Calculate interest expense generated by long-term debt, revolver, and short-term debt from 2021 to 2025. Do not use a weighted average interest rate to calculate interest expense on aggregate debt. For interest expense and interest income computations, use the average of the beginning and ending balances of the corresponding account for your calculations. Link the interest expense and interest income to the Income Statement. · Complete the Shareholder Equity Schedule, and link the ending balance to the Balance Sheet. Risk Rating Instructions: 1. Compute financial risk ratios. Compute the ratios listed on the Rating Calculation worksheet for all years. For ratios that rely on balance sheet numbers, use ending balances. For the Quick Ratio, include only Cash and Cash Equivalents and Receivables in the numerator. For all ratios involving Debt, define Debt as the sum of Short-term debt, the Revolver and Long Term Debt (current and non-current portions). Define EBITDA as operating income before depreciation and amortization. 2. Evaluate the ratios computed on the Rating Calculation sheet. · Using the VLOOKUP function, summarize each financial risk group (e.g., Liquidity, Solvency, etc.) as LOW, MEDIUM, or HIGH risk based on the ranges for the ratios given in the Financial Risk Assessment Table on the Risk Tables sheet. 3. Use Conditional Formatting such that cells reporting LOW risk have a green fill with white letters, cells reporting MEDIUM risk have a yellow fill with black letters, and cells reporting HIGH risk have a red fill with white letters. 4. Using the COUNTIF function, count the number of factors scored as LOW, MEDIUM, and HIGH risk. - 2 - Assumptions CKZ Corporation Model Settings Company NameCKZ Corporation Financial Format (0 = Thousands, 1= Millions)1 Date of Last Year of Audited Financials2020 Fiscal Year Ended 31-Dec Income Statement Assumptions 20212022202320242025 Sales (Growth %)0.0%0.0%5.7%5.8%5.8% Cost of Goods Sold (% of Net Sales)68.5%68.5%68.0%68.0%68.0% Depreciation See Fixed Asset Schedule Selling General & Admin Expenses (% of Net Sales)14.0%12.0%12.0%12.0%12.0% Interest ExpenseSee Debt & Shareholder Schedule Interest Income (% of average beginning and end total Cash balance)2.0%2.0%2.0%2.0%2.0% Effective Tax Rate (% of Inc. from Continuing Operations before Taxes and Equity Interest) 21.5%21.5%21.5%21.5%21.5% Share of Net Income of Equity Companies (as % of prior period's Investments in Equity Companies)46.0%40.0%43.0%43.0%43.0% Net Income from Discontinued Operations$0$0$0$0$0 Net Income Attributable to Noncontrolling Interests (as a % of Net Income Including Net Income Attributable to Noncontrolling Interests)4.0%4.0%4.0%4.0%4.0% Balance Sheet Assumptions Assets20212022202320242025 Current Assets CashAs per Schedule. For minimum requirements, see below. Receivables (Collection days)As per Working Capital Schedule Inventories (days outstanding) Other Current Assets Non-Current Assets Net PPESee Fixed Asset schedule Investments in Equity Companies (M$)$469$601$782$1,017$1,323 Other Assets (M$)$4,703$4,703$4,703$4,703$4,703 Liabilities & Stockholder's Equity Current Liabilities Revolving Line of Credit Short Term Debt As per Debt & Shareholders' Schedule Current Portion of Long-Term Debt Accounts Payable (days outstanding)As per Working Capital Schedule Other Payables Accrued Expenses Noncurrent Employee Benefits (Growth %)-0.5%-0.5%-0.5%-0.5%-0.5% Other Long Term Liabilities (M$)1,5611,5611,5611,5611,561 Other Assumptions 20212022202320242025 Net Equity Issuances/Repurchases (M$)$0$0$0$0$0 Dividend Payout (as % of NI Including Income Attributable to Noncontrolling Interests if > 0, 0% otherwise)52.0%53.0%53.0%54.0%54.0% Dividends Received from Equity Companies (% of Share of Net income of Equity Companies if >0, 0% otherwise)30.0%30.0%30.0%30.0%30.0% Minimum Cash Balance Required (% of Net Sales)4%4%4%4%4% IS CKZ Corporation Consolidated Statements of Income (Dollars in Millions Except Per Share Data) 20182019202020212022202320242025 Sales$19,561$19,724$18,591$18,591$18,591$19,651$20,790$21,996 Cost of Goods Sold(12,952)(13,041)(11,967)(12,735)(12,735)(13,362)(14,137)(14,957) Gross Profit6,6096,6836,6245,8565,8566,2886,6537,039 Depreciation (863)(862)(746) Selling General & Admin Expenses(2,843)(3,300)(4,265) Operating Income (EBIT)2,9032,5211,613 Interest Expense(282)(284)(295) Interest Income201817 Income from Continuing Operations Before Taxes and Equity Interest 2,6412,2551,335 Provision for Income Taxes(828)(856)(418) Income from Continuing Operations before Equity Interest 1,8131,399917 Share of Net Income of Equity Companies205146149 Net Income from Continuing Operations2,0181,5451,066 Net Income from Discontinued Operations203500 Net Income Including Income Attributable to Noncontrolling Interests2,2211,5951,066 Net Income Attributable to Noncontrolling Interests(79)(69)(53) Net Income Attributable to Kimberly-Clark Corporation$2,142$1,526$1,013 BS CKZ Corporation Balance Sheet (Dollars in Millions Except Per Share Data) Assets2019202020212022202320242025 Current Assets Cash$789$619 Receivables2,2232,281 Inventories1,8921,909 Other Current Assets655617 Total Current Assets5,5595,426 Non-Current Assets Net PPE7,3597,104 Investments in Equity Companies257247 Other Assets2,3512,065 Total Non-Current Assets9,9679,416 Total Assets15,52614,842 Liabilities & Stockholder's Equity Current Liabilities Short Term Debt -- Current Portion of Long-Term Debt549598 Trade Accounts Payable2,6162,612 Other Payables310318 Accrued Expenses1,9741,750 Total Current Liabilities5,4495,278 Revolving Line of Credit7771,071 Long Term Debt, net of current portion5,6306,106 Noncurrent Employee Benefits1,6931,137 Other Long Term Liabilities9061,146 Total Non-Current Liabilities9,0069,460 Total Liabilities14,45514,738 Shareholders Equity1,071104 Total Liabilities and Stockholders' Equity$15,526$14,842 Unreconciled Difference WC Schedule CKZ Corporation Working Capital Schedule (Dollars in Millions Except Per Share Data) 2019202020212022202320242025 INCOME STATEMENT ACCOUNTS Sales Cost of Sales WORKING CAPITAL ACCOUNTS Accounts Receivable Inventories Other Current Assets Total non-cash current assets Trade Accounts Payable Other Payables Accrued Expenses Total non-cash current liabilities RATIOS/ASSUMPTIONS Number of days/year365 A/R, net (collection days) Inventory (inventory days) Other Current Assets (% of sales) Trade A/P (days outstanding) Other Payables (% of Sales) Accrued expenses (% of sales) Fixed Asset Schedule CKZ Corporation SLN Depreciation Cost (M$)Salvage (% Cost)Life20212022202320242025 2021 Beginning PPE$7,10410.0%10 2021 CAPEX$2,45010.0%20 2022 CAPEX$1,20010.0%20 2023 CAPEX$1,45010.0%20 2024 CAPEX$1,23610.0%20 2025 CAPEX$90010.0%20 PP&E Schedule 20212022202320242025 Debt & Shareholder Schedule CKZ Corporation Existing and Future Long Term DebtLong-term debt Issuances (Repayments) (M$)20212022202320242025 Issue DatePrincipal AmountRateMaturity 20105985.76%2021 20119644.03%2022 20129335.80%2023 20133080.13%2024 20147558.32%2025 20152793.88%2026 20163002.40%2027 20176002.40%2028 20181,2675.30%2029 20194505.30%2030 20202503.70%2031 20212502.40%2032 20227003.88%2033 Revolver and Other Short term Debt LimitRate Revolver $2,0003.50% Short Term DebtNo Limit6.50% 20212022202320242025 Cash flow available for financing activities Dividend payout Equity Issuances (Repurchases) Increases (Decreases) in Other Long Term Liabilities Plus: Beginning cash balance Less: Minimum cash balance required Cash available for debt Long-Term Debt Issuances (Repayments) Cash available for Revolver and Other Short Term Debt Revolver Beginning balance Issuance (Repayment) on revolver Ending balance Short term Debt Beginning balance Issuance (Repayment) Ending balance Long-Term Debt Balances:20212022202320242025 5.76% Debt maturing 2021 4.03% Debt maturing 2022 5.80% Debt maturing 2023 0.13% Debt maturing 2024 8.32% Debt maturing 2025 3.88% Debt maturing 2026 2.40% Debt maturing 2027 2.40% Debt maturing 2028 5.30% Debt maturing 2029 5.30% Debt maturing 2030 3.70% Debt maturing 2031 2.40% Debt maturing 2032 3.88% Debt maturing 2033 Total Long Term Debt Current Portion of Long Term Debt Interest Expense Calculation20212022202320242025 5.76% Debt maturing 20215.76% 4.03% Debt maturing 20224.03% 5.80% Debt maturing 20235.80% 0.13% Debt maturing 20240.13% 8.32% Debt maturing 20258.32% 3.88% Debt maturing 20263.88% 2.40% Debt maturing 20272.40% 2.40% Debt maturing 20282.40% 5.30% Debt maturing 20295.30% 5.30% Debt maturing 20305.30% 3.70% Debt maturing 20313.70% 2.40% Debt maturing 20322.40% 3.88% Debt maturing 20333.88% Total Long Term Debt interest expense Revolver interest expense3.50% Other Short Term Debt interest expense6.50% Total Interest Expense Total Interest Income2.00% Shareholder Equity Schedule20212022202320242025 Beginning Balance NI Including Income Attributable to Noncontrolling Interests (Dividends) Issuances (Repurchases) of Equity Ending Balance SCF CKZ Corporation Consolidated Statement of Cash Flows (Dollars in Millions Except Per Share Data) 20212022202320242025 Risk Tables CKZ Corporation Financial Risk and Operating Performance Assessment Criteria Financial Risk Assessment HIGHMEDIUMLOWLiquidity CutoffsRiskSolvency CutoffsRiskPayback CutoffsRiskCoverage CutoffsRisk LiquidityAverage of two ratios < 1.5="" 1.5="">< =="" average="" of="" two="" ratios="">< 2.5="" average="" of="" two="" ratios=""> = 2.50HIGH0HIGH0HIGH0HIGH SolvencyRatio < 10%="" 10%="">< =="" ratio="">< 50%="" ratio=""> = 50%1.5MEDIUM0.1MEDIUM0.25MEDIUM3MEDIUM PaybackRatio < 25%="" 25%="">< =="" ratio="">< 50%="" ratio=""> = 50%2.5LOW0.5LOW0.5LOW10LOW CoverageRatio < 3="" 3="">< =="" ratio="">< 10="" ratio=""> = 10 Rating Calculation CKZ Corporation Financial Risk20212022202320242025 Liquidity Current Ratio Quick Ratio Liquidity Risk (LOW, MEDIUM, or HIGH) Solvency Equity/Total Capital (Debt+Equity) Solvency Risk (LOW, MEDIUM, or HIGH) Debt Payback EBITDA/Debt Payback Risk (LOW, MEDIUM, or HIGH) Interest Coverage EBITDA/Interest Expense Coverage Risk (LOW, MEDIUM,