SC_EX19_EOM3-1_KathleneGonzalez_Report_1

Shelly Cashman Excel 2019 | Module 3: End of Module Project 1 kathlene Gonzalez GE ver. 8.0.0 1. 7/7 Change the name of a worksheet. 2. 0/7 Unfreeze rows. In the Orders and Inventory worksheet, the top two rows should not be frozen. Center a range vertically. In the Orders and Inventory worksheet, the merged content in the range A1:K1 should be centered vertically. 3. 7/7 Cut and paste a cell value. Create a formula using a function. 4. 7/7 Merge and center a range. Change the orientation of cell content. Manually change the column width. 5. 0/8 Copy and paste formatting. 6. 8/8 Change the indent of cell contents. Copy and paste a range of values. Copy and paste without formatting. Delete a row. 7. 8/8 Use Goal Seek to set a cell value. Use Goal Seek to set a cell value. 8. 8/8 Add sparklines to a cell. AutoFill values in a range without formatting. Change the color of a sparkline. Add markers to sparklines. Format sparkline markers. 9. 8/8 Copy a formula into a range. 10. 3/8 Use an absolute reference in a formula. In the Orders and Inventory worksheet, the formula in cell C12 should contain an absolute reference to cell I9. Create a formula. Copy a formula into a range. In the Orders and Inventory worksheet, one or more cells in the range D12:G12 contains an incorrect formula. 11. 8/8 Add sparklines to a cell. Format sparklines. Format sparklines. Add sparklines to cells. 12. 0/8 Copy a formula into a range. 13. 5/8 Change the chart type. Insert a chart title. Add data labels to a chart. SUBMISSION #1 | SCORE IS: 69 OUT OF 100 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 the Orders and Inventory worksheet, the formatting from the range B5:K5 should be copied 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% (10 th column, 5 th row of the Sparkline Theme Colors gallery). Add markers for each month, and then change the marker color to Green, Accent 4, Darker 50% (8 th column, 6 th 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. In the Orders and Inventory worksheet, the formula from I15 should be copied and pasted without formatting 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. In the Orders and Inventory worksheet, the Pie chart should display data labels on the outside end of each slice.

Author: Note: Do

M

HardyFit Tools Date: 2/17/2024 Projected Orders Aug Sep Oct Nov Dec Trend C-clamps 312 406 569 492 663 Hand planes 72 75 137 201 165 Socket sets 402 327 458 593 590 Tool sets 330 626 503 607 638 wrench sets 384 301 522 416 409 Total orders 1500 1735 2189 2309 2465 Average orders 300 347 438 462 493 Share 15% 17% 21% 23% 24% Projected Inventory Aug Sep Oct Nov Dec Trend C-Clamps 325 410 600 500 700 Drive sockets 410 330 460 600 600 Hammers 400 600 500 600 650 Hand planes 100 100 140 210 200 Hex wrenches 400 300 525 425 400 Ratchets 410 330 450 600 600 Screwdrivers 500 630 500 610 650 Socket wrenches 400 350 525 420 410 Total inventory 2945 3050 3700 3965 4210