Labs1-3

pdf

School

University of Alberta *

*We aren’t endorsed by this school

Course

352

Subject

Statistics

Date

Apr 3, 2024

Type

pdf

Pages

33

Uploaded by ColonelSteel13693

Report
OM 352: Operations Management University of Alberta Lab 1 | Intro to OM 352 and to Modelling Agenda Lab Information Navigating the course web on eClass Discussion Board Other Tools Dual Screens Fisheries Management Lab Information Am I required to attend? Although attendance is not mandatory, it is recommended that you attend all the labs in order to ensure that you are able to take concepts from the lectures and apply them yourself. We are here to help you succeed so do not hesitate to ask if you need a little extra help with a technique or a concept. Do I have to attend my scheduled lab? We recommend that you attend your scheduled lab. If you are unable to do so on a certain day, and attend another section, then note that those registered in that lab section have priority. If all the computers in your scheduled lab are full, please mention this to a staff member rather than just leaving, so we can make arrangements. What should I do if I fall behind in a lab? Raise your hand and a TA will come by to help you as soon as they are free. Alternatively, if a TA is not immediately available, each Excel workbook that we use has a completed version in a separate worksheet. You should be able to get formulas and other information from this sheet. Navigating the course web on eClass The course web page is your main resource outside of the classroom. It contains many vital links that will enhance your learning experience.
OM 352: Operations Management Lab 1: Introduction to OM 352 and to Modelling 2 Link Content Announcements Updated information with regards to the course. Course Pack Downloadable package to supplement lecture material. Lectures Downloadable material of the slides and workbooks presented in the Lectures. Labs Downloadable material of the workbooks presented in Friday's labs. Supplemental instructional videos are provided for labs 1-3. Resources Frequently Asked Questions about the course, software installation instructions, and using Solver in different versions of Excel. Assignments Homeworks and their corresponding deadlines and weightings. Information about late/missed assignment and appeal procedures. Exams & Quizzes Sample quizzes and exams will be posted before each test. Discussion Board The course discussion board for when you have questions and/or suggestions. A discussion board topic will be created for each homework – check there before emailing us as your question may have already been answered. Discussion Board The discussion board is a web-based on-line discussion area for students and faculty. We will have separate discussion boards for each homework assignment and exam (including
OM 352: Operations Management Lab 1: Introduction to OM 352 and to Modelling 3 the final), and general issues and topics in the course. You are encouraged to read and post to these forums. Although Armann, Sam, Matt, and all the TAs will be checking it regularly and responding to questions and concerns, it is primarily a mechanism for sharing concerns and insights between students in the class. If you have a question or concern that is more private, email us. If you have a question, please make sure that it has not already been answered before asking the question yourself. Often other students have the same question as you and have already received guidance for the issue. Furthermore, it will help to keep the discussion boards organized which will be of benefit to everyone when they are looking for certain information. If people are continually posting questions that have already been answered, these will be ignored, or the poster will be directed to the thread with the answer. Other Tools Always select ' Save ' and not ' Open ' to save files. This applies to all Excel, PowerPoint, and Word files. Dual Screens All business labs have dual monitors. This is particularly useful for OM classes - you can view instructions while working on a problem or have one spreadsheet open on each monitor. You may not want to employ both monitors and can ignore one of them depending on your preferences. In order to get a window from one screen to the other, you will likely have to reduce the window size by clicking on the middle button in the top right-hand corner of that window. Once it is reduced in size you can click and drag the window over to the other monitor. At this point you can maximize the window again to have both monitors in use. The dual screens are also helpful when you have a model with many columns. After reducing the size of the window, hover over a corner until you see the cursor change. Then click and drag the window all the way over to the other screen.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 1: Introduction to OM 352 and to Modelling 4 Fisheries Management In Year 0 the lake is populated with 1,000 trout in April. The fish population expands in May-July and the fish can then be harvested in September. The growth function is defined as P Aug = P Apr + a × (1 – P Apr / Cap) × P Apr Each fish that is harvested is sold for $10, and the discount rate is 5%. We must determine how many fish to harvest in each year to maximize NPV. Key Concepts Data Starting population? Capacity? Growth Parameter (a)? Discount rate? Price? Variables? Output? Setting up the Model Input the growth formula starting in cell C3 and propagate down: The population in following years is the population in August of the previous year, less the fish that were harvested in September of the previous year. Enter this formula starting in cell B4 and propagate down:
OM 352: Operations Management Lab 1: Introduction to OM 352 and to Modelling 5 In column E we want to find the per unit present value for years 0-30. Using a price per fish of $10 and a discount rate of 5%, the present value of $10 in each year is 10/(1+i)^t where t is the time period in years and i is the discount rate. Input this formula into cell E3 and propagate down. The Net Present Value (NPV) is the SUMPRODUCT of the amount of fish to harvest in each year (the decision cells) and the present value of the price per fish for each year. Next, we want to graph the August fish population. Click the Insert tab, then select the August population values in column C. Then click the line button under charts and select the first 2D line graph.
OM 352: Operations Management Lab 1: Introduction to OM 352 and to Modelling 6 While your chart is selected use Chart Design and Format to make changes to your chart such as removing the title, labeling axes, or removing the frame around the graph. To correct the values of the x axis so that they correspond with the years, right click the graph and click “Select Data”. Then click the “Edit” button under “Horizontal (Category) Axis Labels” and select the years and hit OK. You should then see 0, 1, 2, …. instead of 1, 2, 3, …. on the x axis.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 1: Introduction to OM 352 and to Modelling 7 Questions What is the NPV if we harvest 300 fish every year? If we harvest 300 fish every year, does this create a stable population within 30 years? What is significant about harvesting 495 fish every year?
OM 352: Operations Management University of Alberta Lab 2 | Forecasting Part I Agenda Forecasting using the simple methods Simple Exponential Smoothing (SES) Double Exponential Smoothing (DES) Error Calculations Performance Measures Forecasting Using the Simple Methods The purpose of this lab is to review the forecasting methods covered in class. Scenario Your Uncle Larry gave you a hot stock tip about Google Inc. while he was at your house drinking rum and eggnog at a Christmas party in 2010. Excited, you immediately invested your Christmas bonus and life savings into the stock right after the New Year. By the middle of June, you had lost over $100 per share, and later that same month Uncle Larry's body was mysteriously found floating in the North Saskatchewan River. Now that you are enrolled in OM 352, you decide to use what you are learning about forecasting to predict the Google stock price and see if you should sell your shares. Since your future at university depends on your decision, you decide you had better pay attention to your genius instructors. In order to complete this lab you will need to use the data on the “Data” worksheet, which contains the daily closing prices of Google stock from January 3, 2011, through August 31, 2011. Given the data, how would you forecast the closing price of the stock after September 1, 2011? Last Point F t+1 = D t ; Forecasts in the prediction stage are based on your last forecast made using the data, i.e., the last data point:
OM 352: Operations Management Lab 2: Forecasting 2 Average F t+1 = average(D 1 , D 2 , ... , D t ) Once you run out of data points, the forecast in the prediction stage is based on the last forecast you made using the data. Note that you can still use the above formula on September 1 (one day after your last data point). Open the formula for the September 1 forecast to see why. Simple Moving Average (SMA) F t+1 = average(D t-m+1 , D t-m+2 , ... , D t ), where m = "window", i.e., the number of points we will include in our forecasts. Suppose m = 4: Note that you cannot make a prediction until period m+1, because you need m periods of data first. In this case, we will start forecasting in period 5. Similar to the average method, once you run out of data points, the forecast in the prediction stage is based on the last forecast you made using the data. Note that you can still use the above formula on September 1 (one day after your last data point) because you still have four data points:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 2: Forecasting Part I 3 Weighted Moving Average F t+1 = w 1 x D t-m+1 + w 2 x D t-m+2 + ... + w m x D t , where the weights satisfy w 1 + w 2 + ... + w m = 1 WMA always follows these three rules: • Weights are arranged in ascending order. • The largest weight corresponds to the most recent data (comes last). • The weights add up to 1 . Suppose m = 4, w 1 = 0.1, w 2 = 0.2, w 3 = 0.3, w 4 = 0.4: Similar to the last two averaging methods, once you run out of data points, the forecast in the prediction stage is based on the last forecast you made using the data. Note that you can still use the above formula on September 1 (one day after your last data point) because you still have four data points to use: Simple Exponential Smoothing (SES) There are three stages in all exponential smoothing methods: initialization, learning, and prediction. F 2 = D 1 , F t+1 = LS × D t + (1 – LS) × F t , where 0 ≤ LS ≤ 1. Suppose LS=0.25:
OM 352: Operations Management Lab 2: Forecasting 4 Initialization: Learning: Prediction: As with all the other methods covered so far, your prediction is the last point you were able to forecast using the data. Double Exponential Smoothing For this method, we need to forecast level and trend separately, then combine them. DES is the first forecasting method that takes trend into account. Forecast: F t+1 = L t + T t . Level: L t = LS × D t + (1 – LS) × (L t-1 + T t-1 ) Trend: T t = TS × (L t - L t-1 ) + (1 – TS) × T t-1 , where 0 ≤ LS and TS ≤ 1. Initialization: Level: Trend: Learning: Forecast:
OM 352: Operations Management Lab 2: Forecasting Part I 5 Level: Trend: Prediction: Which of all the above forecasting methods should you use to make your investment decision? You may want to compute some performance measures before answering this question. Error Calculations Definitions Error: the difference between the data and the forecast. Error = data – forecast. Absolute error: the absolute value of the error.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 2: Forecasting 6 Percentage error: the absolute value of the error divided by the actual data. Squared error: the error to the power of 2. How-To (using the Last Point method) Performance Measures NOTE: n = number of data points that are included in calculating the performance measure. You must be able to calculate forecast errors for all of these data points, which means that you must have both data and a forecast for each data point. Sometimes we use only a subset of the data to calculate a performance measure, for example because we want to focus on the more recent data.
OM 352: Operations Management Lab 2: Forecasting Part I 7 BIAS (Average Error) Compute the average of the errors.
OM 352: Operations Management Lab 2: Forecasting 8 MAD (Mean Absolute Deviation) Calculate the average of the absolute errors.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 2: Forecasting Part I 9 MAPE (Mean Absolute Percent Error) Calculate the average of the % errors.
OM 352: Operations Management Lab 2: Forecasting 10 MSE (Mean Squared Error): Calculate the sum of the squared errors and divide this by the total number of errors less one . A faster way to calculate the MSE (without having to set up the error, absolute error, % error, and squared error columns) is by using a single Excel formula. In this method, only the data points and forecasts are used.
OM 352: Operations Management Lab 2: Forecasting Part I 11
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 2: Forecasting 12 RMSE (Root Mean Squared Error): Calculate the square root of the MSE. TIP 1: Take a look at the other methods for calculating the performance measures. These can be found on the completed tabs. Feel free to use any method you're comfortable with. As is often the case in operations management, there are many ways to get the correct answer. TIP 2: If using a performance measure to compare different methods, then use the same set of time periods to compute the performance measure for both methods. For example, if you want to compare SES and DES, then you cannot include a forecast error for January 4th in your performance measure calculations--do you see why?
OM 352: Operations Management Dr. Armann Ingolfsson University of Alberta Lab 3 | Forecasting Part II Agenda Triple Exponential Smoothing (TES) Using Excel’s VLOOKUP function Installing Solver Solver’s multistart option Holdout analysis Triple Exponential Smoothing The data in this week’s Excel file represents the monthly sales of garden equipment taken from the US Census Bureau. Given such data, how would you compute forecasts several periods into the future? TES is for data that exhibits seasonality. Data is seasonal when it exhibits repeating patterns of peaks and troughs. The distance between two adjacent peaks or troughs is the length of your cycle. The best way to see this is by plotting your data on a graph. 0 5000 10000 15000 20000 25000 30000 35000 40000 Jan-2004 Jan-2005 Jan-2006 Jan-2007 Jan-2008 Jan-2009 Jan-2010 Jan-2011 Jan-2012 Jan-2013 Jan-2014 Jan-2015 Jan-2016 Sales
OM 352: Operations Management Lab 3: Forecasting — TES 2 As the graph shows, sales are consistently highest in June, and lowest in January. Hence, the distance between two adjacent peaks (June and June) is one year or 12 months. The length of our cycle (or p , the number of seasons), then, is 12 months. Initialization We will use the first p=12 (the number of seasons) periods of past data (D 1 , D 2 , ..., D 12 ) for initialization. Level Let the initial level (L) be the average of the first 12 periods. Trend T p = (D p+1 – D 1 ) / p.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 3 Seasonality S i = D i / L. Learning Recall that LS, TS, and SS are the smoothing constants for level, trend, and seasonality, respectively. Level L t = LS × (D t / S t-p ) + (1 – LS) × (L t-1 + T t-1 ).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 4 Trend T t = TS × (L t – L t-1 ) + (1 – TS) × T t-1 . Seasonality S t = SS × (D t / L t ) + (1 – SS) × S t-p .
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 5 Forecast At time t, the one-step forecast is F t+1 = (L t + T t ) × S t+1–p . Propagate all formulas down to row 157.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 6 Prediction Once we run out of levels, trends, and seasonalities to work with, we will make a “real” forecast. This is where our familiar forecasting rule kicks in: we assume that the latest set of parameters includes all historical information, and we will use them forever into the future. NOTE: A set of parameters in case of seasonality means the latest set of individual seasonality indices (here: 12). Now, a k-step forecast at time t is F t+k = (L t + k × T t ) × S t+k–p , for k ≤ p. Important to note is that we are referencing the last calculated seasonality index for the current month . In this case, we select the seasonality index for the previous January, since we are forecasting for January. Copy the formula in cell E158 down to row 171 in order to compute the forecasts for the next thirteen periods into the future. What would the formula be to forecast for 13 months in the future (k=13)? Can we just propagate the current formula? Another question: Could we use the VLOOKUP () function in Excel to help us out? Here is an alternative way to calculate the forecast more than p periods into the future:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 7 The arguments for VLOOKUP are =VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup]). • Lookup_value: The value you want to look up in the first row of the table – in our case, JAN. • Table_array: The table where we will look for our lookup value. In our case, we need to select the range of values for the last seasonality indices from January to December – one full cycle. We will reference these numbers continuously throughout our “real” forecasts. • Col_index_number: The ordinal number of the column in the table we want to look up. E.g., 1 would be for the first column in the table, 2 for the second number, etc. In our case we want to find the seasonality index that we calculated for each month . The seasonality indices in the table are in the seventh column, so we type in 7. • [Range_lookup]: Put in “FALSE” for an exact match, and “TRUE” for an approximate match, of the lookup_value. We are looking up JAN, FEB, etc., and we want to be sure we are pulling the correct month. Therefore, we put in FALSE so we pick the correct seasonality index from the table we selected. In effect, our VLOOKUP function is asking Excel to find our current month in the table containing our most recent seasonality indices, then go across and pull out the seasonality index. We can then use it in our prediction formula. If we set up the model this way, we will not have to worry about “resetting” our seasonality indices if we forecast more than 12 periods into the future.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 8 Performance Measures Recall that you can only compute an error if you have both an observation and a prediction. Please see Lab 2 for detailed error and performance measure computation. For the purposes of this lab, calculate the value of MSE . Now, let's find the values of LS, TS, and SS that minimize MSE. Solver Time! Using Solver First ensure that solver is installed on your computer. In Excel 2016, go to the Office button at the top left and click on Options.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 9 Click on Add-Ins (on the left toolbar on the Options menu) and where it says “Manage: Excel Add-ins, click Go. Make sure Solver Add-in is checked and press OK.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 10 Then you can continue with solver to optimize LS, TS, SS. Go to the Data tab and select Solver. Point “Set Cell” to the cell with your MSE. From now on, we’ll call this your objective cell . In the “By Changing Variable Cells” field, put the cells with your LS, TS, and SS ( hint: you can select a range of cells). From now on, we’ll call these your decision cells . Click “Add” next to “Subject to the Constraints” and add the following constraints: LS, TS, and SS ≥ 0.05; and LS, TS, and SS ≤ 0.95.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 11 Check “Make Unconstrained Variables Non-Negative”. Click on Options and check off “Use Automatic Scaling”.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 12 Hit OK to exit the options, and then hit Solve! NOTE: MAD, MAPE, MSE, and RMSE are all non-linear functions. Using Solver for the measures that involve the absolute value function (MAD and MAPE), does not guarantee finding the best solution. Moreover, different starting values for LS, SS, and TS might NOT provide the same ending values. Excel 2016’s Solver has several “Solving Methods” used to solve problems more efficiently. In our case, this is a non-linear model, so Solver defaulted to “GRG Nonlinear”. Multistart Option in Solver In order to increase the chance of finding the global optimum, we can ask the solver to try many different starting solutions. To do this, go to Options, GRG Nonlinear, and check “Use Multistart”.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 13 To create bounded constraints for decision variables, “Require Bounds on Variables” can be checked off. If this option is checked, then the solver constraints must include numerical lower and upper limits for every decision variable. This is automatically true for SES, DES, and TES, because we always include a lower bound like 0.01 or 0.05 and an upper bound like 0.99 or 0.95. Holdout Analysis Holdout analysis provides a more realistic measure of the performance of a forecasting method. Holdout analysis involves setting aside a portion of the data (the “holdout data”), using the remaining data (the
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OM 352: Operations Management Lab 3: Forecasting — TES 14 “training data”) to do the forecasting calculations, resulting in forecasts for every period in the holdout data. Then, we bring back the holdout data and use it to compute performance measures based only on the holdout period. Let’s see what happens if we use the data for the year 2016 as holdout data. Forecast performance versus withheld data:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

Priya - Statistics HW 2.2.docx
BIO259-Graphical-Data-Summaries_Tutorial.pdf
lab06 (1).pdf
hw04 (1).pdf
STA 220 MiniProject 2_Part 2_Group.docx
Bennett - Homework 5.docx
Statistical Analysis of Precision in Washer Drop Experiments.docx
THINK4.pdf
CHAPTER 9 REVIEW QUESTIONS.docx
BADM 520 FIB 5.docx
STAT 250Project V ANOVA.docx
Paper 1.docx
Recommended textbooks for you
Text book image
Big Ideas Math A Bridge To Success Algebra 1: Stu...
Algebra
ISBN:9781680331141
Author:HOUGHTON MIFFLIN HARCOURT
Publisher:Houghton Mifflin Harcourt
Text book image
Glencoe Algebra 1, Student Edition, 9780079039897...
Algebra
ISBN:9780079039897
Author:Carter
Publisher:McGraw Hill
Text book image
Algebra for College Students
Algebra
ISBN:9781285195780
Author:Jerome E. Kaufmann, Karen L. Schwitters
Publisher:Cengage Learning
Text book image
Intermediate Algebra
Algebra
ISBN:9781285195728
Author:Jerome E. Kaufmann, Karen L. Schwitters
Publisher:Cengage Learning
Text book image
Algebra: Structure And Method, Book 1
Algebra
ISBN:9780395977224
Author:Richard G. Brown, Mary P. Dolciani, Robert H. Sorgenfrey, William L. Cole
Publisher:McDougal Littell
Text book image
Elementary Geometry for College Students
Geometry
ISBN:9781285195698
Author:Daniel C. Alexander, Geralyn M. Koeberlein
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Big Ideas Math A Bridge To Success Algebra 1: Stu...
Algebra
ISBN:9781680331141
Author:HOUGHTON MIFFLIN HARCOURT
Publisher:Houghton Mifflin Harcourt
Text book image
Glencoe Algebra 1, Student Edition, 9780079039897...
Algebra
ISBN:9780079039897
Author:Carter
Publisher:McGraw Hill
Text book image
Algebra for College Students
Algebra
ISBN:9781285195780
Author:Jerome E. Kaufmann, Karen L. Schwitters
Publisher:Cengage Learning
Text book image
Intermediate Algebra
Algebra
ISBN:9781285195728
Author:Jerome E. Kaufmann, Karen L. Schwitters
Publisher:Cengage Learning
Text book image
Algebra: Structure And Method, Book 1
Algebra
ISBN:9780395977224
Author:Richard G. Brown, Mary P. Dolciani, Robert H. Sorgenfrey, William L. Cole
Publisher:McDougal Littell
Text book image
Elementary Geometry for College Students
Geometry
ISBN:9781285195698
Author:Daniel C. Alexander, Geralyn M. Koeberlein
Publisher:Cengage Learning
SEE MORE TEXTBOOKS