An accountant at the firm Gober Anderson, Arthur believed that several traveling executives were submitting unusually high travel vouchers when they returned from business trips. Arthur took a sample of 300 vouchers submitted

Chapter 6 homework

1-      The number of bottles of water sold in a machine each day is recorded below:

a)       Using Excel, find the equation of linear trend line that fits the data using Excel Regression function. Make sure to find the residuals output.

b)      Use the trend line to make forecasts for periods 25,26, and 27.

c)       Observe the value of R-squared and interpret.

d)      Using the values of residuals, find MAD.

2-      An accountant at the firm Gober Anderson, Arthur believed that several traveling executives were submitting unusually high travel vouchers when they returned from business trips. Arthur took a sample of 300 vouchers submitted from the past year. Then he developed the following multiple regression equation relating expected travel cost (Cost) to number of days on the road (Days) and distance traveled (Distance) in miles:

Cost = $110.00 + $52.30 Days + $0.55 Distance

The coefficient of correlation computed was 0.74.

A)      If Ken Lay returns from a 350-mile trip that took him out of town for 7 days, what is the expected amount he should claim as expenses (use the regression equation to predict this value)?

B)      Ken submitted a reimbursement request for $1012. Based on model above, is this amount reasonable? Explain.

3-      Consider the following data on demand (in 10,000) of bars of soap. The independent variables are Time Period (PERIOD), Price, Average Industry Price (AIP), and Advertising (in $1000). We are interested in building different models to forecast demand.

a.       Using Excel, construct the correlation matrix and interpret (relation of all variables with demand). Rank variables based on degree of absolute values of correlation with Demand.

b.       Using Excel linear regression analysis, find the Trend line to predict demand based on Time Period. Observe R-squared value and Significant F and interpret.

c.       Construct multiple linear regression model using all independent variables to predict Demand (Y). Provide the equation and interpretation of R-squared, Significant-F.

d.       Based on P-values of independent variables, rank the variables based on degree of contribution to the model. Hint: The lower the P-value, the higher the significance of a given variable.

e.       Use the equation in part (c) to forecast demand for March 2026 if Price = $3.90, AIP = $4.25, and ADV= $7.3.

Copy and paste the data below in an Excel file for analysis.

Chapter 7 homework

1-      Using the data in following table,

a)       Compute a 3-month moving forecast of Sales from April through December and for the next month, January. Compute the MAD for the forecast.

b)      Compute a 6-month moving forecast of Sales from July through December and for the next month, January. Compute the MAD for the forecast.

c)       Compute a weighted 3-month moving forecast of Sales from April through December and for the next month, January, using weights of 0.50 (most recent data), 0.30, and 0.20 (most distant data). Compute the MAD for the forecast.

D)      Compare the forecasts you computed by using moving forecast model from part a, b, and c. Which forecasting model does a better job?

2-      Using the data from problem 1,

A)      Compute an exponentially smoothed forecasts with alpha= 0.80 through December and create a forecast for the next month, January. Use initial forecast of 900 for January.  Find MAD.

B)      Compute an exponentially smoothed forecasts with alpha= 0.60 through December and create a forecast for the next month, January. Use initial forecast of 900 for January. Find MAD.

C)      Compare the forecasts you computed by using an exponential smoothing model from part a and b. Which forecasting model does a better job?

3-      Quarterly gas usage in MCF is shown below.

A)      What are the seasonal indices for the four seasons?

B)      Find the de-seasonalized values of the above gas usages. Hint: need to divide values by their respective seasonal indices.

4-      Passenger miles, in thousands flown, on Commuter Odessa Express Airlines, a commuter firm serving the Midland TX, are shown for the past 12 weeks.

A)      Assuming forecast value of 16 for week 1, use Excel to find exponential forecasts miles for weeks 2 through 13 using alpha = 0.6. Calculate MAD for this model.

B)      Redo part (a) using alpha = 0.90.

C)      Which forecasting model is better? Why?