Delnovo produces high-quality made-to-order ultrabook computers for business users. You have been hired as an external consultant to provide a tool to help them control their staffing costs. Delnovo is able to make weekly (but not daily) adjustments to their staffing for two operations, picking and assembling. A "picker" retrieves and organizes all of the components needed for a particular ultrabook order, and an "assembler" assembles them into an ultrabook. Loading the software, testing and shipping the ultrabook are outside of the scope of this project. To maintain their historically high level of customer satisfaction, Delnovo requires that all orders that come in on one day be assembled the next day (they are not assembled the same day as ordered to facilitate credit checks and inventory management). To achieve this objective, Delnovo pays the pickers and assemblers overtime or brings in additional staff late in the day at time-and-a-half wages (150% of normal hourly wages) until the day's orders are completed. Delnovo does not assemble on Sundays, so orders that come in on Saturday and Sunday are assembled Monday; orders that come in Monday are assembled Tuesday, and so on until orders that come in on Friday are assembled Saturday. Each Friday Delnovo's planners use historical data and current economic trends to produce a forecast for the aggregate number of units that will need to be assembled the next week. Note: You have not been asked to do forecasting; your task is to help Delnovo turn a forecast into a staffing level, considering that forecasts have error that you can model given the data. The file DelnovoData.xlsx on Moodle contains the weekly forecasts and actual orders for 2019, from which you can model the forecast errors, i.e., the actual demand (AD) can be calculated by the forecasted demand (RD) by adding an random error (AD = RD + ????????), where the random error ???????? is normally distributed, and its mean and standard deviation can be calculated from the data. Of course, the actual daily orders do not distribute themselves evenly throughout the week; the long-run historical averages for when the orders come in are Saturday-Sunday 24%, Monday 13%, Tuesday 20%, Wednesday 17%, Thursday 13% and Friday 13%. Based on the forecast, the number of pickers and assemblers for the next week is set. These employees are guaranteed 8 hours per day wages, 6 days per week, whether they have work to do or not. Pickers make $15 per hour, and assemblers make $30 per hour. Thus, staffing at P pickers and A assemblers for a week costs a minimum of $158P per day for picking and $308A per day for assembling, plus additional overtime cost needed to complete the day's work. Recently a time study was done on picking; the data on time to pick the components for one Ultrabook can be found in DelnovoData.xlsx. Based on central limit theorem, we know that if the picking time for one ultrabook has a mean ???????? and standard deviation ????????, then the time required to pick for ???? ultrabooks will be normally distributed with mean ???????????? and standard deviation √????????????. Also, from their enterprise management system the company extracted the number of ultrabooks assembled and the number of hours to assemble them from each week in 2019; those data can be also be found in DelnovoData.xlsx. Based on the data on assemble hours, we could develop a relationship between the assemble time required for assembling ???? ultrabooks using linear regression, where the regression model will be ???? = ????0 + ????1???? + ????. Hence, given number of ultrabooks to be assembled in one week, we could predict the assemble hours by the linear relationship ????0 + ????1????, adding a random residual, which is normally distributed with mean and standard deviation calculated from data. You could, in theory, simulate individual ultrabook picking and assembly, but that would require many more hours of consulting time than Delnovo is willing to support. Therefore, you have proposed a spreadsheet simulation that works at the level of daily aggregate picking and assembly time as a function of the number of orders to be assembled that day. In other words, you will NOT simulate individual ultrabooks, but rather the daily aggregate hours of work needed and the daily aggregate hours of picking and assembly time available to compute wage cost. Delnovo wants to use your simulation to set the staffing level, either to minimize the longrun average weekly staffing cost, or to minimize the 90th percentile of weekly staffing cost; they will decide later which metric to use (Note: someone suggested the 90th percentile is a better way to manage risk; you should use your simulation results to help them understand the differences between these two objectives, if there is any difference). Your job is NOT to do the optimization, since a new optimization will be needed each week based on that week's forecast. Rather, you are to provide a simulation model that could be used for optimization by estimating weekly staffing cost for different staffing plan.

Excel Data: https://www.dropbox.com/s/cyjydzugx8jfhau/DelnovoData.xlsx?dl=0 (There are three sheets in total)

a) Find the relationship between assembler hours and number of ultrabooks to be assembled. (20 marks)

b) Suppose the forecast department provides a forecast of 2200 ultrabooks for next week. Calculate the average staffing cost with 14 pickers and 19 assemblers. (30 marks)

c) Use your simulation model to find a good staffing plan for next week when the forecasted demand is 2400 ultrabooks. (50 marks)

May you please attach the excel file as well? You may share it by creating a link. I need to submit the both the excel file and the step by step explanation.

pur-new-sol

Related Questions