Using Excel, run descriptive analysis for the first 12 months, the next 12 months, etc., on the dataset.Determine the 12 period moving average forecasts and the simple exponential smoothing forecasts (α = 0.05) based on the data. The mathematical formulas for these forecasting techniques are as follows. N period Moving Average Forecast (for next period) = (Sum of Actuals for N number of previous periods) / N. Note: For this assignment, assume N=12. Simple Exponential Smoothing Forecast (for next period) = Previous Period Forecast + (α * (Previous Period Actual - Previous Period Forecast)). Note: For this assignment, assume α = 0.05. For the Period 1, assume that the Forecast is the same as the Actual.value. Mean Absolute %Error = Average of: ((|Actual - Forecast|) / Actual )) for all periods where Actuals and Forecasts exist.Create a line chart for each forecast and ensure that each line chart also contains the historical dataset. Compute the mean absolute percentage error for each forecast, based on the available and computed data.Based on the line charts and the mean absolute percentage error calculations, indicate which forecasting technique should be usedUsing Excel, run descriptive analysis for the first 12 months, the next 12 months, etc., on the dataset.Determine the 12 period moving average forecasts and the simple exponential smoothing forecasts (α = 0.05) based on the data. The mathematical formulas for these forecasting techniques are as follows. N period Moving Average Forecast (for next period) = (Sum of Actuals for N number of previous periods) / N. Note: For this assignment, assume N=12. Simple Exponential Smoothing Forecast (for next period) = Previous Period Forecast + (α * (Previous Period Actual - Previous Period Forecast)). Note: For this assignment, assume α = 0.05. For the Period 1, assume that the Forecast is the same as the Actual.value. Mean Absolute %Error = Average of: ((|Actual - Forecast|) / Actual )) for all periods where Actuals and Forecasts exist.Create a line chart for each forecast and ensure that each line chart also contains the historical dataset. Compute the mean absolute percentage error for each forecast, based on the available and computed data.Based on the line charts and the mean absolute percentage error calculations, indicate which forecasting technique should be used***need steps in excel to solve*** **Customer Forecasting and Error Analysis****Data Overview:**This table presents a dataset over 17 months where the "Customers" column indicates the actual number of customers for each month.**Forecasting Methods and Error Analysis:**1. **12 Period Moving Average Forecast:** - This column shows the forecasted customer numbers using a 12-period moving average method. - Mean Absolute % Error indicates the error percentage corresponding to this forecast method for each month.2. **Simple Exponential Smoothing (α = 0.05):** - This method uses α (alpha) equal to 0.05 for the exponential smoothing calculation. - The forecasted values are shown, followed by their respective Mean Absolute % Error.3. **Seasonal Average Forecast:** - This column displays forecasts based on seasonal averages for months 13 to 17.**Graph/Diagram Explanation:**The table is divided into four sections:- The first section simply lists the months and actual customer data.- The second section is the 12 Period Moving Average Forecast with associated error percentages.- The third section details the Simple Exponential Smoothing Forecast results with their errors.- The final section provides the Seasonal Average Forecast values for select months.This dataset allows users to compare different forecasting methods and understand the error associated with each technique, aiding in selecting the most reliable forecasting method.

MonthsCostumers1103211331234125514961447151816591781019211202122001311014114151161613417132

Answered: Using Excel, run descriptive analysis…

MATLAB: An Introduction with Applications

6th Edition

ISBN:9781119256830

Author:Amos Gilat

Publisher:Amos Gilat

Chapter1: Starting With Matlab

Section: Chapter Questions

Problem 1P

See similar textbooks

Related questions

Question

Using Excel, run descriptive analysis for the first 12 months, the next 12 months, etc., on the dataset.
Determine the 12 period moving average forecasts and the simple exponential smoothing forecasts (α = 0.05) based on the data. The mathematical formulas for these forecasting techniques are as follows. N period Moving Average Forecast (for next period) = (Sum of Actuals for N number of previous periods) / N. Note: For this assignment, assume N=12. Simple Exponential Smoothing Forecast (for next period) = Previous Period Forecast + (α * (Previous Period Actual - Previous Period Forecast)). Note: For this assignment, assume α = 0.05. For the Period 1, assume that the Forecast is the same as the Actual.value. Mean Absolute %Error = Average of: ((|Actual - Forecast|) / Actual )) for all periods where Actuals and Forecasts exist.
Create a line chart for each forecast and ensure that each line chart also contains the historical dataset.
Compute the mean absolute percentage error for each forecast, based on the available and computed data.
Based on the line charts and the mean absolute percentage error calculations, indicate which forecasting technique should be used
Using Excel, run descriptive analysis for the first 12 months, the next 12 months, etc., on the dataset.
Determine the 12 period moving average forecasts and the simple exponential smoothing forecasts (α = 0.05) based on the data. The mathematical formulas for these forecasting techniques are as follows. N period Moving Average Forecast (for next period) = (Sum of Actuals for N number of previous periods) / N. Note: For this assignment, assume N=12. Simple Exponential Smoothing Forecast (for next period) = Previous Period Forecast + (α * (Previous Period Actual - Previous Period Forecast)). Note: For this assignment, assume α = 0.05. For the Period 1, assume that the Forecast is the same as the Actual.value. Mean Absolute %Error = Average of: ((|Actual - Forecast|) / Actual )) for all periods where Actuals and Forecasts exist.
Create a line chart for each forecast and ensure that each line chart also contains the historical dataset.
Compute the mean absolute percentage error for each forecast, based on the available and computed data.
Based on the line charts and the mean absolute percentage error calculations, indicate which forecasting technique should be used
***need steps in excel to solve***

**Customer Forecasting and Error Analysis**

**Data Overview:**

This table presents a dataset over 17 months where the "Customers" column indicates the actual number of customers for each month.

**Forecasting Methods and Error Analysis:**

1. **12 Period Moving Average Forecast:**
- This column shows the forecasted customer numbers using a 12-period moving average method.
- Mean Absolute % Error indicates the error percentage corresponding to this forecast method for each month.

2. **Simple Exponential Smoothing (α = 0.05):**
- This method uses α (alpha) equal to 0.05 for the exponential smoothing calculation.
- The forecasted values are shown, followed by their respective Mean Absolute % Error.

3. **Seasonal Average Forecast:**
- This column displays forecasts based on seasonal averages for months 13 to 17.

**Graph/Diagram Explanation:**

The table is divided into four sections:

- The first section simply lists the months and actual customer data.
- The second section is the 12 Period Moving Average Forecast with associated error percentages.
- The third section details the Simple Exponential Smoothing Forecast results with their errors.
- The final section provides the Seasonal Average Forecast values for select months.

This dataset allows users to compare different forecasting methods and understand the error associated with each technique, aiding in selecting the most reliable forecasting method.

Definition Definition Measure of central tendency that is the average of a given data set. The mean value is evaluated as the quotient of the sum of all observations by the sample size. The mean, in contrast to a median, is affected by extreme values. Very large or very small values can distract the mean from the center of the data. Arithmetic mean: The most common type of mean is the arithmetic mean. It is evaluated using the formula: μ = 1 N ∑ i = 1 N x i Other types of means are the geometric mean, logarithmic mean, and harmonic mean. Geometric mean: The nth root of the product of n observations from a data set is defined as the geometric mean of the set: G = x 1 x 2 ... x n n Logarithmic mean: The difference of the natural logarithms of the two numbers, divided by the difference between the numbers is the logarithmic mean of the two numbers. The logarithmic mean is used particularly in heat transfer and mass transfer. ln x 2 − ln x 1 x 2 − x 1 Harmonic mean: The inverse of the arithmetic mean of the inverses of all the numbers in a data set is the harmonic mean of the data. 1 1 x 1 + 1 x 2 + ...

Expert Solution

Step 1: Write the given information.

Months	Costumers
1	103
2	113
3	123
4	125
5	149
6	144
7	151
8	165
9	178
10	192
11	202
12	200
13	110
14	114
15	116
16	134
17	132

Step by step

Solved in 8 steps with 8 images

SEE SOLUTION Check out a sample Q&A here