Financial Forecasting
Review the current financial statements of Starbucks through Yahoo! Finance Links to an external site.or the EDGAR | Company Filings Links to an external site.database in the Filings and Forms page. You can access the financial statements by going to the Yahoo! Finance webpage, typing in the stock symbol of Starbucks, and then clicking on the “Financials” tab. Watch the Week 1 Assignment video with Dr. Kevin Kuznia, DBA, CSSBB, PMP, available in the online classroom.
Reviewing the previous quarter’s financial statements will provide you with data to construct pro forma financial statements for Starbucks and make some basic projections. This week, you will be charged with constructing two pro forma financial statements and addressing some questions about your projections. The two financial statements will include an Income Statement and Balance Sheet.
Part 1
Use the EDGAR | Company Filings Links to an external site.or Yahoo! Finance Links to an external site.database to download the last 10-Q from Starbucks into Excel. Use the downloaded data to complete the Income Statement and Balance Sheet on the appropriate tabs in the Financial Forecasting Template. Assume the following:
Sales will increase for the next quarter by the same percentage increase from the previous quarter to the last reported quarter. For example, if sales increased 8% from the last quarter to the current reported quarter, you will use 8% as the sales increase for your pro formas.
Calculate the expenses to determine what will change and what will remain the same.
Sample Solution
Income Statement:
-
Revenue:
- Locate "Net Sales" or "Total Revenue" in the 10-Q for the most recent quarter.
- This represents the current quarter's revenue (Q2).
-
Growth Rate:
- Identify the percentage change in revenue from Q1 to Q2. This might be in the Management Discussion and Analysis (MD&A) section or footnotes.
Full Answer Section
-
Projected Revenue (Q3):
- Apply the growth rate from step 2 to Q2's revenue to estimate revenue for the next quarter (Q3).
Example:
- Current Quarter Revenue (Q2): $10,000,000
- Growth Rate (Q1 to Q2): 5%
- Projected Revenue (Q3): $10,000,000 * (1 + 0.05) = $10,500,000
- Cost of Goods Sold (COGS):
- Analyze the historical relationship between COGS and Revenue in the 10-Q. COGS often has a proportional relationship with revenue.
- Estimate COGS for Q3 using the historical percentage of COGS to revenue.
Example:
- Historical COGS/Revenue Ratio: 60%
- Projected Revenue (Q3): $10,500,000
- Projected COGS (Q3): $10,500,000 * 0.60 = $6,300,000
-
Operating Expenses:
- Identify variable expenses (change with revenue) and fixed expenses (remain constant) in the 10-Q. Examples:
- Variable: Commissions, delivery costs
- Fixed: Rent, salaries
- Analyze historical trends and MD&A for expected changes in operating expenses for Q3.
- Project variable expenses based on the revenue growth rate and keep fixed expenses constant.
- Identify variable expenses (change with revenue) and fixed expenses (remain constant) in the 10-Q. Examples:
-
Interest Expense and Other Expenses:
- Analyze the 10-Q to understand these expenses and project them based on historical trends or mentioned changes.
-
Tax Expense:
- The tax expense is a percentage of pre-tax income. Project it based on projected pre-tax income and the historical effective tax rate.
Balance Sheet:
-
Current Assets:
- Analyze the composition of current assets (cash, inventory, receivables) in the 10-Q.
- Project changes based on historical trends or significant changes mentioned (e.g., planned inventory build-up).
- Estimate some current assets based on projected revenue using industry benchmarks or historical ratios (e.g., inventory turnover ratio).
-
Non-Current Assets:
- Analyze the composition of non-current assets (property, plant, and equipment) in the 10-Q.
- Project changes based on mentioned capital expenditure plans or depreciation schedules.
-
Current Liabilities:
- Analyze the composition of current liabilities (accounts payable, accrued expenses) in the 10-Q.
- Project changes based on historical trends or significant changes mentioned (e.g., expected increase in accounts payable due to supplier negotiations).
- Estimate some current liabilities based on projected COGS using industry benchmarks or historical ratios (e.g., payables turnover ratio).
-
Non-Current Liabilities:
- Analyze the composition of non-current liabilities (long-term debt) in the 10-Q.
- Project changes based on mentioned debt issuance or repayment plans.
-
Shareholder Equity:
- This is the balancing item on the Balance Sheet. Once you project all other elements, calculate the difference to arrive at the projected shareholder equity.
Important Note:
This is a simplified approach. Real-world financial forecasting involves complex calculations and considerations. However, this process provides a framework to get you started using the downloaded 10-Q.