Module $11$ Financial Statement Forecasting $$Ongoing Analysis Project$(100$ Points$)($I need this done for Costco Wholesale Corporation and I need it done in Excel please$)$

This module describes methods commonly used to forecast financial statements. The module shows how to forecast a complete set of financial statements $($for one or more years$).$ The module concludes with a parsimonious forecast of select balance sheet and income statement metrics. A project can include both types of forecasts. We can use the full set of financial statements to analyze the company$$s near$$term future performance and position. We can then use the parsimonious forecast for longer$$term forecasts as inputs for valuation models that estimate the company$$s stock price. Importantly, use a spreadsheet for the forecasting process. The SEC website has $$interactive data$$ for annual reports$$these are spreadsheet$$like arrays that can be copied into a spreadsheet. Also, many companies include on their investor relations page an Excel version of their financial statements. Define as many cells as possible with formulas, and reference income statement totals to the related balance sheet accounts. To balance the balance sheet, define the cash account to be equal to the difference between forecasted assets and liabilities plus stockholders$$ equity. $$

Forecasting Preliminaries Begin with the adjusted set of financial statements that reflect the company$$s net operating assets and its operating income that we expect to persist into the future. This requires that we exclude one$$time items and adjust other items to reflect anticipated levels of ongoing activities. $$

Model Assumptions and Inputs The assumptions we use critically impact forecasted numbers. Be as thorough as possible in research and analysis in determining model inputs. The most critical assumption is sales growth. Before we begin, adjust any fiscal years to take care of the $13$th week$($or $53$rd week$)$ problem. Then, use all the reported years$$ sales numbers to compute historical growth numbers. Observe any trends. If the company reports segment sales, compute growth of each segment and compare it with total sales growth. We should forecast each segment separately if growth differs by segment. Read the company$$s MD&A$,$ the footnotes, and any guidance the company voluntarily pro$$ vides. Obtain an industry report and, determine a consensus about sales expectations and the cost environment. As discussed in the module, we assume most costs $($including COGS and SG&A$)$ will not deviate from their historical percentages unless there is evidence to suggest otherwise. Again, scour the footnotes. In the end, use sound judgment and remember that forecasted numbers are subjective. $$

Forecast the Income Statement Use the sales growth assumption to forecast sales for the next fiscal year. Use cost$$level assumptions to forecast all the operating expenses. At this first stage, we typically leave nonoperating expenses and revenues unchanged from prior$$year dollar levels. We return to fine$$ tune these after we forecast the balance sheet. Forecast a preliminary tax expense and net income. We need those numbers to complete the balance sheet. $$

Forecast the Balance Sheet Use the percentage of sales approach to forecast each operating asset and liability, and follow the method described in the module to forecast PPE and intangible assets. Certain operating assets and liabilities will be forecasted to remain unchanged year over year unless we learn otherwise from the financial statements. Forecast debt using the information about scheduled debt maturities. Pay careful attention to forecasting dividends and any stock repurchases or issuances. $$

Forecast the Statement of Cash Flows We construct a statement of cash flows from the company$$s current balance sheet $($from the Form $10$K or annual report$)$ and the forecasted balance sheet. The net income number forecasted will also tie in$.$ Remember that this statement is a mechanical operation, requiring no assumptions or new calculations. $$