# Student Project Activity – Week 2

Student Project Activity – Week

2

1. Week

2

: Budget Proposal Section 2.0 Sales Forecast

TCO 5:  Given a new business startup or new product introduction and the need to make a forecast when historical data is not available, create the forecast for the organization.TCO 10:  Given a description of a new business, new product, service or project develop, present and defend the budget.

1. Project Activity Overview – Scenario / Summary:

Last week, you selected a business for which you’ll make a budget proposal.  Your first step is to create a sales forecast (in sales dollars) when no historical data is available.    Use methods such as historical analogy, expert judgment, consumer surveys, the Delphi method, or calculations based on population distributions, estimated growth rates, or expected market penetration rates to arrive at reasonable sales figures for your business for the next 5 years.

Use the Budget Proposal Workbook.xlsx and Budget Proposal Template.docx.

1. Deliverables:

Complete Section 2.0 (including sections 2.1 and 2.2) in the Budget Proposal Template.docx after doing research and performing calculations to arrive at your 5 year forecast.  Also, provide calculations in the Budget Proposal Workbook.xlsx.

Add section 2.0 to your Budget Proposal Template and save it as

YourName_Project_WK2.docx.

Save your sales forecast in the worksheet tab labeled Section 2.1 and 2.2 as

YourName_Worksheet_WK2.xlsx

and upload both files to the

Week

2

Project

Dropbox.

Research the area in which your business is located, and do calculations in the Excel workbook which produce a reasonable dollar value forecast based on population size, growth rates, an estimate of the percent of the population expected to purchase your product, and the dollar value of the average sale over the 5 year planning horizon.  Do these calculations in the Section 2.1 and 2.2 tab of the Budget Proposal Workbook.xlsx.  Also, feel free to use other methods described in this course you feel are appropriate to estimate sales for your new business startup’s first five years.

Write section 2.1 and 2.2 of the Budget Proposal Template.docx document, summarizing your forecast in a table, and also describing and justifying your methodology for arriving at the sales forecast.  Follow the instructions in section 2.0 of the Budget Proposal Template.docx when writing these sections.  Also, update your works cited Section 6.0 in the template with any research you did.

Paste the first paragraph of the 1.0 Executive Summary template into the Budget Proposal Template.docx so your professor is reminded which business you’re doing.

Save the draft of the Budget Proposal Word document and Budget Proposal Excel calculation and submit it to the Week 2 Project Dropbox.

a

Description

Suggested Points

There is a 5 year sales forecast in the Word document Section 2.1 and the Excel spreadsheet (in sales dollars).

2

The 5-year forecast appears to be based on research, reasonable assumptions, and methodologies described in the course based on the description in section 2.2 of the template.

4

The 5-year forecast calculations appear to be correct.

4

Total Points

10 points

Professor Notes to Student:

• No historical data will be made available.
• Use any information you were furnished with the case (along with independent research) to complete this week’s requirements.
• Use your choice of historical analogy, expert judgment, consumer survey, Delphi method, or calculations based on population distributions, estimated growth rates or expected market penetration rates to arrive at reasonable sales figures for your business for the next 5 years.

Assignment Expectations:

• Submit the Excel file provided in Doc Sharing with the appropriate tab updated. Show all calculations.  An example has been provided below.
• Submit the Word file provided in Doc Sharing. Relevant screen shots from the Excel file may be included.  You must include the findings of the calculations (sales forecast results), what the results mean (forecasted sales strong relative to the competition, but lower than established business, etc); How sales trend over the years (start up phase, then growing in later years, etc)?
• Also, you must include what assumptions were made to arrive at these results.  For example, what company did you research, what were your findings, what adjustment factor did you apply to scale down the researched company’s sales to a start up level, what growth rate did you apply to sales and how will you achieve that growth via business strategy? Were your sales seasonally adjusted?  If so, why, and what components of your business are seasonally driven?
• Be sure to include both sales units by month and by year, as well as the selling price for each product or service.

Show all references in the Works Cited Section.

Sales Forecast Examples:

Forecast of sales, including seasonal adjustment

y=

a + bx

Average

a=

110.344

Selling Price

3,000.00

b=

0.76913

Time            x

Expected Sales in Units

Regression Forecast

(F)

y = a + bx

Seasonal Ratio

(A)/(F)

Seasonal Forecast of Sales

Average Dollar Sales Forecast

Annual Sales Forecast in Dollars

Year

Month

Year 1

Jan

1

90

111.11

0.81

90

270,000

Feb

2

95

111.88

0.85

95

285,000

Mar

3

105

112.65

0.93

105

315,000

Apr

4

110

113.42

0.97

110

330,000

May

5

125

114.19

1.09

125

375,000

Jun

6

140

114.96

1.22

140

420,000

Jul

7

150

115.73

1.30

150

450,000

Aug

8

150

116.50

1.29

150

450,000

Sep

9

130

117.27

1.11

130

390,000

Oct

10

100

118.04

0.85

100

300,000

Nov

11

90

118.80

0.76

90

270,000

Dec

12

85

119.57

0.71

85

255,000

4,110,000

Year 2

Jan

13

99

120.34

0.82

99

297,000

Feb

14

105

121.11

0.87

105

315,000

Mar

15

116

121.88

0.95

116

348,000

Apr

16

121

122.65

0.99

121

363,000

May

17

138

123.42

1.12

138

414,000

Jun

18

154

124.19

1.24

154

462,000

Jul

19

165

124.96

1.32

165

495,000

Aug

20

165

125.73

1.31

165

495,000

Sep

21

143

126.50

1.13

143

429,000

Oct

22

110

127.27

0.86

110

330,000

Nov

23

99

128.03

0.77

99

297,000

Dec

24

94

128.80

0.73

94

282,000

4,527,000

Year 3

Jan

25

129.57

0.82

106

317,316

Feb

26

130.34

0.86

112

335,513

Mar

27

131.11

0.94

123

370,484

Apr

28

131.88

0.98

129

387,012

May

29

132.65

1.11

147

440,290

Jun

30

133.42

1.23

164

491,888

Jul

31

134.19

1.31

176

526,669

Aug

32

134.96

1.30

175

526,320

Sep

33

135.73

1.12

152

455,846

Oct

34

136.49

0.86

117

350,424

Nov

35

137.26

0.77

105

315,180

Dec

36

138.03

0.72

99

298,286

4,815,229

Year 4

Jan

37

138.80

0.82

114

341,238

Feb

38

139.57

0.86

120

361,141

Mar

39

140.34

0.95

133

398,635

Apr

40

141.11

0.98

139

415,864

May

41

141.88

1.11

158

473,422

Jun

42

142.65

1.23

176

528,293

Jul

43

143.42

1.31

189

565,509

Aug

44

144.19

1.31

188

564,995

Sep

45

144.96

1.13

163

489,223

Oct

46

145.72

0.86

125

375,992

Nov

47

146.49

0.77

113

338,096

Dec

48

147.26

0.73

107

320,323

5,172,731

Year 5

Jan

49

148.03

0.82

121

363,225

Feb

50

148.80

0.86

128

384,026

Mar

51

149.57

0.94

141

423,748

Apr

52

150.34

0.98

147

442,123

May

53

151.11

1.11

168

502,890

Jun

54

151.88

1.23

187

561,209

Jul

55

152.65

1.31

200

600,510

Aug

56

153.42

1.30

200

599,736

Sep

57

154.18

1.12

173

519,108

Oct

58

154.95

0.86

133

398,810

Nov

59

155.72

0.77

119

358,481

Dec

60

156.49

0.72

113

339,287

5,493,153

Please note that the table above is an example only.  You should enter your own expected sales data, which will then be  used to calculate the slope and intercept, as well as the regression forecast.

Year 1

Product Category

Average Sales Price

Jan

Feb

Mar

Apr

May

June

July

Aug

Sept

Oct

Nov

Dec

Total

Product A

300

9,000

9,300

9,600

10,200

9,900

9,600

10,200

12,000

11,400

10,800

15,000

18,000

135,000

Product B

500

6,000

7,000

8,000

7,500

8,500

8,000

8,500

12,500

11,500

11,000

17,500

22,500

128,500

Product C

450

7,200

6,750

7,650

7,200

8,550

8,100

9,000

12,600

16,650

13,500

16,200

22,500

135,900

Product D

650

5,200

4,550

5,850

4,550

6,500

5,850

7,150

8,450

11,050

9,100

16,250

29,250

113,750

Product E

400

8,000

8,800

8,800

8,400

9,200

9,600

9,200

12,000

12,800

10,800

16,000

22,000

135,600

Total

35,400

36,400

39,900

37,850

42,650

41,150

44,050

57,550

63,400

55,200

80,950

114,250

648,750