Part V-Workers for Fulfillment Center Rebecca Anderson is running a zamaton com fulfiliment center, She has 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 thing: 6 - Stow items into shelves from bowes that are received into the warehouse - Stow items into shelves that are individual units (from pallets) - Pick items that are queued up trom customer orders You are assbting Rebecca. You have historical data from 2013. Look at files: ZamazonHistoricalData.csv, ZamanonHolidays.csv - Date: What day of the year was it - NumpackaresinQueueTostow: How many boxes are in recening that need to be stowed into - Stow litems into shelves from boues 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 are assisting Rebecea. You have historical data from 2013. Look at flles: ZamatonHlistoricalData.csv, ZamaronHolldays.csv - Date: What day of the year was it - NumPackareanQueue Fostow: How many boxes are in receiving that need to be stowed into shelves - NumLoneitemsimqueueToStow How many loose item (from pallets) are there to stow into shelves - NumitemsTopisk: How many inems are there waiting to be picked from shelves for customet orders (to be later mailed to the customers' homes) - Irucksisheduled How many trucks are scheduled on the inbound and outhound loodiry docks todar - Workessiededi On that day, how many workers were required to do the work that had to be done Using fxcel, 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. Specifically, do the following: Create 6 additional columns, one for Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday inot Sunday). Put a " 1 " in this column if the day of the year corresponds to that day of the week, and a zero otherwise. In this way, the "Monday" column will have a " 1 " in each row that is a Monday. This is a dummy variable for day of wee (and so on). We don't need "Sundar' because we are measuring the effect of Mon-5at against Sunday. Create a column for "Holiday" Look at the list of holidars. Put a "1" for each day that is a holiday, and a - NumLooseltemsinQueueTostow - NumitemsToPick - TrucksScheduled Look at your coefficients. Which coefficients have p-values >0.05 ? Aemove 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" (with all x-variables) and "Regression2" (with significant x-variables). Round decimals to reasonable numbers. B) For the second regression model, help Rebecca to interpres the model and coefficients for your client who isn't very numbers savry. Feel free to be a little detailed. What variables did you exclude? is your model a good one? insert these comments as text boxes on your Regression? 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.8., "Demand = 543-8*Price") D) How many workers does Rebecca 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? otherwise. In this way, the "Monday" column will have a " 1 " in each row that is a Monday. This is a dummy variable for day of wee (and so on). We don't need "Sunday" because we are measuring the effect of Mon-Sat against Sunday. Create a column for "Holiday". Look at the list of holidays. Put a " 1 " for each day that is a holiday, and a "0 " in the rest of the rows. Make sure everything you think is a predictor (X-variable) is together. You may have to move columns around. (When you do regression, Excel cannot highlight non-contiguous cells). Perform a regression of "Total Workers" on all of your input (Independent, or X) variables (11 in total): - Mon(0/1) - Tue (0/1) - Wed (0/1) - Thu (0/1) - Fri (0/1) - Sat(0/1) - Holiday (0/1) - NumPackagesinQueueToStow Part V-Workers for Fulfillment Center Rebecca Anderson is running a zamaton com fulfiliment center, She has 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 thing: 6 - Stow items into shelves from bowes that are received into the warehouse - Stow items into shelves that are individual units (from pallets) - Pick items that are queued up trom customer orders You are assbting Rebecca. You have historical data from 2013. Look at files: ZamazonHistoricalData.csv, ZamanonHolidays.csv - Date: What day of the year was it - NumpackaresinQueueTostow: How many boxes are in recening that need to be stowed into - Stow litems into shelves from boues 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 are assisting Rebecea. You have historical data from 2013. Look at flles: ZamatonHlistoricalData.csv, ZamaronHolldays.csv - Date: What day of the year was it - NumPackareanQueue Fostow: How many boxes are in receiving that need to be stowed into shelves - NumLoneitemsimqueueToStow How many loose item (from pallets) are there to stow into shelves - NumitemsTopisk: How many inems are there waiting to be picked from shelves for customet orders (to be later mailed to the customers' homes) - Irucksisheduled How many trucks are scheduled on the inbound and outhound loodiry docks todar - Workessiededi On that day, how many workers were required to do the work that had to be done Using fxcel, 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. Specifically, do the following: Create 6 additional columns, one for Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday inot Sunday). Put a " 1 " in this column if the day of the year corresponds to that day of the week, and a zero otherwise. In this way, the "Monday" column will have a " 1 " in each row that is a Monday. This is a dummy variable for day of wee (and so on). We don't need "Sundar' because we are measuring the effect of Mon-5at against Sunday. Create a column for "Holiday" Look at the list of holidars. Put a "1" for each day that is a holiday, and a - NumLooseltemsinQueueTostow - NumitemsToPick - TrucksScheduled Look at your coefficients. Which coefficients have p-values >0.05 ? Aemove 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" (with all x-variables) and "Regression2" (with significant x-variables). Round decimals to reasonable numbers. B) For the second regression model, help Rebecca to interpres the model and coefficients for your client who isn't very numbers savry. Feel free to be a little detailed. What variables did you exclude? is your model a good one? insert these comments as text boxes on your Regression? 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.8., "Demand = 543-8*Price") D) How many workers does Rebecca 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? otherwise. In this way, the "Monday" column will have a " 1 " in each row that is a Monday. This is a dummy variable for day of wee (and so on). We don't need "Sunday" because we are measuring the effect of Mon-Sat against Sunday. Create a column for "Holiday". Look at the list of holidays. Put a " 1 " for each day that is a holiday, and a "0 " in the rest of the rows. Make sure everything you think is a predictor (X-variable) is together. You may have to move columns around. (When you do regression, Excel cannot highlight non-contiguous cells). Perform a regression of "Total Workers" on all of your input (Independent, or X) variables (11 in total): - Mon(0/1) - Tue (0/1) - Wed (0/1) - Thu (0/1) - Fri (0/1) - Sat(0/1) - Holiday (0/1) - NumPackagesinQueueToStow