Please see attached
BU.210.650: Advanced Financial Accounting Forecasting Project Objectives · To apply the forecasting framework to a publicly-traded company · To analyze the risk and profitability of the company using financial ratios Instructions The forecasting project is a partnered assignment in which you and a partner assigned by the professor will prepare a set of five-year financial statement forecasts (Income Statement and Balance Sheet) for Walmart using the assumptions provided in Problem 10.1 a (pg. 708). Parts b, c, and d are not required. I provide a template for the forecast in an Excel file on Blackboard. I also provide the calculations to forecast Walmart’s Capital Expenditures, Property, Plant and Equipment, and Depreciation (“Year +1” and “Year +2”) in the same Excel file. You are still required to calculate the numbers for “Year +3” to “Year +5.” In addition, you need to analyze the profitability and risk of Walmart in “Year +5” based on the financial statements (from the view of a financial analyst). You need to prepare this analysis in the form of a one-page executive summary. Your analysis should be supported by adequate profit and risk ratios. Please show the detailed calculations of the ratios in an appendix. Because this is a partnered assignment, you may only consult with me and your assigned partner, and you should not discuss the project with anyone else. The maximum score for this project is 15 points (8 points for the financial forecast and 7 points for the executive summary). Specific Instructions: You may hard-key the 2016 financial information into the spreadsheet, which will provide a basis for your projected numbers. However, you should not hard-key any of your financial statement forecast numbers into your spreadsheet. Rather, the financial statement forecasts should include formulas that link the data in the three spreadsheets based on the assumptions provided in the case (Income Statement, Balance Sheet, and CapEx/Deprecation Expense/PP&E). Cash will serve as the flexible financial account (I have already provided the cash values for each year in the Excel file). In addition, I provided all numbers for noncontrolling interests. Project Deliverables: Your project deliverables will include the following (in this order): 1.) A PDF of the cover page (a template for the cover page will be posted on Blackboard) 2.) A PDF of the one-page executive summary analyzing the company’s financial performance (profitability and risk) in “Year +5” 3.) An Excel version of the report, containing the two forecasted financial statements (Income Statement and Balance Sheet) 4.) An Excel version of the worksheet CapEx, PP&E and Depreciation. These files will be submitted via the Assignment Tool in the appropriate area in Blackboard. Rubric Forecasted Financial Statements (8 points possible total) · Complete and correct calculation of forecasted Income Statement and Balance Sheet numbers Executive Summary (7 points possible total) · Selection and correct calculation of relevant risk and profitability ratios —15% · Structure of executive summary—10% · Quality of content of the risk and profitability analysis (for a user of this analysis, e.g., an investor)—60% · Logical conclusions based on the risk and profitability analysis)—15% Missing Cover Page: Deduction of 1 point Total Possible Points (15 Points) 3 FAQs Forecasting Project Below, you will find frequently asked questions relating to the forecasting project. 1. Q: I am not sure whether to use a constant turnover rate to forecast A/R or to forecast that it will grow at the same rate of sales. A: Both methods should yield consistent forecasts. However, for the sake of simplicity, assume that A/R grows at the same rate as sales. 2. Q: Which Net Income should I use when I calculate the change in Retained Earnings and the Dividends? A: You should use Net Income Attributable to Common Shareholders. 3. Q: How should I forecast inventory? A: Begin by calculating the daily COGS (Annual COGS/365). Then multiply the daily rate by the number of days of COGS in ending inventory as described in the case. 4. Q: How should I forecast Accounts Payable (A/P)? A: Solve for annual purchases using beginning and ending inventory and COGS, and divide by 365 to obtain a daily purchases rate. Then multiply by the number of days of purchases in A/P as described in the case. 5. Q: How should I calculate the interest expense in the Income Statement? A: The interest expense in Year +1 should be based on the average of Notes Payable/ST Debt, LT Debt–Current, and LT Debt–Long-Term (years 2016 and Year +1). Income Statement COMPREHENSIVE INCOME STATEMENT 2016Year +1Year +2Year +3Year +4Year +5 Revenues COGS Gross Profit SG&A Expense Operating Income Interest Income Interest Expense Income Before Tax Income Tax Expense Net Income NI Attributable to NCI(386)(386)(386)(386)(386)(386) NI Attributable to CS OCI Comprehensive Income Balance Sheet BALANCE SHEET 2016Year +1Year +2Year +3Year +4Year +5 Cash & CE8,70513,67520,22527,53935,61444,459 Accounts Receivables (net) Inventory Prepaid Expenses Current Assets PPE (cost) Acc. Depreciation Goodwill Other Assets Total Assets Accounts Payable Accrued Expenses Notes Payable/ST Debt LT Debt - Current IT Payable Current Liabilities LT Debt - Long-Term Deferred Taxes - LT Redeemable NCI- 0- 0- 0- 0- 0- 0 Total Liabilities Common Stock & APIC Retained Earnings Acc. Other Comprehensive Income Total Shareholder's Equity Noncontrolling Interests3,0653,0653,0653,0653,0653,065 Total Equity Total Liabilities and Equity CapEx, PP&E and Depreciation Forecast Development: Capital Expenditures, Property, Plant and Equipment, and Depreciation Property, Plant and Equipment and DepreciationProperty, Plant and Equipment and Depreciation Forecasts: PP&E at cost:2016Year +1Year +2Year +3Year +4Year +5 Beg. balance at cost:188,054198,054 Add: CAPEX forecasts:10,00010,000 End balance at cost:188,054198,054208,054 Accumulated Depreciation: Beg. Balance:(71,538)(81,441) Subtract: Depreciation expense forecasts from below:(9,903)(10,403) End Balance:(71,538)(81,441)(91,843) PP&E—net116,681116,613116,211 Depreciation expense forecast on existing PP&E: Existing PP&E at cost:188,0549,4039,403 Depreciation expense forecasts on new PP&E: Capex Year +1500500 Capex Year +2500 Capex Year +3 Capex Year +4 Capex Year +5 Total Depreciation Expense9,90310,403