PrinciplesofFinance-WEB-115

Prévia do material em texto

Figure 18.22 Cash Forecast Formulas
Using Excel Functions to Balance
Once we get a draft of the forecasts outlined, then the tinkering starts. Additional information can be used to
adjust the formulas, as we saw with the 2% reduction in January sales for the forecasted income statement.
Because we have linked most (though not all) of our expenses, subtotals, and statements together using
formulas, management can also use the forecast workbook to perform scenario and sensitivity analyses,
essentially asking “what if?” and looking at the results. When completed, however, before finalizing the
forecast, it’s important that the financial statements are in balance (particularly the balance sheet, just as the
name implies).
Notice that throughout, we used formulas to calculate subtotals to ensure they are correct and change as
needed. We also linked figures, such as the ending and beginning cash balances, to ensure they are in balance.
Perhaps the easiest but most important thing to do is to ensure that the balance sheet balances. We can do
this with a simple formula that compares total assets to total liabilities and equity. We can see in Figure 18.23
that subtracting one from the other in cell C27 should result in $0. If there is a difference, the formula will
highlight it, forcing us to investigate and correct the sheet so that it balances.
18.6 • Using Excel to Create the Long-Term Forecast 557
Figure 18.23 Forecasted Balancing Formula
558 18 • Financial Forecasting
Access for free at openstax.org
Summary
18.1 The Importance of Forecasting
Forecasting financial statements is important to different users for different reasons. In finance, it’s most
important for assessing the value of future growth plans and planning for future cash flow needs.
18.2 Forecasting Sales
The sales forecast is the foundation on which much of the rest of the forecast is built. Thus, the sales forecast
is completed first. Historical sales data and any other information on the firm, its products, the economy, its
customers, and its competitors are all used to create the most accurate sales forecast possible.
18.3 Pro Forma Financials
Pro forma financial statements are forward looking in nature. They use the sales forecast, historical data,
financial statement analyses, relationships between accounts and statements, and any other information
known about the firm, the environment, and the future to create the most accurate financial statement
forecast possible.
18.4 Generating the Complete Forecast
Interrelationships among historical data, the forecasted income statement, and the forecasted balance sheet
are all used to estimate each line item in the financial statements.
18.5 Forecasting Cash Flow and Assessing the Value of Growth
Once the income statement and balance sheet forecasts are complete, data from those statements,
information on company policies, and account relationships are used to generate a cash forecast. The cash
forecast is important for identifying any gaps in cash flow so that financial managers can plan for cash needs.
It’s also important to review not only the cash forecast but all forecasted financial statements to assess the
overall impact and value of proposed firm growth.
18.6 Using Excel to Create the Long-Term Forecast
Excel can be a powerful tool for creating financial forecasts. Formulas that complete mathematical functions
and tie accounts and financial statements together are used to create the statements, ensure that they
balance, and facilitate scenario and sensitivity analyses.
Key Terms
balance sheet a financial statement that reflects a firm’s asset, liability, and equity account balances at a
given point in time
cash deficit an excess of cash outflows over cash inflows for a given period
cash forecast a financial statement that estimates a firm’s future cash inflows and outflows
cash surplus an excess of cash inflows over cash outflows for a given period
common-size describes a financial statement in which each element is expressed as a percentage of a base
amount
financing activities cash business transactions reported on the statement of cash flows that reflect the use
of financed funds
forecast an estimate of future performance based on historical performance and other contextual
information
income statement a financial statement that measures a firm’s financial performance over a given period of
time
investing activities cash business transactions reported on the statement of cash flows that reflect the
acquisition or disposal of long-term assets
18 • Summary 559
operating activities cash business transactions reported on the statement of cash flows that relate to
ongoing day-to-day operations
pro forma in the context of financial statements, forward-looking
scenario analysis analysis of how various situations and circumstances would impact the financial forecast
sensitivity analysis analysis of the sensitivity of an output variable to a change in an input variable
statement of cash flows a financial statement that lists a firm’s cash inflows and outflows over a given
period of time
statement of stockholders’ equity a financial statement that reports the difference between the beginning
and ending balances of each of the stockholders’ equity accounts during a given period
Multiple Choice
1. Which type of financial statement analysis is most commonly used to create a baseline estimate for a
financial forecast?
a. Trend analysis
b. Common-size analysis
c. Ratio analysis
d. Liquidity analysis
2. What key element of the income statement is used to estimate several other key income statement lines?
a. Cost of goods sold
b. Gross margin
c. Sales
d. Fixed costs
3. Jamal wants to forecast sales for the first quarter of next year. His first assumption is that sales will likely
grow by 3% in the coming year. If Jamal’s monthly sales were $10,000, $9,000, and $11,000 in the first
quarter of this year, what should his sales forecast be for the first quarter of next year?
a. $30,000
b. $30,900
c. $33,000
d. $33,500
4. In the context of a firm’s financial statements, what does pro forma mean?
a. Forward looking
b. Historical
c. Board approved
d. Audited
5. What is the most common length of a forecast if the goal is to forecast cash and assess possible short-
term growth?
a. 3 months
b. 12 months
c. 3 years
d. 5 years
6. When completing a first pass at a forecasted income statement, which type of costs are assumed to be
tied directly to sales?
a. Fixed costs
b. Period costs
560 18 • Multiple Choice
Access for free at openstax.org
c. Variable costs
d. Sunk costs
7. In the cash forecast, if cash inflows exceed cash outflows, what does this create?
a. A cash surplus
b. A cash deficit
c. A long-term liability
d. An undeclared dividend
8. Amelia wants to use a formula in Excel to estimate her utilities expense for each month. She normally pays
her utilities within 30 days. What formula or link might she use in Excel to estimate her cash outflow for
utilities?
a. Sum the past three months’ cost of goods sold from the forecasted income statement
b. Link to the prior month’s accounts payable from the forecasted balance sheet
c. Link to the prior month’s utilities expense from the forecasted balance sheet
d. Link to the prior month’s ending cash balance from the cash flow forecast
9. Amelia wants to use a formula in Excel to estimate her sales for each month. She believes her sales for the
next year will be about 7% higher than this year’s. She also has a big new ad campaign running late this
year that she thinks will add another $5,000 to January sales. Which of the following is an appropriate Excel
formula for Amelia’s January sales?
a. =(lastyearsalesA2*1.07)+5000
b. =(lastyearsales+5000*1.07)
c. =lastyearsalesA2+5000*.07
d. =lastyearsalesA2*5000*1.07
Review Questions
1. Javier’s firm has created a forecasted income statement that shows the firm with a net profit of $25,000 for
the coming year. What can we assume about Javier’s cash flows?
2. Lulu’sfirm’s sales grew by 9%, 11%, and 10% over the past three years, respectively. Lulu wants to take her
first pass at forecasting sales for next year. What percent sales growth would you recommend she use,
and why?
3. Aria wants to create a set of pro forma financial statements. Her goal is to plan for future cash flows and
operations as well as help envision her long-term strategy. What time frames should Aria consider for her
operations and cash flows versus her long-term strategy?
4. What information might you use to calculate the ending balance for retained earnings on a forecasted
balance sheet?
5. Damon estimates his beginning cash balance for June to be $10,000, with cash inflows of $4,000 and cash
outflows of $6,000 for the month. What is Damon’s forecasted ending balance for June?
6. Tanneh wants to use an Excel formula to help her estimate sales for January in her forecasted income
statement. She already has her sales estimate for the full year. Assuming she wants to use the past year’s
income statement percentages to forecast next year’s sales, how would she calculate estimated sales for
January?
18 • Review Questions 561
	Chapter 18 Financial Forecasting
	Summary
	Key Terms
	Multiple Choice
	Review Questions