Description
Please use the attached Excel template to complete and submit the work.
Q2-0
Please study the examples in slides and underlying spreadsheet. Try replicate the spreadsheet in excel. You don’t need to turn in this part.
Q2-1
Statement of Cash Flows The statements of cash flow for Walmart (WMT) are found below for the years 2010 through 2013:
a) Describe Walmart’s cash flows from operations for the period. Is the firm generating positive cash flow? How has this cash flow changed over time?
b) Is Walmart investing cash in its business? How has Walmart been financing its operations?
c) How would you describe Walmart’s pattern of net cash flows for the period?
Q2-2
Forecasting Pro Forma Financial Statements Prepare a pro forma income statement and balance sheet for Webb Enterprises (see Problem 6-7), where revenues are expected to grow by 20% in 2015 ~ 2017. Make the following assumptions in making your forecast.
• The income statement expenses are a constant percentage of revenues except for interest, which is charged at 5% of previous interest-bearing borrowings, and taxes, which equal 40% of earnings before taxes.
• The cash and marketable securities balance remain equal to$500, and the remaining current asset accounts increase in proportion to revenues.
• Net property, plant, and equipment increase in proportion to the increase in revenues.
• Depreciation will be estimated as 1% of sales.
• Accounts payable increases in proportion to firm revenues.
• Dividend of 400 are paid and will remain the same.
• Owners’ equity increases by the amount of firm net income minus dividend.
• Long-term debt remains unchanged, and short-term debt changes in an amount that balances the balance sheet.
a) Replicate the forecast for 2015 with excel formula.
b) Check the formula against the results for 2016.
c) Extend your forecasting to 2017.
d) Forecasting Firm FCF for 2017.
Your grade will be based on both the excel replication and new calculations.
Q2-3
Select a company for project analysis. Below is the guideline.
a) The company should be a Food or Beverage company.
b) For the company you pick, you need to find another three companies in the same business. Keep the list for your record.
c) Register a free account with barchart.com to collect the financial data
a. Login barchart.com
b. Type in selected stock ticker
c. In the stock page à left column à Financial section
i. Select balance sheet -> download
ii. Select income statement -> download
iii. Select cash flow statement -> download
d. Copy & paste the data in your worksheet
i. Organize the data in the order of ascending date from left to righ.
d) Compare your company with the industry or competitors. Conduct a brief SWOT analysis.
Hint:
• This is the first step of your project.
• There is no need to include long paragraph analysis in the excel, but you should have the data collected for analysis later.