The following steps need to be put into an Excel spreadsheet.
Your Moeing manager has asked you to complete one more project! Download this Part Data workbook that includes a single Moeing part with its corresponding characteristics. Using that spreadsheet, complete the following things for your Moeing manager.
1. Calculate the units of safety stock required for the part shown. Be sure to include a cell somewhere in the spreadsheet that allows you to input a desired confidence level. Enter a confidence level of your choice, making sure that it is between 50.0% and 99.9%.
2. In a column on your spreadsheet, create a vertical list of potential confidence levels, starting at 50.0% and ending at 99.9%. The vertical list of confidence levels should increase in increments of a tenth of a percent (e.g., 50.0%, 50.1%, 50.2%, etc).
3. In an appropriate row at the top of the confidence levels column described in #2 above, include a row of possible lead times, ranging from 0 months to 6 months. The row should increase by increments of 0.5 (eg., 0, .5, 1.0, 1.5, 2.0, 2.5....6.0). Organize the row so that you can create a data table of safety stock with lead time as the header row, and confidence level as the leading column.
4. Using the column of confidence levels, row of lead times, and your calculated safety stock (from #1 above), create a data table that shows the correct number of safety stock units required for EACH corresponding confidence level in the list and EACH corresponding lead time in the list. You MUST use a data table for this step, which means that there should be a safety stock formula in only a single cell in your entire spreadsheet (the safety stock cell in #1 above).
5. Create a line graph that depicts how the number of safety stock units changes with confidence levels. The line graph should reflect lead time at the given 2.5 months (that is the only lead time for which you need to graph safety stock). The horizontal axis on the graph should reflect the confidence level (between 50.0% and 99.9%), and the vertical axis should show the number of safety stock units required.
6. Make the graph you created in #4 clean, sharp, and professional. Be sure to include a graph title as well as labels for each axis.
7. In a cell or box on your spreadsheet, write at least one full paragraph that describes the shape of the graph. In the description, give a detailed and clear explanation about what happens to the safety stock as confidence level changes. Be specific.
8. In another cell or box on your spreadsheet, describe what happens to safety stock for different lengths of lead time.
9. When you are finished with the quiz in the provided spreadsheet, save the file with a new name to your computer. Submit this quiz and then go to the activity entitled "Data Tables Quiz Drop Box" and upload your spreadsheet. This must be done as soon as possible after submitting your test. Remember, you only have 30 minutes to work on the spreadsheet and upload it.