Question details

Week 2: Budget Proposal Section 2.0 Sales Forecast
$ 10.00

 

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

 

Available solutions