Part 2: Organizing the Worksheets 1. Navigate to the PPCData worksheet. 2. Insert 21 rows at the top of the worksheet. 3. Use the figure below as a guide to enter the required labels and borders to prepare your worksheet for analysis. 1 2 3 4 5 Order Type Analysis 6 Total Number of Orders 7 New Order 8 Recurring Order 9 Last order between 6-12 months ago 10 Last order over 1 year ago 11 12 13 Orders by State 14 15 Iowa 16 Missouri 17 Nebraska 18 Illinois 19 20 21 Order Type Orders Sales Piedmont Paper Company Prepared by: Student Name Number of Orders Avg. Profit per Order Average Sale Price Median Sale Price Maximum Units Sold Minimum Units Sold Range of Units Sold State Product Discount Band Units Sold Manufacturing Price Sale Price Discounts Sales Cost of Goods Sold (COGS) Profit Date 4. Navigate to the Annual Comparison worksheet. 5. Select any cell in the table. 6. Go to Table Design, then in the Tools grouping, select Convert to Range to remove the table. 7. To remove the formatting of the worksheet, highlight cells A1 to C7. From the Home tab, Editing group, select Clear then Clear Formats to remove the coloring. 8. Delete the content of the cells in row 1. Enter 2018-2019 in B1, 2019-2020 in C1, and 2020-21 in D1. Part 3: Build the Worksheet Beginning in row 22 of the PPCData worksheet, you find the raw data. PPC would like to analyze total dollar sales by order type. The area A5:C10 has been set up for this purpose. 1. In cell B6, enter a function to calculate the total number of orders. 2. In cell C6, enter a function to calculate the total dollar sales of all orders. 3. In cell B7, enter a function to calculate the total number of orders with order type of New Order. Use proper cell referencing and copy this function down to calculate the number of orders for the other order types in B8:B10. 4. In cell C7, enter a function to compute the total dollar sales for orders with order type of New Order. Use proper cell referencing and copy this function down to calculate the total dollar sales for the other order types in C8:C10. PPC would also like to analyze order profitability by state. The area A13:C18 has been set up for this purpose. 5. In B15, enter a function to count the number of orders from Iowa. Use appropriate cell referencing and copy this function down for the rest of the states in B16:B18. 6. In C15, enter a function to calculate the average profit for the orders from Iowa. To do this, you cannot use a simple AVERAGE function. You will need to first determine the total profit for the given state. Then divide this value by the number of orders for that state. Use appropriate cell referencing and copy this function down for the rest of the states in B16:B18. (If you have calculated this correctly, the value in C15 should be $25,134.78.) 7. Enter the appropriate functions in cells G13:G17 to calculate the requested measure as noted in F13:F17. 8. Go to the Annual Comparison worksheet. Here, you need to display the Average Profit for each state for 2020-2021 as calculated above. Cell D2 should be equal to the value of cell C15 in the PPCData worksheet. Once you have done this, copy the formula in cell D2 down for the rest of the products in D3:D7. 9. On the Annual Comparison worksheet, enter a function in cell B8 to calculate the sum for all states in 2018-2019. Copy this function across for the other years in C8:D8. Part 4: Formatting - Enhance the Appearance of the Worksheet 1. On the PPCData worksheet, merge and center the worksheet headings found in cells A1 and A2 across columns A - L. Increase the font size of the row 1 heading to 18 pt. Bold this heading. Increase the font size of the row 2 heading to 16 pt. 2. Replace "your name" in row 2 with your own name. 3. Format the values in C6:C10, C15:C18 and G13:G17 to display as Currency with two (2) decimal places. 4. On the Annual Comparison worksheet, format the values in B2:D6 to display as Currency with two (2) decimal places. Part 5: Add a Chart 1. On the PPCData worksheet, add a column chart to show the average profit for each state. Modify the chart elements so the chart should look like this: Average Profit by State $30,000.00 $25,134.78 $25,000.00 $20,000.00 $15,000.00 $10,000.00 $5,000.00 $- Iowa $26,258.10 Missouri $26,626.58 Nebraska $20,623.46 Illinois 2. Click and drag the chart so that the upper left corner is positioned in cell 12 and the lower right corner is positioned in cell N18. Save your worksheet as PPCData_YourLastName.xlsx Part 6: Interpretation 1. PPC wants to grow its sales by increasing the dollar value of orders from its existing customers. Using the Order Type Analysis section of the PPCData worksheet, which order type(s) should it focus its marketing efforts on? Explain the reasoning behind your answer. 2. PPC wants to understand its order profitability by location. Based on the Orders by State section of the PPCData worksheet, what should PPC infer from its order profitability by state. statistics