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 XXXXXXXXXX, 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 XXXXXXXXXX.
Based on CKZ’s 2020 Debt Note, the company long-term debt of $6.704 billion at end of 2020 ($598 million cu
ent portion; $6.106 billion non-cu
ent 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 co
esponding 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 (cu
ent and non-cu
ent 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 Name CKZ Corporation
Financial Format (0 = Thousands, 1= Millions) 1
Date of Last Year of Audited Financials 2020
Fiscal Year Ended 31-Dec
Income Statement Assumptions
2021 2022 2023 2024 2025
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 Expense See 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
Assets 2021 2022 2023 2024 2025
Cu
ent Assets
Cash As per Schedule. For minimum requirements, see below.
Receivables (Collection days) As per Working Capital Schedule
Inventories (days outstanding)
Other Cu
ent Assets
Non-Cu
ent Assets
Net PPE See 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
Cu
ent Liabilities
Revolving Line of Credit
Short Term Debt As per Debt & Shareholders' Schedule
Cu
ent Portion of Long-Term Debt
Accounts Payable (days outstanding) As per Working Capital Schedule
Other Payables
Accrued Expenses
Noncu
ent Employee Benefits (Growth %) -0.5% -0.5% -0.5% -0.5% -0.5%
Other Long Term Liabilities (M$) 1,561 1,561 1,561 1,561 1,561
Other Assumptions
2021 2022 2023 2024 2025
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)
2018 2019 2020 2021 2022 2023 2024 2025
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 Profit 6,609 6,683 6,624 5,856 5,856 6,288 6,653 7,039
Depreciation (863) (862) (746)
Selling General & Admin Expenses (2,843) (3,300) (4,265)
Operating Income (EBIT) 2,903 2,521 1,613
Interest Expense (282) (284) (295)
Interest Income 20 18 17
Income from Continuing Operations Before Taxes and Equity Interest 2,641 2,255 1,335
Provision for Income Taxes (828) (856) (418)
Income from Continuing Operations before Equity Interest 1,813 1,399 917
Share of Net Income of Equity Companies 205 146 149
Net Income from Continuing Operations 2,018 1,545 1,066
Net Income from Discontinued Operations 203 50 0
Net Income Including Income Attributable to Noncontrolling Interests 2,221 1,595 1,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)
Assets 2019 2020 2021 2022 2023 2024 2025
Cu
ent Assets
Cash $789 $619
Receivables 2,223 2,281
Inventories 1,892 1,909
Other Cu
ent Assets 655 617
Total Cu
ent Assets 5,559 5,426
Non-Cu
ent Assets
Net PPE 7,359 7,104
Investments in Equity Companies 257 247
Other Assets 2,351 2,065
Total Non-Cu
ent Assets 9,967 9,416
Total Assets 15,526 14,842
Liabilities & Stockholder's Equity
Cu
ent Liabilities
Short Term Debt - -
Cu
ent Portion of Long-Term Debt 549 598
Trade Accounts Payable 2,616 2,612
Other Payables 310 318
Accrued Expenses 1,974 1,750
Total Cu
ent Liabilities 5,449 5,278
Revolving Line of Credit 777 1,071
Long Term Debt, net of cu
ent portion 5,630 6,106
Noncu
ent Employee Benefits 1,693 1,137
Other Long Term Liabilities 906 1,146
Total Non-Cu
ent Liabilities 9,006 9,460
Total Liabilities 14,455 14,738
Shareholders Equity 1,071 104
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)
2019 2020 2021 2022 2023 2024 2025
INCOME STATEMENT ACCOUNTS
Sales
Cost of Sales
WORKING CAPITAL ACCOUNTS
Accounts Receivable
Inventories
Other Cu
ent Assets
Total non-cash cu
ent assets
Trade Accounts Payable
Other Payables
Accrued Expenses
Total non-cash cu
ent liabilities
RATIOS/ASSUMPTIONS
Number of days/year 365
A/R, net (collection days)
Inventory (inventory days)
Other Cu
ent 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) Life 2021 2022 2023 2024 2025
2021 Beginning PPE $7,104 10.0% 10
2021 CAPEX $2,450 10.0% 20
2022 CAPEX $1,200 10.0% 20
2023 CAPEX $1,450 10.0% 20
2024 CAPEX $1,236 10.0% 20
2025 CAPEX $900 10.0% 20
PP&E Schedule
2021 2022 2023 2024 2025
Debt & Shareholder Schedule
CKZ Corporation
Existing and Future Long Term Debt Long-term debt Issuances (Repayments)
(M$) 2021 2022 2023 2024 2025
Issue Date Principal Amount Rate Maturity
2010 598 5.76% 2021
2011 964 4.03% 2022
2012 933 5.80% 2023
2013 308 0.13% 2024
2014 755 8.32% 2025
2015 279 3.88% 2026
2016 300 2.40% 2027
2017 600 2.40% 2028
2018 1,267 5.30% 2029
2019 450 5.30% 2030
2020 250 3.70% 2031
2021 250 2.40% 2032
2022 700 3.88% 2033
Revolver and Other Short term Debt Limit Rate
Revolver $2,000 3.50%
Short Term Debt No Limit 6.50%
2021 2022 2023 2024 2025
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
Revolve
Beginning balance
Issuance (Repayment) on revolve
Ending balance
Short term Debt
Beginning balance
Issuance (Repayment)
Ending balance
Long-Term Debt Balances: 2021 2022 2023 2024 2025
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
Cu
ent Portion of Long Term Debt
Interest Expense Calculation 2021 2022 2023 2024 2025
5.76% Debt maturing 2021 5.76%
4.03% Debt maturing 2022 4.03%
5.80% Debt maturing 2023 5.80%
0.13% Debt maturing 2024 0.13%
8.32% Debt maturing 2025 8.32%
3.88% Debt maturing 2026 3.88%
2.40% Debt maturing 2027 2.40%
2.40% Debt maturing 2028 2.40%
5.30% Debt maturing 2029 5.30%
5.30% Debt maturing 2030 5.30%
3.70% Debt maturing 2031 3.70%
2.40% Debt maturing 2032 2.40%
3.88% Debt maturing 2033 3.88%
Total Long Term Debt interest expense
Revolver interest expense 3.50%
Other Short Term Debt interest expense 6.50%
Total Interest Expense
Total Interest Income 2.00%
Shareholder Equity Schedule 2021 2022 2023 2024 2025
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)
2021 2022 2023 2024 2025
Risk Tables
CKZ Corporation
Financial Risk and Operating Performance Assessment Criteria
Financial Risk Assessment
HIGH MEDIUM LOW Liquidity Cutoffs Risk Solvency Cutoffs Risk Payback Cutoffs Risk Coverage Cutoffs Risk
Liquidity Average of two ratios < 1.5 1.5 < = Average of two ratios < 2.5 Average of two ratios > = 2.5 0 HIGH 0 HIGH 0 HIGH 0 HIGH
Solvency Ratio < 10% 10% < = Ratio < 50% Ratio > = 50% 1.5 MEDIUM 0.1 MEDIUM 0.25 MEDIUM 3 MEDIUM
Payback Ratio < 25% 25% < = Ratio < 50% Ratio > = 50% 2.5 LOW 0.5 LOW 0.5 LOW 10 LOW
Coverage Ratio < 3 3 < = Ratio < 10 Ratio > = 10
Rating Calculation
CKZ Corporation
Financial Risk 2021 2022 2023 2024 2025
Liquidity
Cu
ent 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,