In: Operations Management
A manufacturing area needs to forecast workhours. This is a slightly different project. The data for several months is supplied below. Beside each of the actual numbers in black, the forecast is provided in red. The goal is to calculate three stats on the data: MAD, MAPE, and the tracking signal. Be careful since the data is listed beginning with the most recent. Please round to two decimal places. For MAPE, please convert to a percentage before rounding. Do not enter the percent sign.
Jul 2020: 1693, 1591 | Jun 2020: 1408, 1281 | May 2020: 1945, 2237 | Apr 2020: 1197, 1125 | Mar 2020: 1985, 2084 | Feb 2020: 1584, 1378 |
Jan 2020: 1117, 1050 | Dec 2019: 1660, 1743 | Nov 2019: 1113, 968 | Oct 2019: 1127, 958 | Sep 2019: 1075, 1204 | Aug 2019: 1253, 1103 |
Jul 2019: 1633, 1437 | Jun 2019: 1552, 1443 | May 2019: 1689, 1486 | Apr 2019: 1775, 1988 | Mar 2019: 1523, 1355 | Feb 2019: 1634, 1879 |
Jan 2019: 1675, 1910 | Dec 2018: 1488, 1637 | Nov 2018: 1399, 1581 | Oct 2018: 1071, 975 | Sep 2018: 1721, 1807 | Aug 2018: 1389, 1195 |
Jul 2018: 1656, 1457 | Jun 2018: 1342, 1409 | May 2018: 1022, 910 | Apr 2018: 1599, 1391 | Mar 2018: 1558, 1636 | Feb 2018: 1440, 1310 |
Jan 2018: 1903, 2169 | Dec 2017: 1637, 1833 | Nov 2017: 1052, 999 | Oct 2017: 1092, 1256 | Sep 2017: 1955, 1740 | Aug 2017: 1623, 1866 |
Jul 2017: 1682, 1917 |
Please enter MAD here---> | |
Please enter MAPE % here---> | |
Please enter the tracking signal here---> |
Question: A manufacturing area needs to forecast workhours. This is a slightly different project. The data for several months is supplied below. Beside each of the actual numbers in black, the forecast is provided in red. The goal is to calculate three stats on the data: MAD, MAPE, and the tracking signal. Be careful since the data is listed beginning with the most recent. Please round to two decimal places. For MAPE, please convert to a percentage before rounding. Do not enter the percent sign.
Answer: (Make sure to recheck the supplied data with my calculations. Here at my end i couldn't differentiate actual and forecast value colors. And intermediate values are not rounded, only output values are rounded to two decimals. For your convenience i have also provided excel formulas used, refer accordingly)
MAD = 159
MAPE % = 10.60
Tracking Signal = -0.26
Explanation:
Overall Calculation:
Excel Formulas Used:
Formulas:
Mean Absolute Deviation (MAD) = |Error| / n
Here: |Error| = |Actual - Forecast|
Mean Absolute Percent Error (MAPE) = (|Error| / Actual) x 100
Running Sum of Forecast Error (RSFE) = Error
Tracking Signal (TS) = (RSFE / MAD)