Please help with the attached spreadsheet.

Thank you.

WFP Assessment Objective: To assess your technical and business skills. Hint - take your time and explain your decision making. It is as much about how you answer as it is what you answer. Be creative and think about each scenario Question 1 You are the Workforce Planner for store 1234 and your sales goal for the next week of business is $150,000. You are no sales in order to forecast goals by day for the week. You are given the following 4 years of sales history broken down by historical week (2010 - 2014). Note all observations in the box below about the data and how useful it may/may not be in forecasting goals for the we WEEKLY DISTRIBUTION % Week Begin Mon Tue Wed Thu Fri Sat Feb 10, 2014 Feb 11, 2013 Feb 13, 2012 Feb 14, 2011 16.2% 16.7% 11.9% 11.5% 11.8% 10.8% 12.0% 10.6% 12.7% 12.5% 11.9% 12.7% 13.5% 10.9% 15.9% 13.7% 14.4% 16.7% 17.6% 19.9% 18.3% 20.4% 18.5% 20.4% 10/17/2017 WFP Assessment 1 of 12 Question 2 Using a formula, calculate a sales forecast in $ and % for the store by day from the $150,000 weekly goal based on the h Goal $ % Monday Tuesday Wednesday Thursday Friday Saturday Sunday Question 2 Using a formula, calculate a sales forecast in $ and % for the store by day from the $150,000 weekly go 4. You were given the following 5 years of sales history broken down by hour for Monday in the same week as the scena a) Using a formula calculate an hourly goal breakdown % that you would recommend be used (in cells P60:P73). b) Note all observations about the data & how useful it may/may not be in forecasting goals for the week ahead. 10/17/2017 WFP Assessment 2 of 12 Hour Begin 10:00 AM 11:00 AM 12:00 PM 1:00 PM 2:00 PM 3:00 PM 4:00 PM 5:00 PM 6:00 PM 7:00 PM 8:00 PM 9:00 PM 10:00 PM 11:00 PM Total Current Open/Close Open Open Open Open Open Open Open Open Open Open Open Closed Closed Closed 2014 2013 2012 2% 11% 4% 12% 14% -2% 10% 5% 16% 16% 10% 1% 2% 3% 8% 10% 16% 8% 13% 6% 11% 10% 13% 3% 7% 13% 8% 9% 3% 11% 12% 3% 22% 9% 0% 100% 100% 100% Question 3 Using a formula, calculate the hourly $ goal in the table below. Day Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Time 10:00 AM 11:00 AM 12:00 PM 1:00 PM 2:00 PM 3:00 PM 4:00 PM 5:00 PM 6:00 PM 7:00 PM 8:00 PM 9:00 PM 10:00 PM 11:00 PM % 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% $ Question 5 One of the guiding principles in WFP is to maximize staff based on their SPH (sales per hour). Based on the information would you allocate to Amy, Betty, and Caroline? 10/17/2017 WFP Assessment 3 of 12 Question 5 One of the guiding principles in WFP is to maximize staff based on their SPH (sales per hour). Based on the information would you allocate to Amy, Betty, and Caroline? Amy: $650 SPH, 20 hours available Betty: $450 SPH, 30 hours available Caroline: $250 SPH, 40 hours available Total hours available to schedule: 40 hours Question 6 For Monday (as per question #3) assume all people are available to work at any point during the day. What nine hour s would you schedule each person? Why? Amy: open all day Monday Betty: open all day Monday Caroline: open all day Monday 10/17/2017 WFP Assessment 4 of 12 Question 6 The Store Manager of store 1234 has just re-submitted employee availability for Monday. What are your observations? Amy: Can work from 10am until 5pm Betty: Can work from 10am until 6pm Caroline: Can work from 11am until 7pm Question 6 Consider the following scenario: - It is 11AM on Friday - Schedules are technically due to stores by 4PM on Friday - The department is down two people, one due to illness and another due to a family emergency - Best case scenario, assuming all people available within the department to pick up the slack, schedules will be send o - It is the height of people taking vacations in stores and three store managers are asking for their schedules in advance - Your computer is running really slowly and just crashed; you just lost 3 hours of work - The Vice President of Retail just called identifying a number of concerns with the current week's schedule, and would why certain decisions were made - You have dinner plans with your partner as you are celebrating your three year anniversary together What do you do? 1) Postpone dinner plans with my partner 2) See the VP of Retail to rectify any concerns and provide explanations of why decisions were made. Meanwhile, comp fixed. 3) Kindly request buffer time from store managers and compromise on having the schedules submitted by Friday evenin 10/17/2017 WFP Assessment 5 of 12 1) Postpone dinner plans with my partner 2) See the VP of Retail to rectify any concerns and provide explanations of why decisions were made. Meanwhile, comp fixed. 3) Kindly request buffer time from store managers and compromise on having the schedules submitted by Friday evenin 10/17/2017 WFP Assessment 6 of 12 SUBJECT: Evaluating DEPT: WFP UPDATED: Dec 08, 2015 siness skills. ion making. e and think about each scenario carefully. usiness is $150,000. You are now analyzing historical of sales history broken down by day for the same e in forecasting goals for the week ahead. Sun Total 13.0% 12.0% 12.2% 11.3% 100.0% 100.0% 100.0% 100.0% 10/17/2017 WFP Assessment 7 of 12 ,000 weekly goal based on the historical data above. TOTAL $150,000 100% y in the same week as the scenario above. be used (in cells P60:P73). goals for the week ahead. 10/17/2017 WFP Assessment 8 of 12 2012 2011 2010 3% 7% 13% 8% 9% 3% 11% 12% 3% 22% 9% 0% 1% 3% 9% 8% 19% 8% 3% 7% 17% 8% 16% 2% 1% -1% 8% 18% 28% 36% 10% 100% 100% 100% HGB % 0% our). Based on the information below how many hours 10/17/2017 WFP Assessment 9 of 12 uring the day. What nine hour shift (start to end time) 10/17/2017 WFP Assessment 10 of 12 y. What are your observations? mergency e slack, schedules will be send out at noon on Saturday ng for their schedules in advance of the 4PM deadline ent week's schedule, and would like an explanation of rsary together s were made. Meanwhile, computer is with IT to get dules submitted by Friday evening. 10/17/2017 WFP Assessment 11 of 12 10/17/2017 WFP Assessment 12 of 12