Test 2 Formula Sheet PDF

Preview

Summary

Formula sheet for test 2...

Description

QMB Formulae Test 2 Excel Formulas Decision Theory: 1. Expected Value for Decision Theory Step 1 =SUMPRODUCT(select probabilities*, select payoffs for 1st decision alternative) * put the probabilities in dollar signs (absolute cell reference) Step 2 Select cell from Step 1 & drag the formula down from 1st decision alternative all the way to last decision alternative 2. Expected Value without Perfect Information Step 1 Find Expected Values for all Decision Alternatives ***(See Formula #1 above)*** Step 2 =max(select all the Expected Values found in Step 1) 3. Expected Value with Perfect Information Step 1 Manually select the best payoff from each state of nature Step 2 =(best payoff from State of Nature 1)*(Probability from State of Nature 1) +(best payoff from State of Nature 2)*(Probability from State of Nature 2) +(best payoff from “nth” State of Nature)*(Probability from “nth” State of Nature) 4. Expected Value of Perfect Information Step 1 Find Expected Value without Perfect Information ***(See Formula #2 above)***** Step 2 Find Expected Value with Perfect Information ***(See Formula #3 above)***** Step 3 =Expected Value with Perfect information – Expected Value without Perfect Information Forecasting: 1. Simple Moving Average (SMA) Step 1 =AVERAGE(select “nth” number of periods from the actual observations column) Step 2 Select cell from Step 1 & drag the formula down from 1st possible forecast all the way down to the last possible forecast 2. Weighted Moving Average (WMA) Step 1 Create a column with the weights listed from the oldest period first down to the most recent last Step 2 =SUMPRODUCT(select column with weights*, select “nth” number of periods from the actual observations column) * put the weights in dollar signs (absolute cell reference) 3. Exponential Smoothing Method (EXP) Step 1 Insert alpha value in cell above the actual data column Step 2 In cell above forecasting column, type in the function: =1-(select cell from Step 1) Step 3 When being to do your forecasting, skip the forecast for the 1st period & make the actual value from the first period, the forecast value for the second period. Step 4 In the cell for the 3rd period of the forecast column, enter the following formula: =SUMPRODUCT(select cells from Step 1 & Step 2*, select 2nd period’s actual & forecast data) * put the values in dollar signs (absolute cell reference) 4. Mean Absolute Deviation/Error (MAD)/(MAE) & Mean Squared Error (MSE) MAD Step 1: Calculate the error for each possible period: =(actual value – forecasted value) Step 2: Take the absolute value of those errors: =ABS(select cells from Step 1) Step 3: Take the average of those absolute valued errors: =AVERAGE(select cells from Step 2) Step 1: Calculate the error for each possible period: =(actual value – forecasted value) MSE Step 2: Square each of those errors: =(select cells from Step 1)^2 Step 3: Take the average of those squared errors: =AVERAGE(select cells from Step 2) Page 1 of 1...