Future Trends Forecast via Excel Regression
Budget: $10 – $30 USD
Part I: Workers for Fulfillment Center
You are running a Zamazon.com fulfillment center. You have to decide every day how many temp
workers to hire in order to complete the tasks that need to be done. These workers will do three things:
Stow items into shelves from boxes that are received into the warehouse
Stow items into shelves that are individual units (from pallets)
Pick items that are queued up from customer orders
You have historical data from 2013.
Look at files: ZamazonHistoricalData, ZamazonHolidays
Date: What day of the year was it
NumPackagesInQueueToStow: How many boxes are in receiving that need to be stowed into
shelves
NumLooseItemsInQueueToStow: How many loose items (from pallets) are there to stow into
shelves
NumItemsToPick: How many items are there waiting to be picked from shelves for customer
orders (to be later mailed to the customers’ homes)
TrucksScheduled: How many trucks are scheduled on the inbound and outbound loading docks
today
WorkersNeeded: On that day, how many workers were required to do the work that had to be
done
Create a regression model that describes the number of workers required as a function of the above
inputs as well as holiday and day of week.
Hints:
First you need to find what day of the week is each of the date given. Google to see what excel
function could do this.
Next, you may think of using dummy (categorical) variables to indicate whether a specific date is
a Monday, Tuesday, Wednesday, Thursday, Friday, or Saturday, using Sunday as the benchmark.
How many dummy variables do you need? (remember Sunday is the benchmark). For example,
put a “1” in the Monday column if the date corresponds to a Monday.
Similarly, create a column for “Holiday” as another dummy variable. Look at the list of holidays
which tells you which date was a holiday of that year.
Make sure all predictor (X-variable) variables are together (side by side) because excel only take
X variables that appear in a block (side by side). You may have to move columns around to
make that happen.
Perform a regression of “Total Workers” on all of your input (Independent, or X) variables (you should
have 11 in total):
Look at your coefficients. Are there coefficients that are not significant (having a p-values > 0.05)?
Remove all these from your model and rerun the regression. (Hint: To validate your model, try to use
the coefficients to predict the number of workers. On January 1, 2014, if there are 2000 Packages to be
stowed, 30000 items to be stowed, and 35000 items to be picked, and 10 trucks scheduled, then you
should get 339.05 workers predicted: Remember 1/1/2014 is in 2014 and it’s a holiday).
Questions to answer:
A) Label your worksheets containing your regression analysis as “Regression1” and “Regression2”,
corresponding to the two regression you had run. Round decimals to reasonable numbers.
B) For the second regression model, interpret the model and coefficients for your client who isn’t very
numbers savvy. Be clear but concise. What variables did you exclude? Is your model a good one?
Insert these comments as text boxes on your Regression2 worksheet.
C) For the second regression model, write the equation that predicts number of workers as a function
of the inputs to your model. (e.g., “Demand = 543 – 8*Price”)
D) How many workers do you need on 5/10/2014 if there are 1500 packages to be stowed, 20000
items to be stowed, 40000 items to be picked, and 6 trucks scheduled? How much do you expect to
be “off” from this prediction?
E) Create a new worksheet called “predictions.” For each of the 365 data points, calculate the
predicted value of “NumWorkers” (Based on your coefficients and model and the input data for
each day of the year). Then, calculate the residuals (Actual – Predicted) for each data point.
1. Make a histogram of the 365 residuals. What does this look like?
2. What is the standard deviation of the residuals (STDEV.P())? Is this close to your
standard error?
F) Add “TrucksScheduled” back to your model. What are the old and new R-squared values? What are
the old and new adj-R-squared values? Can you explain why adding TrucksScheduled would have
this effect?
You are running a Zamazon.com fulfillment center. You have to decide every day how many temp
workers to hire in order to complete the tasks that need to be done. These workers will do three things:
Stow items into shelves from boxes that are received into the warehouse
Stow items into shelves that are individual units (from pallets)
Pick items that are queued up from customer orders
You have historical data from 2013.
Look at files: ZamazonHistoricalData, ZamazonHolidays
Date: What day of the year was it
NumPackagesInQueueToStow: How many boxes are in receiving that need to be stowed into
shelves
NumLooseItemsInQueueToStow: How many loose items (from pallets) are there to stow into
shelves
NumItemsToPick: How many items are there waiting to be picked from shelves for customer
orders (to be later mailed to the customers’ homes)
TrucksScheduled: How many trucks are scheduled on the inbound and outbound loading docks
today
WorkersNeeded: On that day, how many workers were required to do the work that had to be
done
Create a regression model that describes the number of workers required as a function of the above
inputs as well as holiday and day of week.
Hints:
First you need to find what day of the week is each of the date given. Google to see what excel
function could do this.
Next, you may think of using dummy (categorical) variables to indicate whether a specific date is
a Monday, Tuesday, Wednesday, Thursday, Friday, or Saturday, using Sunday as the benchmark.
How many dummy variables do you need? (remember Sunday is the benchmark). For example,
put a “1” in the Monday column if the date corresponds to a Monday.
Similarly, create a column for “Holiday” as another dummy variable. Look at the list of holidays
which tells you which date was a holiday of that year.
Make sure all predictor (X-variable) variables are together (side by side) because excel only take
X variables that appear in a block (side by side). You may have to move columns around to
make that happen.
Perform a regression of “Total Workers” on all of your input (Independent, or X) variables (you should
have 11 in total):
Look at your coefficients. Are there coefficients that are not significant (having a p-values > 0.05)?
Remove all these from your model and rerun the regression. (Hint: To validate your model, try to use
the coefficients to predict the number of workers. On January 1, 2014, if there are 2000 Packages to be
stowed, 30000 items to be stowed, and 35000 items to be picked, and 10 trucks scheduled, then you
should get 339.05 workers predicted: Remember 1/1/2014 is in 2014 and it’s a holiday).
Questions to answer:
A) Label your worksheets containing your regression analysis as “Regression1” and “Regression2”,
corresponding to the two regression you had run. Round decimals to reasonable numbers.
B) For the second regression model, interpret the model and coefficients for your client who isn’t very
numbers savvy. Be clear but concise. What variables did you exclude? Is your model a good one?
Insert these comments as text boxes on your Regression2 worksheet.
C) For the second regression model, write the equation that predicts number of workers as a function
of the inputs to your model. (e.g., “Demand = 543 – 8*Price”)
D) How many workers do you need on 5/10/2014 if there are 1500 packages to be stowed, 20000
items to be stowed, 40000 items to be picked, and 6 trucks scheduled? How much do you expect to
be “off” from this prediction?
E) Create a new worksheet called “predictions.” For each of the 365 data points, calculate the
predicted value of “NumWorkers” (Based on your coefficients and model and the input data for
each day of the year). Then, calculate the residuals (Actual – Predicted) for each data point.
1. Make a histogram of the 365 residuals. What does this look like?
2. What is the standard deviation of the residuals (STDEV.P())? Is this close to your
standard error?
F) Add “TrucksScheduled” back to your model. What are the old and new R-squared values? What are
the old and new adj-R-squared values? Can you explain why adding TrucksScheduled would have
this effect?