YEAR SALES WEIGHTS 1 $1,079,864 0.1 2 $1,238,586 0.2 3 $1,409,078 0.3 4 $1,098,274 0.1 5 $1,578,968 0.3 6 ? ? 1. Using the sales information above, calculate both the averaging and the weighted sales forecasts using Microsoft Excel. Be sure to write the simple formulas in the spreadsheet, rather than calculating the answers by hand and entering the numbers directly into the spreadsheet. In addition, do the following with your spreadsheet. Brief instructions on how to accomplish the Excel functions are below. a. Be sure the gridlines are showing for the print mode. b. Create a title for the table and include it on the top line of the spreadsheet. c. Be sure to include dollar signs where appropriate. Instructions for Excel 1. First, you need to enter the data, as presented in the attachment, into the Excel Spreadsheet. 2. The averaging forecast is simplest. You simply take the previous years of sales, add them up and divide by however many years you have used. In the case of this assignment, you are using five (5) years and forecasting for Year 6. For this assignment, you will need to do this work in Excel. In the place of the ? for Year 6 at the bottom of Column 2 you will need to add the sales numbers up and divide by five. There are two ways you can accomplish this. You may use the SUM function by typing in the following: =SUM(B2:B6)/5. Or you can use the AVERAGE function. 3. The weighted sales forecast is a little more complicated. You will need to create a 4th Column. The numbers in the 4th Column will be the sales in the 2nd Column multiplied by the weight in the 3rd Column. The command to do this is =B2*C2, for the first place and then increase the numbers as you go down the 4th Column. Once you get all five numbers calculated for this column, under the fifth number you will simply sum the numbers in Column 4 by using the following formula: =SUM (D2:D6). 4. You should also use the SUM function to add the 3rd Column to be sure that the numbers add up to be 1.0.
YEAR SALES WEIGHTS
1 $1,079,864 0.1
2 $1,238,586 0.2
3 $1,409,078 0.3
4 $1,098,274 0.1
5 $1,578,968 0.3
6 ? ?
1. Using the sales information above, calculate both the averaging and the weighted sales forecasts using Microsoft Excel. Be sure to write the simple formulas in the spreadsheet, rather than calculating the answers by hand and entering the numbers directly into the spreadsheet. In addition, do the following with your spreadsheet. Brief instructions on how to accomplish the Excel functions are below.
a. Be sure the gridlines are showing for the print mode.
b. Create a title for the table and include it on the top line of the spreadsheet.
c. Be sure to include dollar signs where appropriate.
Instructions for Excel
1. First, you need to enter the data, as presented in the attachment, into the Excel Spreadsheet.
2. The averaging forecast is simplest. You simply take the previous years of sales, add them up and divide by however many years you have used. In the case of this assignment, you are using five (5) years and
3. The weighted sales forecast is a little more complicated. You will need to create a 4th Column. The numbers in the 4th Column will be the sales in the 2nd Column multiplied by the weight in the 3rd Column. The command to do this is =B2*C2, for the first place and then increase the numbers as you go down the 4th Column. Once you get all five numbers calculated for this column, under the fifth number you will simply sum the numbers in Column 4 by using the following formula: =SUM (D2:D6).
4. You should also use the SUM function to add the 3rd Column to be sure that the numbers add up to be 1.0.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 4 images
