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 double wages (200% 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 contains the weekly forecasts and actual orders for 2021, from which you can model the forecast errors, i.e., the actual demand (AD) can be calculated by the forecasted demand (RD) by adding a 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 28%, Monday 12%, Tuesday 16%, Wednesday 18%, Thursday 12% and Friday 14%. 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 $25 per hour, and assemblers make $50 per hour.

Thus, staffing at P pickers and A assemblers for a week costs a minimum of $25*8*P per day for picking and $50*8*A 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 2021; 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 its 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 that only depends on 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.

1. Find the relationship between assembler hours and number of ultrabooks to be assembled.

2. Suppose the forecast department provides a forecast of 3000 ultrabooks for next week. Calculate the average staffing cost with 30 pickers and 40 assemblers.

3. Use your simulation model to find a good staffing plan for next week when the forecasted demand is 3500 ultrabooks.

May you please also attach Excel file and explanation thanks.

Excel link: https://docs.google.com/spreadsheets/d/17HcQcxMM-T1D4-wwyXQ3hOPWIAesxb1f/edit?usp=sharing&ouid=108980914351747791957&rtpof=true&sd=true