Year 1 Jan 245
Feb 244
Feb 250 261.67
Mar 258 256.67
Apr 267 252.67
May 273 258.33
Month
Actual
Sales
3 Month
Moving
240
250
260
270
280
290
Actual
F6-2
Figure 6-2
Scatter Diagram
260
270
280
290
SCATTER DIAGRAM
F6-3
Page 4
270
280
290
FIGURE 6-3
Sales Chart with Regression Line
300
320
Regression Forecast with Seasonal Factors Included
Actual sales first 24 Months,
T6-1
TABLE 6-1 Moving Average Model
Formula for Moving Average:
Year
0
Year 1 Jan 1 245
Feb 2244
Mar 3 250
Apr 4260 246.33 13.67 13.67
May 5 265 251.33 13.67 13.67 249.75 15.25
Jun 6 260 258.33 1.67 1.67 254.75 5.25
Jun 18 278 266.00 12.00 12.00 262.00 16.00
Jul 19 260 272.67 (12.67) 12.67 269.00 9.00
Aug 20 256 270.33 (14.33) 14.33 269.50 13.50
Sep 21 255 264.67 (9.67) 9.67 266.75 11.75
MAD= 2.95 12.16 11.61
Moving Average = Forecast
Time
Period
Actual
Sales
3 Month
Moving
Average
Month
Actual
Numerical
Deviation =
Actual –
Forecast
Absolute
Deviation
D|A-F|
Absolute
Deviation
D|A-F|
4 Month
Moving
Average
( [ 1]) 1
1
…
t n t t
t
A A A
Fn
− − −
+
+ + +
=
Page 6
Year Month
Time
Period
Actual
Sales
W1=0.1,
W2=0.3,
W3=0.6
D|A-F|
W1=0.25,
W2=0.35,
W3=0.40
D|A-F|
W1=4
W2=5
W3=8
D|A-F|
0
Dec 12 270 259.50 10.50 255.25 14.75 256.18 13.82
Year 2 Jan 13 250 267.00 17.00 264.50 14.50 265.00 15.00
Feb 14 250 257.50 7.50 260.75 10.75 259.41 9.41
Mar 15 258 252.00 6.00 255.00 3.00 254.71 3.29
n = 21.00 21.00 21.00
MAD = 10.42 11.65 11.19
TABLE 6-2 Weighted Moving Average Model
Formula for weighted moving average:
( )( ) ( )( ) ( )( )
W
AWAWAW
Ftttt
tS
++
=−− 3122
T6-3
Year D|A−F| D|A−F| D|A−F|
0
Year 1 Jan 1 245
Feb 2 244 245.00 245.00 245.00
Nov 11 265 249.44 15.56 250.73 14.27 250.80 14.20
Dec 12 270 250.99 19.01 253.58 16.42 254.35 15.65
Year 2 Jan 13 250 252.89 2.89 256.86 6.86 258.26 8.26
Feb 14 250 252.60 2.60 255.49 5.49 256.20 6.20
Mar 15 258 252.34 5.66 254.39 3.61 254.65 3.35
Apr 16 267 252.91 14.09 255.11 11.89 255.49 11.51
May 17 273 254.32 18.68 257.49 15.51 258.36 14.64
Formulas for Exponential Smoothing Model:
TABLE 6-3 Exponential Smoothing Model
Time
Period
Actual
Sales (A)
Forecast
for a=0.1
Forecast
for a=0.2
Forecast
for a=0.25
Month
( ) ( )( )
( )
tttt
ttt
FAFF
FAF
−+=
−+=
+
+
a
aa
1
11
Page 8
T6-4
Year Month
x2xy
Year 1 JAN 1 245 1245
FEB 2 244 4488
NOV 11 265 121 2,915
DEC 12 270 144 3,240
Year 2 JAN 13 250 169 3,250
SUMS (S) 300 6,229 4,900 79,027
y=a+bx
Time
Period
(x)
Actual
Sales
(y)
Using the formulas below, we substitute from Table 6-3 above and obtain an intercept (a) of 246.8841 and a
slope (b) of 1.0126
TABLE 6-4 Calculation of the Regression Line
T6-4
Intercept (a)= 246.8841
Slope (b)= 1.012609
Page 10
y= a + bx
a= 246.8841
b= 1.0126
Year Month
0 246.88
Year 1 Jan 1 245 247.90 0.99 245
Feb 2 244 248.91 0.98 244
Mar 3250 249.92 1.00 250
Mar 15 258 262.07 0.98 258
Apr 16 267 263.09 1.01 267
May 17 273 264.10 1.03 273
Jun 18 278 265.11 1.05 278
Jun 30 277.26 1.04 288
Jul 31 278.27 0.99 276
Aug 32 279.29 0.96 268
Sep 33 280.30 0.94 265
TABLE 6-5, Forecast of sales, including seasonal adjustment.
Time
x
Seasonal
Ratio
(A)/(F)
Seasonal
Forecast of
Sales
Regression
Forecast (F)
y = a + bx
Actual Sales (A)
Jan-01 – Dec-02
TABLE 6-6 Forecast of Revenue for a Start-up Service or Trade Business
Hours per
Day
Days per
Week
Billed
Hourly
Rate
Hourly
Wage
Overtime
Wage
Number
of
Workers
Weeks
Worked
per Year
Best Case 10 665 30 45 150
Worst Case 4 4
Best
Case
Worst
Case
Best Case Worst
Gross Revenue 3,900$ 1,040$ 195,000$ 52,000$
Labor Cost 2,100 480 105,000 24,000
Material Cost – –
Gross Profit 1,800$ 560$ 90,000$ 28,000$
Weekly
Annually
T6-7
Sales 200,000$ 250,000$
COG 100,000 125,000$
Gross Profit 100,000$ 125,000$
Operating Expenses
Rent 24,000 24,000
Utilities 3,000 3,600
TABLE 6-7, Pro Forma Income Statement
Income Statements for Time Periods Indicated
Actual
Sales for
2012
Pro Forma
Sales for
2013
Page 13
T6-8
Total Payments 20,267$ 15,517$ 16,267$ 16,017$ 18,517$ 20,517$ 21,267$ 19,517$ 17,767$ 20,517$ 20,767$ 22,517$
Total Receipts 19,020$ 16,110$ 13,930$ 15,640$ 18,800$ 22,330$ 23,030$ 20,930$ 20,130$ 21,610$ 24,410$ 26,920$
Total Payments 20,267 15,517 16,267 16,017 18,517 20,517 21,267 19,517 17,767 20,517 20,767 22,517
Actual Sales 2012
Pro Forma Sales and Cash Receipts 2013
TABLE 6-8 Pro Forma Cash Budget
Monthly Cash Receipts
Monthly Cash Budget
Actual
Pro Forma
Salaries Payable 2,500$ 2,625$
Notes Payable 9,000
Total Current Liabilities 22,750$ 17,625$
Long Term Liabilities
Long Term Loan 30,000$ 50,000$
Total Liabilities 52,750$ 67,625$
Owner’s Equity 17,670 31,091
Total Liabilities & Owner’s Equity 70,420$ 98,716$
TABLE 6-9 Pro Forma Balance Sheet
Balance Sheets for Year Ending December 31
Sales taken from Table 6-7 200,000$ 250,000$
Actual
sales for
2012
Percentage
of 2012
sales
Pro Forma
sales for
2013
Current Assets
Cash 4,000$ 2.00%
5,000$
Accounts Receivabe 21,420 10.71%
26,775
Inventory 15,000 7.50%
18,750
50,525$
37,500
14,063$
11,250
28,438$
Long Term Loan 30,000$ 15.00%
37,500$
Total Liabilities 52,750$ 26.38%
65,938$
Owner’s Equity 17,670 8.84%
22,088
Total Liabilities & Owner’s Equity 70,420$ 35.21% 88,025$
TABLE 6-10 Pro Forma Balance Sheet using percentage of sales method
Balance Sheets for Year Ending December 31
TABLE 6-11 Actual Sales Data .
Time
Period
Actual Sales ($000)
1445
2478
3525
4660