So far in this excel class we've covered the basics of entering data in cells and creating charts. Now all of a sudden we have this assignment and are supposed to know how to do these things. I'm not sure what formulas I am supposed to use for any of this.

Another profitable facet of Stinger Sam's Auto Parts business is supplying parts for auto repairs. The most critical component of the parts supply business is having enough of the right parts on hand so that repair shops can receive same-day delivery. The key to profits is minimizing the number of parts that need to be warehoused while also ensuring that sufficient parts are on hand to meet orders. Because each warehouse distribution center serves a different set of customers with different needs, each center must be considered separately. Distribution centers have a five-day lead time for ordering of parts; this must be taken into consideration when determining target inventory levels.

The costs involved in warehousing the parts include the working capital tied up in the inventory itself (cost of each unit), as well as the space to store the part. These costs can be substantial. On the other hand, alternative "generic" parts are often available from rival suppliers, and keeping the auto repair centers supplied and customers satisfied is critical.

One of the most problematic items to supply and store are bearings. Bearings are both high volume and relatively large, taking up considerable warehousing space. A bearing is also relatively expensive. So, analyzing bearing needs is a good place for Stinger Sam's to start, specifically in one of its largest distribution centers such as Metro Dallas, which serves 10 major customers. You have been asked to analyze the bearing inventory level requirements for this center, including simulating demand levels based on 30-day historical extremes, calculating a target inventory level based on this simulation, and then comparing simulated values to actual values from the past five days from the targeted warehouse.

1. Open the workbook named Parts_Students.xlsx
2. Modify the format of the data on the BearingData worksheet so that zero values are displayed with a 0 instead of the default dash, aligned in the I's column
3. On the BearingData worksheet, take the existing 30-day data for these bearings for each customer to calculate the high and low limits of the bearing demand by customer (These will be placed in rows 35 and 36, the rows have already been labeled for you)
4. On the worksheet named Simulation, use the high/low limits you just calculated to simulate daily requirements for cach customer to obtain a combined daily requirement. Assume that the daily requirements will vary for each customer randomly between the high/low limits you have calculated from the existing 30-day data for that customer. Generate the data for approximately 100 instances, and then copy the results as values to SimulationData1 in the workbook, keeping the original analysis intact on the Simulation worksheet so that it can be used again later.
5. On the SimulationData1 worksheet, use the daily demand total data to calculate the daily mean, mode, median, and standard deviation for the combined requirements of all 10 customers.
6. Extend your analysis (on SimulationData1) to include a ranking of the data (1 to 100) so that the day with the least total demand has the lowest ranking (rank 1)
7. To the right of the data on this same worksheet, create a listing of the top and bottom five daily combined demands from the 100 simulated instances. A table has been created for this, you need to enter the formulas -- see cells: O4:Q8).
8. Recommend a target inventory level needed for a five-day period (this will be in cell P14) based on the following; to be on the cautious side, assume each day's supply will be equal to the average daily demand for all locations combined, plus two standard deviations:

• Because the mean and standard deviations might not already be integers, round the daily demand up to the nearest whole number.
• Use the daily demand to calculate demand over a five-day period.

1. The warehouse manager has tracked a total of five different parts over the past five days, recording for each shipment the value of the part and the number of days it was in storage. One of these tracked parts is the bearing you have just analyzed. The data has been compiled in a workbook named Demand.xlsx. Each line item represents i single shipment of one item. Copy the data from the Demand.xlsx workbook, located in the Week 4 Module folder, to your workbook and in the sheet named ActualDemand
2. On the same worksheet, summarize the data to determine the number shipped by part, the total values of those shipments by part, and the average number of days that part was stored, using the format shown below. (This will be placed in these cells: F1:I6)

Item Description

#Items Shipped

Total Dollar Value of Items Shipped

Average #Days Held in Inventory

Bearings

Timing Belts

Air Filters

Fan Belt

Electronic Board

In the # Items shipped column, write a formula to determine the number of bearings shipped. Write the formula so that it can be copied down the column to automatically determine the number of timing belts shipped, the number of air filters shipped to and so on. In the Total Dollar Value of Items Shipped column, write the necessary formula to determine the value of all bearings shipped. Again, write the formula so that it can be copied down the column to automatically determine the value of timing belts, air filters, and so on. In the Average #Days Held in Inventory column, write the necessary formula to determine the average number of days bearings shipped were held in inventory; again write the formula so that it can be copied down the column. Be certain that these formulas will work even if the data is updated in the future.

1. Merge Cells F9:J10 in the ActualDemand worksheet.
2. Based on the recommended inventory level you previously calculated for bearings would you have had enough bearings in the warehouse to cover these orders? Place your answer in the cells you created in step 11. Clearly identify this answer and highlight it in yellow.
3. Double-check all values and formulas for correct implementation. Include sufficient formatting and titles to clearly identify the worksheet elements
4. Once you are satisfied with your entry, upload in the ExPrep portal.

Here are links to the Excel Spreadsheets:

https://drive.google.com/file/d/1onGfYPlExHDMfLV9HZbTVaU2PYEQ4H6P/view?usp=sharing

https://drive.google.com/file/d/1xMiRFo2u8HNDE7ew3vHV10OvTPJiM_fE/view?usp=sharing