MNGT 377 – Production/Operations Management

Instructions

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.

Don't use plagiarized sources. Get Your Custom Essay on
MNGT 377 – Production/Operations Management
Just from $13/Page
Order Essay

 

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.

 

Moving Average

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.

 

 

Exponential Smoothing

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.

 

Seasonality

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.

 

Tracking Signals

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.

 

Essay Assign
Calculate your paper price
Pages (550 words)
Approximate price: -

Our Advantages

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.

Free Revisions

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.

Title-page

A title page preceeds all your paper content. Here, you put all your personal information and this we give out for free.

Bibliography

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!

Calculate the price of your order

We'll send you the first draft for approval by at
Total price:
$0.00

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.

Our Services

We work around the clock to see best customer experience.

Pricing

Flexible Pricing

Our prices are pocket friendly and you can do partial payments. When that is not enough, we have a free enquiry service.

Communication

Admission help & Client-Writer Contact

When you need to elaborate something further to your writer, we provide that button.

Deadlines

Paper Submission

We take deadlines seriously and our papers are submitted ahead of time. We are happy to assist you in case of any adjustments needed.

Reviews

Customer Feedback

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.