Question

How to calculate in excel the following Please show formula - Calculate (a) MAD, (b) MSE, and (c) MAPE for the following forecast versus actual sales figures. (Please round to four decimal places for MAPE.) Forecast Actual 100 95 110 108 120 123 130 130

Answer 1

Step by step procedure :

1. Copy paste forecast actuals in to excel sheet

2.Find Mean and Median for the actuals using formula

MEan formula : =AVERAGE(A2:A9)

Median Formula : =MEDIAN(A2:A9)

3. Find Mean deviation next to forecast actual value with formula

=Abs(Forecast Actuals - Mean value)

4. NExt to Mean Deviation add column as MSE

MSE is = (Forecast value - Mean value) ^ 2

5. NExt column would be MAPE

MAPE formula is Mean deviation / Forecast Actuals

average of all the columns are the real value of MAD,MSE and MAPE