NBA 5110 Spring 2024
FINAL ASSIGNMENT – INDIVIDUAL
Due: 5pm on March. 17, 2024 via Canvas
Your 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 2024-2028, 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 2023 as well as assumptions to help you make projections from 2024-2028.
Three-Statement Instructions:
The three-statement is very similar to our class model. Below I highlight two main differences that you should pay attention in the model:
1. Debt Schedule: CKZ has a long-term debt of $6.704 billion at end of 2023 ($598 million current portion; $6.106 billion non-current portion). The total debt consists of a variety of issuances of bonds with varying maturities and interest rates. I list the existing and future issuances in the Debt Schedule sheet, with their respective principal amounts, interest rates, and maturity year. Each of these bonds generates interest expenses every year and will be paid off in its full principal amount in its maturity year. To avoid confusion, I’ve filled out the debt issuance/repayment table for you.
CKZ also has a revolving line of credit with a credit limit of $2.0 billion. If the cash deficiency exceeds the $2.0 billion credit line, CKZ will take on short-term debt that it can refinance on the same terms year after year. Assume that if CKZ takes on short-term debt, any subsequent available cash will pay down this short-term debt before paying down the revolving line of credit.
2. CKZ has investment in equity companies. The related accounts include Share of Net Income of Equity Companies on I/S, Investment in Equity Companies in B/S and SCF, and Equity Income not received as Dividends and Investments on SCF. You can find their related assumptions on the Assumption sheet. I also provide a supplemental document on Canvas to help you with these accounts.
Risk Rating Instructions:
1. Compute financial risk ratios operating 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, revolver, and long-term debt (current and non-current portions). Define EBITDA as operating income before depreciation and amortization. Lastly, define Net Income as the Net Income of CKZ (not including NCI).
2. Evaluate the ratios computed on the Rating Calculation sheet.
· Input Cutoff values in the Risk Tables
· 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.
· Using the VLOOKUP function, summarize each operating risk group (e.g., Return on Equity, Revenue Growth, etc.) as STRONG, AVERAGE or WEAK based on the ranges for the ratios given in the Operating Performance 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. Summarize the financial risk. Using the COUNTIF function, count the number of factors scored as LOW, MEDIUM, and HIGH risk. For the Financial Risk Summary measure (B36:F36), give one point for each LOW-Risk category, two points for each MEDIUM risk category, and five points for each HIGH-Risk category. Use the SUMPRODUCT function to compute the Financial Risk Summary measure. Higher values of Financial Risk Summary indicate higher risk.
5. Summarize the operating risk. Using the COUNTIF function, count the number of operating performance factors scored as STRONG, AVERAGE, and WEAK. For the Operating Risk Summary measure (I36:M36), give one point for each STRONG category, two points for each AVERAGE category, and five points for each WEAK category. Use the SUMPRODUCT function to compute the Operating Risk Summary measure. Higher values of Operating Risk Summary indicate higher risk.
6. Use a weighted average of financial risk and operating risk to construct a credit risk measure. The appropriate weights on operating and financial risk are not always clear. Add a scroll bar that allows the user to adjust the weights to range from 20% to 80% for the Financial Risk Summary measure (i.e., 80% to 20% for the Operating Risk Summary measure). Use the ROUNDUP function to round the Credit Risk Summary Measure to the next highest whole number. You may assume that the chosen weights remain constant across years.
7. Estimate Debt Rating based on Credit Risk Summary. Using the VLOOKUP function, retrieve the debt rating from the Ratings and Rates sheet that corresponds to the Credit Risk Summary. This should be reported in cells B45:F45.
Submission Instructions:
· Please name your file with your NetID (e.g., xz687.xlsx)
· Submit your file on Canvas under Individual Project
· The due time for Individual Project is 5pm, March 17.