1
Chapter 6 Forecasting and Pro Forma Financial Statements
CHAPTER OUTLINE
Learning Objectives
Forecasting
Types of Forecasting Models
Practical Sales Forecasting for a Start-up Business
Pro Forma Financial Statements
Gantt Chart
Conclusion
Review and Discussion Questions
Exercises and Problems
Case Study: Hannah’s Donut Shop
REVIEW AND DISCUSSION QUESTIONS
2. What are the basic criteria for selecting a forecasting model?
• Who will be using the forecasting model and what information do they require?
• How relevant is historical data and what is its availability?
3. What role does MAD play in evaluating a forecasting model? Since MAD is a measure of how closely a
4. Compare judgmental, time series, and causal forecasting models. Judgmental models are qualitative and
essentially use estimates based on expert opinion. Time Series models use historical records that are readily
2
5. List and describe at least three time series models.
• Moving Average Model that assumes that some recent previous time periods are the best predictor of future
sales. It uses the arithmetic average of sales for the previous time periods to predict sales in the next time
period.
6. In linear regression, compare independent and dependent variables. An independent variable is one that
does not depend on other variables for its value. Often a time period, temperature, or some other natural
7. Describe how to develop a pro forma income statement (Table 6-7).
• The first thing that must be done is to develop a sales forecast.
• For a new business, cost of goods sold is a percentage of sales, based on industry standards. For an existing
business, cost of goods sold is a percentage of sales based on the company‘s existing income statement.
8. What role does the pro forma cash budget play in financial forecasting (Table 6-8)? The pro forma cash
9. What role does the pro forma balance sheet play in financial forecasting (Table 6-9)? The pro forma
10. In generating a pro forma balance sheet, on what is the percentage of sales method based? It is based
on the fact that assets and liabilities historically vary with sales. So any increase in sales will cause a
11. How are budgets used as monitoring and control tools? Budgets are estimates of the future income and
expenditures of a business or sections (departments) of a business. The budget is the standard that is
3
EXERCISES AND PROBLEMS
1. Jane White has recorded the following sales figures for last year for her business: January, $35,645;
February, $35,456; March, $31,270; April, $32,129; May, $34,456; June, $35,256; July, $36,218;
August, $35,456; September, $34,250; October, $32,156; November, $30,125; and December, $32,275.
a. Construct a table that shows each of these forecasts for the current year, and provide the forecast for
January of year two.
b. Using the available data and your forecasts, which model would you suggest that Jane use for her
business? Weighted moving Average has the lowest MAD (1,728.19).
W1= 0.2
W2= 0.3
W3= 0.5 =0.2
Month
Actual
Sales
3 Month
Mov Avg
Absolute
Deviation
Weighted
Mov Avg
Absolute
Deviation
Exponential
Smoothing
Absolute
Deviation
January Yr1 35,645$ 35,000.00$
February 35,456 35,129.00 327.00
March 31,270 35,194.40 3,924.40
April 32,129 34,123.67$ 1,994.67 33,400.80$ 1,271.80 34,409.52 2,280.52
4
2. Gary Fisher owns five successful health clubs. He believes that he can put a health club in a new
community that currently has no such facility. His research indicates that communities such as this can
support two health clubs.
a. What type of information should Gary gather when selecting a specific site for his health club?
He should use demographic information on the new community and compare it to the information for his
compare favorably to his current sites.
b. What type of forecasting model should Gary use to determine the new location for a health club?
Judgmental models, but specifically he would use the historical analogy to determine the specific site. Market
3. PERIOD 1 2 3 4
DATA 32 14 41 30
a. Make an exponential smoothing forecast for periods 2 through 5 with 2 values of alpha, 0.05 and 0.60,
and an assumed forecast for period 1 of 30.
b. Compute the MAD for each of the above forecasts. MAD = 9.31 and 13.42
Table for Problem 3
=0.05 = 0.60
PERIOD DATA Forecast
Absolute
Deviation
Forecast
Absolute
Deviation
132 30.00 30.00
214 30.10 16.10 31.20 17.20
5
4. Develop a linear regression equation to predict future demand from the following data:
Demand 23 24 31 28 29
Year 2008 2009 2010 2011 2012
Table for Problem 4
Year
Time
Period
(x)
Actual
Demand
(y)
x2
xy
2008
1
23
1
23
2009
2
24
4
48
2010
3
31
9
93
2011
4
28
16
2012
5
29
25
2013
6
2014
7
2015
8
135
55
Intercept=
a. Write the regression equation.
b. Predict demand for 2015.
35)8(6.12.22 =+=+= bxay
c. Name the independent variable. Year expressed as a time period (e.g. 1, 2 etc.).
6
Table 6-11.
Actual Sales Data
Time
Period
Actual Sales
($000)
1
445
2
478
3
525
4
660
5
570
6
600
7
632
8
648
725
750
5. Using the data in table 6-11, calculate a three-month moving average forecast for month 12.
6. Using the data in Table 6-11, calculate the following:
Table 6-11. Actual Sales Data
Solution to Problem 6
W1=
3
W2=
5
W3=
9
Time
Period
Actual Sales
($000)
Forecast
Weighted
Moving Average
Absolute
Deviation
1
445
2
478
3
525
4
660
497.06
162.94
5
570
588.18
6
600
588.53
7
632
601.76
8
648
611.65
9
690
634.82
725
667.41
750
701.12
732.06
a. A weighted-moving average forecast for months 4 through 12, using weights of 3, 5, and 9.
b. What is the MAD for this forecast? 52.60
7
7. Using the data in Table 6-11, calculate the following: NOTE: If the student is proficient and uses a
calculator or spreadsheet, he/she can just calculate the value asked for below without actually showing the
b. What is the intercept?
0364.445
210,1
494,538
210,1
)384,43)(66()723,6)(506(
)( 22 ==
−
=
−
−
xxn
xyxy
2
x
=a
c. Write the regression equation.
d. Calculate a regression forecast for month 25.
Table 6-10. Actual Sales Data
Solution to Problem 7
Time
Period x
Actual
Sales
($000) y
x2 xy
1445 1 445
2478 4 956
3525 9 1,575
8
8. You have just completed the first year of operation for your business and have the following
information: sales, $200,000; cost of goods, $140,000; rent, $18,000; utilities, $8,400; insurance, $2,000;
equipment, $3,500; and interest, $10,000. Your forecast indicates that your sales will increase by 20
a. Using this data, construct an actual income statement for this year and a pro forma income statement for
next year.
b. By what percentage did your net income change?
%99.61100
100,18
100,18320,29
100
0
%=
−
=
−
=xx
ON
CHANGE
c. What are your current profit margin and your pro forma profit margin?
Solution to Problem 8
Actual This
Year
Pro forma
Next year
Sales 200,000$ 240,000$
Cost of Goods Sold 140,000 168,000
Gross Profit 60,000$ 72,000$
Income Statement for current and next year
9
d. In your business, assets and liabilities have historically varied with sales. Assets are usually 80 percent of
sales, and liabilities are usually 55 percent of sales. You anticipate that you will have no owner payout
of net profit. Using the percentage of sales method, determine if any additional financing is needed for
your business next year.
Percentage of sales method equation is:
10
9. Sam Jones has two years of historical sales data for his company. He is applying for a business loan
and must supply his projections of sales by month for the next two years to the bank.
a. Using the data from Table 6-12, provide a regression forecast for time periods 25 through 48.
b. Do Sam’s sales data show a seasonal pattern? Yes as the chart below depicts a seasonal sales pattern.
Slope= 1.92
Intercept= 345.20
MONTH
Actual
Sales
Regression
Forecast
Seasonal
Ratio
0
345.20
1345
347.12 0.99
349.03 1.00
350.95 1.01
352.87 1.02
354.79 1.03
6360
356.70 1.01
7358
358.62 1.00
8352
360.54 0.98
9348
362.46 0.96
10 353
364.37 0.97
11 362
366.29 0.99
368.21 1.00
370.13 1.01
372.04 1.03
373.96 1.04
375.88 1.01
377.79 0.99
379.71 0.97
381.63 0.95
383.55 0.97
385.46 0.99
387.38 1.01
389.30 1.02
391.22 1.04
393.13
26
395.05
27
396.97
28
398.89
29
400.80
402.72
404.64
406.56
408.47
34
410.39
35
412.31
36
414.23
37
416.14
418.06
419.98
421.89
425.73
427.65
429.56
431.48
46
433.40
47
435.32
48
437.23
Solution to problem 9a, UsingTwenty-four Months
of Actual Data
Regression Forecast
11
ACTUAL SALES
390
400
410
420
12
10. Your projected sales for the first three months of next year are as follows: January, $15,000; February,
$20,000; and March, $25,000. Based on last year’s data, cash sales are 20 percent of total sales for each
a. Using the format from the pro forma cash budget in Table 6-8, what is your monthly cash budget for
January, February, and March?
Pro Forma Cash Budget to solve Problem 10. a.
Monthly Cash Receipts
November
December
January
February
March
Sales
15,000
17,000
15,000
20,000
25,000
Current Month Collection at 20% of Sales
3,000
3,400
3,000
4,000
5,000
Outstanding Current Month Accounts Receivable
12,000
13,600
12,000
16,000
20,000
60% of AR Collected Month Following Sale
7,200
8,160
7,200
9,600
4,800
5,440
4,800
Total Receipts
15,960
16,640
19,400
Estimated Cash Payments
4,500
5,500
5,200
Total Payments
4,500
5,500
5,200
Total Receipts
15,960
16,640
19,400
Total Payments
4,500
5,500
5,200
Net Cash Flow
11,460
11,140
14,200
b. What will your accounts receivable be for the beginning of April? We have 40 percent of February
c. Will your company have any borrowing requirements for any month during this three-month period? No
because all of the cash budgets have a positive balance.
13
11. Using Table 6-13, create a pro forma balance sheet using the percentage of sales method. If net income next
year is $50,000, answer the following:
a. How much did the owners take out of the business? Since projected net income is $50,000 and owner’s equity
increased by $6,022 then the owners must have taken $43,978 out of the business.
b. What is the profit margin for next year?
12. Using Table 6-13, what is required new financing if next years sales forecast increases to $400,000, profit
margin is 10 percent, and the payout ratio is 90 percent? Note: If you solve with a calculator or Excel, and don’t
round, you will get $6,036.36. In any event we need financing as the solution is a positive number.
RECOMMENDED TEAM ASSIGNMENT
1. Using a discount store or supermarket. What type of forecasting model would they use to plan for expansion
Table 6-13, answers to question 11.
Total Sales
Current Year
$275,000
Percentage
of Sales
(%)
Forecast
Sales Next
Year
$350,000
Assets
Current Assets
Cash 5,694$ 2.07 7,247
Accounts Receivable 19,662 7.15 25,024
Inventory 3,381 1.23 4,303
Total Current Assets 28,737$ 10.45 36,574$
14
into a particular city? What internal data and external data would they require to develop a 10-year
expansion plan? The basic forecasting model would be judgmental with a combination of historical analogy and
2. Using the financial statements for a public corporation, develop a pro forma income statement and balance
sheet for this company. This assignment is research based and cannot be answered in advance.