Student Project Activity – Week 2

Student Project Activity – Week 2

1. Week 2: Budget Proposal Section 2.0 Sales Forecast

1. TCOs Addressed:

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.  

1. Project Tasks:

Task 1:

Download Budget Proposal Workbook.xlsx from DocSharing.

Task 2:

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.

Task 3:

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.  

Task 4:

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.

Task 5:

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

1. Grading Criteria

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