The file “Unit 1 Review Project Template.xlsx” contains a handful of data sets on separate worksheets; use these data sets while you complete the problems below. For each topic, you will see a brief introduction to the problem and then a list of instructions, labeled in italics e.g. Moving Average Part 1.
Note that ALL of your work should lie inside of the yellow-shaded cells. Also, make sure you read the problem thoroughly and complete all of the instructions for each topic before moving on to the next worksheet. Lastly, be aware that hard-coding (typing numbers directly into cells or formulas) will result in ZERO credit for the question. To incentivize you in this regard, the data sets are tied to random-number generation functions in Excel, so they will constantly update. If you try to solve the problem by hand, the problem will change as you enter the answer, and you will find your answer is then incorrect.
There are three tables on the “Moving Average” worksheet for you to use while computing Naïve, Moving Average, and Weighted Moving Average Forecasts.
Moving Average Part 1 Complete the yellow-shaded cells in column D to perform a Naïve Forecast. Remember, don’t just type the values from columns C into the formulas, use references or else your formulas won’t work when the data values update!
Moving Average Part 2 Complete the yellow-shaded cells in column H, rows 7 through 13, to calculate a three-period moving average. Note that H4:H6 are grayed out, so you shouldn’t try to fill in anything in those cells.
Moving Average Part 3 Complete the yellow-shaded cells in column H, rows 20 through 26, to calculate a three-period weighted moving average, using the weights in D17:D19. I strongly recommend using the SUMPRODUCT() function to perform the multiplication and subsequent addition. Use the largest weight (D19) for the most-recent period and the smallest weight (D17) for the least-recent period used in the forecast. Again, there are grayed-out cells that you should not complete; only enter formulas into the yellow-shaded cells.
Use the data on the “Exponential Smoothing” worksheet to compute an Exponential Smoothing forecast for the various smoothing constants (alpha values) specified in D3:H3. As always, skip the grayed-out cells, and your formulas should work for any set of data that appears.
Hint: this section is made considerably easier through the use of partially-absolute cell references. If you are unfamiliar with absolute cell references, check out this tutorial on relative references. The video is a little under 4 minutes long (the runtime is 5:24, but the last 1:30 is off topic) and does a good job at succinctly covering what you need to know. In this case, if you row-lock (the row doesn’t change) the smoothing constant and column-lock (the column doesn’t change) the demand, you can use the fill handle to copy the formula from D5 to the rest of the yellow-filled cells.
I’ve gone ahead and provided a line chart that illustrates the different forecasts generated by each smoothing constants. This is already complete, so you don’t need to do anything with it.
Measures of Error
The “Exponential Smoothing” worksheet contains three tables for you to use while computing the Mean Absolute Deviation (MAD), Mean Squared Error (MSE), and Mean Absolute Percent Error (MAPE) respectively. There aren’t any tricks here, but be sure to pay attention to the column labels so you don’t skip steps.
Exponential Smoothing with Trend
The “Exponential Smoothing w Trend” worksheet contains the necessary information to compute the Forecast Including Trend, FITt. As usual, do not alter cells that have a gray background, and constrain your solutions to the yellow-filled cells.
Least Squares Projections
The “Least Squares” worksheet contains the necessary information to follow the Least Squares procedure for computing Trend Projections. As usual, do not alter cells that have a gray background, and constrain your solutions to the yellow-filled cells.
As an aside, there is a much easier way to compute regression equations – actually, there are two easier ways, but I strongly prefer one of them – but those will be covered in MNGT 379 – Business Analytics, so we are going to stick with doing things manually. That is, explicitly defining what Excel does to compute the values of the regression equation.
The “Seasonality” worksheet contains the necessary information to determine appropriate seasonality indices and create seasonal forecasts for both regular and Trend-Adjusted data. Parts 1-4 direct you to compute the regular (non-trend-adjusted) forecast while Parts 5 and 6 provide the instructions to compute the trend-adjusted version.
Seasonality Part 1 Start by using the AVERAGE() function to compute the average demand for each month, entering the result in column G.
Seasonality Part 2 Next, compute the average demand per month across all thirty-six (36) months in the data set and enter the result into each cell in column H (every cell in the range H6:H17 should contain the same value). This can easily be computed by either applying the AVERAGE() function to all thirty-six (36) months of the original data set or to the twelve (12) Average Demands you computed in Seasonality Part 1.
Seasonality Part 3 After that, use the previous two results to compute the Seasonal Index for each month. Enter this result into column I.
Seasonality Part 4 Combine the result from Seasonality Part 3 and the Estimated Demand in Year 4 (this number is already divided by 12 to provide a per-month rate) to compute a seasonally-adjusted forecast for each month, and enter the results in column K.
Seasonality Part 5 From there, use the values for a and b provided in cells M18:M19 to compute a trend-adjusted forecast for each month of year 4, and enter the results in column M. Note: three years will have elapsed before Month 1 begins in year 4, so Month 1 be considered month 37 for the purposes of the trend projection.
Seasonality Part 6 Finally, use your previous results in columns I and M to compute a Trend-Adjusted Seasonal Forecast for year 4, and put the results into Column N.
The “Tracking Signals” worksheet contains the necessary information to calculate tracking signals for ten periods’ worth of data. The provided template follows the same layout and process as from slide 53 in the lecture slides, so refer to that if you need guidance in filling out this table. Of course, pay close attention to the column labels as they outline the process to follow to compute the tracking signals.
Plagiarism Free Papers
All our papers are original and written from scratch. We will email you a plagiarism report alongside your completed paper once done.
All papers are submitted ahead of time. We do this to allow you time to point out any area you would need revision on, and help you for free.
A title page preceeds all your paper content. Here, you put all your personal information and this we give out for free.
Without a reference/bibliography page, any academic paper is incomplete and doesnt qualify for grading. We also offer this for free.
Originality & Security
At Essay Assign, we take confidentiality seriously and all your personal information is stored safely and do not share it with third parties for any reasons whatsoever. Our work is original and we send plagiarism reports alongside every paper.
24/7 Customer Support
Our agents are online 24/7. Feel free to contact us through email or talk to our live agents.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
We work around the clock to see best customer experience.
Our prices are pocket friendly and you can do partial payments. When that is not enough, we have a free enquiry service.
Admission help & Client-Writer Contact
When you need to elaborate something further to your writer, we provide that button.
We take deadlines seriously and our papers are submitted ahead of time. We are happy to assist you in case of any adjustments needed.
Your feedback, good or bad is of great concern to us and we take it very seriously. We are, therefore, constantly adjusting our policies to ensure best customer/writer experience.