Orders and Inventory Worksheet
Shelly Cashman Excel 2019 | Module 3: End of Module Project 1
Shelly Cashman Excel 2019 | Module 3: End of Module Project 1
Create an order and inventory projection
GETTING STARTED
Open the file SC_EX19_EOM3-1_FirstLastName_1.xlsx, available for download from the SAM website.
Save the file as SC_EX19_EOM3-1_FirstLastName_2.xlsx by changing the “1” to a “2”.
If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
With the file SC_EX19_EOM3-1_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
Eugene Park is a senior consultant for the Seven Summits Group, a consulting firm in Denver, Colorado. He is working with HardyFit Tools, a manufacturer of hand tools, to improve their business operations. He has created a workbook projecting the company’s orders and inventory, and asks for your help in completing and formatting the projections. Go to the Orders worksheet. Rename the Orders worksheet to use Orders and Inventory as the worksheet name.
Unfreeze the top 2 rows of the worksheet, and then middle-align the contents of the merged cell A1.
Cut the contents of cell K2 and paste them in cell A2. In cell B2, insert a formula that uses the NOW function to display today’s date.
Merge and center the range A3:A9. Rotate the text up to 90 degrees in the merged cell, and then change the width of column A to 5.00.
Copy the formatting from the range B5:K5 and apply it to the range B7:K7.
In cell B8, decrease the indent by one level. Copy the values in the range C26:G26, and then paste only the values in the range C8:G8. Delete row 26 to remove the repeated data.
Use Goal Seek to set cell C11 to the value of 300 by changing cell C7.
In cell H4, insert a Line sparkline based on the data in the range C4:G4. Fill the range H5:H9 without formatting based on the contents of cell H4. Change the color of the sparklines in the range H4:H9 to Gold, Accent 6, Darker 25% (10th column, 5th row of the Sparkline Theme Colors gallery). Add markers for each month, and then change the marker color to Green, Accent 4, Darker 50% (8th column, 6th row of the Sparkline Theme Colors gallery).
Copy the formula in cell K4 and then paste the formula and number formatting in the range K5:K8.
In cell C12, insert a formula that divides the total orders for August (cell C9) by the total number of orders (cell I9). Use an absolute reference to cell I9, and then copy the formula to the range D12:G12.
In cell H15, insert a Column sparkline based on the data in the range C15:G15. Display the High Point and Low Point in the sparkline, and then fill the range H16:H23 without formatting based on the sparkling in cell H15.
Copy the formula in cell I15 and then paste only the formula in the range I16:I23.
Change the chart in the range J10:O23 to a Pie chart. Enter Average Orders per Month as the chart title. Add data labels to the outside end of each slice.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
(Note: The value in cell B2 of the Orders and Inventory worksheet may differ from that shown below.)
Final Figure 1: Orders and Inventory Worksheet
| 2 |