Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

 

| Month     | Sales A | Sales B | Sales C |

|-----------|---------|---------|---------|

| January   | 1000    | 1200    | 800     |

| February  | 1500    | 900     | 1100    |

| March     | 800     | 700     | 600     |

Task :

1. Calculate the total sales for each month using the SUM function.
2. Use TREND function to forecast sales for the next three months based on the existing data.
3. Calculate the month-over-month growth rate for each product using the RATE function.
4. Use FORECAST.ETS function to generate a seasonal forecast for Sales A..
5. Create a line chart to visualize the actual sales and forecasted sales for each product.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

1. Calculate Total Sales for Each Month Using SUM Function
   • Insert a new row at the bottom or top of your data table labeled "Total Sales."
   • Use the formula =SUM(Sales A range) for January, February, and March to calculate the total sales for Sales A.
   • Repeat the same formula for Sales B and Sales C.
2. Use TREND Function to Forecast Sales for the Next Three Months
   • Select a new range of cells where you want to display the forecasted sales.
   • Use the TREND function to forecast sales based on existing data. For example, if your data starts from cell B2 to D4, and you want to forecast for three more months, the formula would be something like:

         =TREND(B2:B4, $A$2:$A$4, A5:A7)

• Adjust the cell references based on your actual data range and where you want to display the forecasted values.
2. Calculate Month-over-Month Growth Rate Using RATE Function
   • Insert a new row labeled "MoM Growth Rate" below your existing data.
   • Use the RATE function to calculate the month-over-month growth rate for each product. For example:

          =RATE(B3, B2)

• Copy the formula across for Sales B and Sales C, adjusting the cell references accordingly.
3. Use FORECAST.ETS Function to Generate Seasonal Forecast for Sales A
   • Select a new cell where you want to display the forecasted value.
   • Use the FORECAST.ETS function to generate a seasonal forecast for Sales A. For example:

          =FORECAST.ETS(A5, B2:B4, $A$2:$A$4, 1, 1)

• Adjust the cell references based on your actual data range and seasonal pattern.
4. Create a Line Chart to Visualize Actual and Forecasted Sales
   • Select the data range including Month, Sales A, Sales B, and Sales C (including forecasted values).
   • Go to the "Insert" tab in Excel and click on "Line Chart" in the "Charts" group.
   • Excel will create a line chart visualizing the actual sales data and the forecasted sales for each product over the given months.