Answers

[Solved] Week 2: Budget Proposal Section 2.0 Sales Forecast

Student Project Activity – Week 2 A. Week 2: Budget Proposal Section 2.0 Sales Forecast B. TCOs Addressed: TCO 5: Given a new... [Show More] 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. C. 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. D. 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. E. 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. F. 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 [Show Less]

Preview 1 out of 4 pages