Reference no: EM132310613
Milestone Guidelines
Overview: This milestone will help you to complete Sections II and III of the final project. You will review balance sheets, statements of cash flows, and income statements. You will also analyze different valuation standards and focus on the difference in standards used for recording revenue, expenses, and fair values of assets and liabilities.
Prompt: Produce a financial statement analysis and valuation report that summarizes the financial health, projected future performance, and estimated value of the publicly traded (S&P 500) company that you selected at the beginning of the course. As an independent financial accounting analyst working for a major trade journal, your report is intended to inform a general audience about the overall financial well-being of the company and how it compares to a major competitor.
Your report should cover information of interest to both internal and external stakeholders-summarizing major findings, suggesting ways to improve operational performance, and assessing investment potential. Keep in mind that brief, clear communications are essential in effectively reaching business and media audiences.
Specifically, the following critical elements must be addressed:
I. Balance Sheet Analysis. In this section, use financial statements and accompanying notes to:
A. Analyze what the company's current and prior year liquidity and debt-to-equity ratios say about the company's financial health, justifying your response. Consider the appropriate level of debt and how this year's performance compares to the previous year's.
B. Consider normalization adjustments when adjusting the balance sheet to be comparable to competitors, when creating the prospective balance sheet, and when calculating the final company valuation as either a premium or discount. (Include your analysis spreadsheet in Excel as an appendix.)
C. Analyze the company's balance sheet for the current and previous year using a horizontal analysis. (Include your horizontal analysis spreadsheet in Excel as an appendix.) Explain your findings.
D. Comparison. Analyze the competitor's balance sheet indicators for opportunities for the selected company to improve its own performance. Justify your response using the financial statements of both the company and its competitor.
II. Income Statement and Cash Flow Analysis. In this section, use financial statements and accompanying notes to:
A. Analyze the profitability of your selected company using appropriate profitability ratio(s) and a vertical analysis of the company's current and prior year income statement. (Include your vertical analysis spreadsheet in Excel as an appendix.) Be sure to explain your findings.
B. Normalization adjustments. Analyze historical income statements to determine whether there were any non-recurring or extraordinary items that should be removed from the income statement. An example would be aggressive expense recognition or conservative revenue recognition, which could either depress or inflate earnings. Adjusting these items will make the target company more comparable to the others.
C. Free cash flow. Analyze what your selected company's free cash flow figures for the current and prior year say about the company's financial health. Consider other sources or uses of available discretionary cash.
D. Dividend paying capacity of the company. Determine if the company is on track to pay out dividends this year and whether the dividend payout ratio increase or decrease.
Rubric
Guidelines for Submission: Your milestone should be completed using the Financial Analysis Section of the Business Valuation Report template. Sources should be cited according to APA Style. For the analysis, use and submit the pertinent sections of the Business Valuation Model Excel file. Include a link to the chosen company's financial information for confirmation purposes.
To begin follow these steps:
1. Enter the numbers from your company's balance sheet and income statement for each year, starting with the most recent year through the prior five years (Example: if the most recent is 2017 then go back through
2013).
2. Ratios auto-calculate but you may wish to make an adjustment if necessary. You will only use the ratios for explanatory or analysis purposes in your report. There is nothing more to do with them in this workbook.
3. Create your prospective analysis by changing the growth rate for Revenue, the percentage of Revenue for Gross Profit and Operating expenses, and then add Other Income or expense items. You can do this in the cells highlighted in yellow. This will give you your projected net income, which you will then use to discount to present value on the "dcf" tab later. The default number for Revenue in the prospective analysis is the most recent year's Revenue number plus 2.5%. You may change it.
4. On the "discount rate" tab you are welcome to leave the number as is or go through and make adjustments. In most cases you will need access to data that is unavailable or requires a paid subscription, which is why you're allowed to keep the default values. If you're able to obtain any of those figures then you may use them. The detail was provided to expose you to the concepts, but not actually require the research since it may be cost prohibitive.
5. The "dcf" tab feeds your projected Net Income figures from the "prospective analysis" tab. To that number you will add back depreciation since it's a non-cash item and then subtract expected capital expenditures or and planned debt reductions. You may estimate these if you're unable to find any projection by the company. It is not required that these be fully accurate since you don't have access to management's plans. Enter those numbers in the yellow highlighted cells. You shouldn't have to change any other cells in that tab.
6. On the last tab, "valuation summary", the only values you need to change are the cells in yellow for the DLOC and the DLOM. You may leave these as the default values since these also require access to data that may be only acquired via subscription or purchase. If you're able to find material supporting a change in those values then you're free to do so. The goal in introducing them in this manner is to get you exposed to the concepts, not the actual calculation as that is beyond the scope of this course.
Consider these factors when working through the model:
1. The financial statements you encounter in the annual report will look differently than they do in this model. Categories will be different than what you find in the annual report, so just use your best judgement when classifying them and if you need to lump certain costs together then do so.
(Example: your company shows Cost of Sales of $100k, G&A of $50k, and Marketing expense of $10k. Combine the G&A and Marketing in the single line on the income statement called "General, Administrative and other non-operating expenses" in the amount of $60k. This places Marketing into the "Other" catch all category.
2. You may insert any "Key Assumptions" that you want to convey using the space below the balance sheet, income statement, or prospective analysis. This could be anything from combining certain line items to explaining apparent anomalies.
3. Make sure to net your interest income and expense on the income statement. So in some years you may have a positive balance and a negative in others.
4. The "Normalization adjustments" listed on the "income statement" tab are referring to the adjustments discussed in module three. To recap - Normalization adjustments are changes that you as an analyst can make in order to "normalize" any anomalies or non-recurring items that may have been reported in the financial statements.
For example, if your company was exposed to a natural disaster and you know management does not expect that type of major expense in the future then you can add it back under this section. Another example would be a class-action lawsuit that resulted in a major settlement. While companies are always subject to lawsuits, one that results in a material settlement may be removed if it's unexpected to occur again in the near future.
Attachment:- Business Valuation Model.rar