Reference no: EM132387638

NBA 5110 Financial Modeling

Cornell University

FINAL ASSIGNMENT- INDIVIDUAL

Background: Your job is to determine the debt rating for Abercrombie & Fitch (ANF). Credit analysis requires assessing the financial and operating risk of a company, usually with ratios based on financial statement data. However, financial statements projections are based on assumptions sometimes subject to considerable volatility. Changes in key assumptions can dramatically affect assessments of operating and financial risk of a company.

Given: Your starting point is the ANF-Start File.xlsx, available on Canvas. This file provides historical Balance Sheet and Income Statement for ANFas well as assumptions about future performance under three scenarios: Best Case, Base Case, and Worst Case.

Instructions:

1. Add a Combo Box to the Assumptions sheet to allow the selected scenario to determine the Income Statement and Balance Sheet assumptions being used for future projections. You do not need to make any adjustment for deferred taxes as scenarios change.

• To insert the Combo Box, you must first enable the Developer tab. You can do this within ExcelOptions(Alt F T). Within Options, go to Customize Ribbon to enable the Developer tab. Within the Developer menu, go to Insert and select the Combo Box option [Alt L I C].
• Once you have inserted the Combo box, use CHOOSE function in Assumptions Being Used on the Assumptions tab (Row 63 and below) to indicate which scenario (and corresponding assumptions) are being used for the financial statements subsequently projected.

2. After you have ensured that the assumptions being used correctly reflect the selected scenario, construct Income Statements, Balance Sheets and Statements of Cash Flows for ANF for the period 2019-2023, with supporting schedules for Working Capital, Fixed Assets, Debt & Shareholder Equity and Valuation.Use a moving average of the preceding three years for Working Capital projections. Use straight line to compute depreciation.Treat "Deferred Leases" (a non-current liability on the Balance Sheet) as a financing activity.In the Valuation tab, compute an estimate of value per share for ANF as of 12/31/2019by discounting the appropriate projected Free Cash Flows to the Firm. Make sure your model is robust to changes in assumptions.

3. Compute financial and 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 Equivalentsand Receivables in the numerator. For all ratios involving Debt, define Debt as the sum of the Revolver and Long Term Debt (current and non-current portions). Define EBITDA as operating income before depreciation and amortization.

The ratios must be aggregated into a useful measure of credit risk. This aggregation will follow these steps:

4. 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.
• 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.

5. Bonus Point:

• In Risk Tables tab in ANF_start_file, I manually fill in the cutoff points and risk ratings (Column G to Q) for you to refer when preparing the Rating Calculation sheet. If you'd like to challenge yourself and earn some bonus points, you can replace these hard-coded input using formula to extract the cutoff points and the risk ratings from the Financial Risk and Operating Performance Assessment Criteria table (column A to D).

6. Use conditional formatting such that cells reporting LOW risk or STRONG performance have a green fill with white letters, cells reporting MEDIUM risk or AVERAGE performance have a yellow fill with black letters, and cells reporting HIGH risk or WEAK performance have a red fill with white letters.

7. 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.

8. 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.

9. Use a weighted average offinancialrisk and operating risk to construct acredit 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.

10. 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.

Attachment:- Financial Modeling.rar