Exp22_Excel_Ch09_HOE_CircleCity

profileSolutions Aplus
 (Not rated)
 (Not rated)
Chat

#Exp22_Excel_Ch09_HOE_CircleCity 

#Exp22 Excel Ch09 HOE CircleCity 

#Excel Chapter 9 Hands-On Exercise - Circle City Sporting Goods


  

Project Description:

You are the regional manager of Circle City Sporting Goods (CCSG), a retailer that has locations in Indianapolis, Bloomington, and South Bend. Each store manager gathers monthly data for every department and prepares a quarterly worksheet. The worksheets are identical to help you consolidate sales data for all three locations. You want to review sales data for the past fiscal year. Before consolidating data, you will format the worksheets, copy data to a summary sheet, and insert hyperlinks to the individual quarterly sheets. Later, you will consolidate each store’s data into a regional workbook. You will use auditing tools to identify errors and add validation to ensure accurate data entry. Finally, you will use tools to protect data, worksheets, and workbooks against unauthorized access, and then mark the workbook as final.


     

Start Excel. Download and open   the file named Exp22_Excel_Ch09_HOE_CircleCity.xlsx.   Grader has automatically added your last name to the beginning of the   filename.
 

  Note: When you open the file, Excel prompts you to fix a circular error.   Click or press OK and continue.

 

The main title and row headings   are displayed only in the Qtr1 worksheet. You want to fill in the title and   row headings for the other three quarterly worksheets as well as the yearly   worksheet.
 

  Group the Qtr1, Qtr2, Qtr3, Qtr4, and Year worksheets. Select cell A1 in the   the Qtr1 worksheet. Fill the formatting and content across the grouped   worksheets. Fill the range A2:A10 across the grouped worksheets. Ungroup the   worksheets.

 

You want to insert monthly and   department totals for the quarterly worksheets.
 

  Group the four quarterly worksheets. Select the range B3:E11 and use AutoSum.

 

Now you want to format values in   the quarterly worksheets.
 

  With the four quarterly worksheets grouped, apply Accounting Number Format to   the ranges B3:E3 and B11:E11. Select the range B11:E11 and apply the Total   cell style. Ungroup the worksheets.

 

The Year worksheet contains   summary data. You will insert a hyperlink to each quarterly worksheet.
 

  On the Year worksheet, in cell B2, insert a hyperlink to the range E2:E11 in the Qtr1 worksheet with the   ScreenTip text Qtr 1 Totals.
 

  In cell C2, insert a hyperlink to the range E2:E11 in the Qtr2 worksheet with the ScreenTip text Qtr 2 Totals.
 

  In cell D2, insert a hyperlink to the range E2:E11 in the Qtr3 in the worksheet with the ScreenTip   text Qtr   3 Totals.
 

  In cell E2, insert a hyperlink to the range E2:E11 in the Qtr4 in the worksheet with the ScreenTip   text Qtr   4 Totals. Test   the hyperlinks to ensure the work correctly.

 

You decide to hide some   worksheets. After hiding three worksheets, you decide to display two   worksheets again to continue working on them.
 

  Select and hide the Year, Consolidated, and Future worksheets. Then unhide   the Year and Consolidated worksheets. (The Future worksheet should still be   hidden.)

 

Now you are ready to insert a   formula with a reference to the Qtr1 department totals.
 

  Display the Year worksheet. In cell B3, insert a formula with a 3-D reference   to cell E3 in the Qtr1 worksheet. Copy the formula to the range B4:B11.

 

Now you are ready to insert a   formula with a reference to the Qtr2 department totals.
 

  In cell C3 in the Year worksheet, insert a formula with a 3-D reference to   cell E3 in the Qtr2 worksheet. Copy the formula to the range C4:C11.

 

Now you are ready to insert a   formula with a reference to the Qtr3 and Qtr4 department totals.
 

  In cell D3 in the Year worksheet, insert a formula with a 3-D reference to   cell E3 in the Qtr3 worksheet. In cell E3, insert a formula with a 3-D   reference to cell E3 in the Qtr4 worksheet. Copy the formulas in the range   D3:E3 to the range D4:E11.

 

Column F in the Year worksheet   is designed to display department totals.
 

  In cell F3 in the Year worksheet, insert the SUM function with a 3-D   reference to calculate the yearly total for the Athletic Apparel department   using cell E3 in the four quarterly worksheets. Copy the formula to the range   F4:F11.

 

You are ready to format the   values in the Year worksheet.
 

  Apply Accounting Number Format to the ranges B3:F3 and B11:F11. Apply Comma   Style to the range B4:F10. Apply the Total cell style to the range B11:F11.

 

Although you used 3-D references   to pull in quarterly sales for each department, you want to create a   comprehensive worksheet displaying monthly sales for the entire year. You   will use the Consolidate tool.
 

  Display the Consolidated worksheet. In cell A1, use the Consolidate tool to   select and add the range A2:E11 in the Qtr1 sheet, Qtr2, Qtr3, and Qtr4   worksheets. Use the top row and left column labels. Do not create links.

 

Now you are ready to format the   consolidated data.
 

  With the consolidated data selected, apply AutoFit Column Width. Select the   range B1:N1 and apply bold and center horizontal alignment. Select the range   B10:N10 and apply the Total cell style.

 

The CCSG Totals worksheet   contains totals from stores in two cities. However, you need to link to data   in another workbook to obtain the Bloomington values.
 

  Open the Exp22_Excel_Ch09_HOE_Bloomington.xlsx   workbook. Go back to the Exp22_Excel_Ch09_HOE_CircleCity.xlsx   workbook. In cell C3 on the CCSG Totals worksheet, insert a link to the   Athletic Apparel department total (cell B3). Change $B$3 to B3 in the   formula. Use AutoFill to copy the formula from cell C3 to the range C4:C10   using Fill Without Formatting. Close the Bloomington workbook.

 

Your workbook contains FW2, a   worksheet for the Qtr2 data for the Fort Wayne location. The worksheet   contains a formula that is missing an adjacent cell. Now you want to find and   correct it.
 

  On the FW2 worksheet, check for errors and update cell E3 to include adjacent   cells.

 

The FW2 worksheet also contains   a circular error.
 

  Use the error-detection tool to find the cell containing a circular   reference. Then correct the formula.

 

On the FW3 worksheet, you want   to insert the AVERAGEIF to calculate the average monthly revenue for   departments that contain the word ball in   the first column. However, the function will return #DIV/0! errors for rows   that do not contain ball.   Therefore, you will nest the AVERAGEIF function within the IFERROR function   to avoid the error.
 

  Display the FW3 worksheet. In cell F3, insert an IFERROR function with a   nested AVERAGEIF function with a relative reference to cell A3, the criteria *ball, and the average range B3:D3.   The second argument in the IFERROR function should be "-". Copy the function to the range   F4:F10.

 

You want to create a validation   rule to prevent the user from entering too many seats sold. For now, you will   create a validation for the Exercise Equipment values on the FW3 worksheet.
 

  Select the range B4:D4 on the FW3 worksheet, create a validation rule to   allow decimal values less than or equal to 500000. Enter the input message title Sales Data and the input message Enter the   sales amount for the respective month. (including the period). Use the Warning alert with   the error alert title Potentially Invalid Data and the error alert message The projected   maximum is $500,000. However, if actual sales are higher, enter the actual   value.   (including the period). Test the data validation by attempting to enter 600000 in cell D4 and click or press   Yes. Use the Data Validation arrow to circle invalid data.

 

You want to unlock data-entry   cells so that the user can change the number of seats sold in the worksheets.   
 

  Group the Qtr1, Qtr2, Qtr3, and Qtr4 worksheets. Select the range B3:D10 and   unlock these cells. Ungroup the worksheets.

 

Now that you unlocked data-entry   cells, you are ready to protect the Qtr1 worksheet to prevent users from   changing data in other cells.
 

  Protect the Qtr1 worksheet using the default allowances with the password Expl0r!ng.

 

Mark the workbook as final.
 

  Note: Mark as Final is not available in Excel for Mac. Instead, use Always   Open Read-Only on the Review tab.

 

Close Exp22_Excel_Ch09_HOE_CircleCity.xlsx. Exit Excel. Submit the file   as directed.

    • a year ago
    Exp22_Excel_Ch09_HOE_CircleCity
    NOT RATED

    Purchase the answer to view it

    blurred-text
    • attachment
      Exp22_Excel_Ch09_HOE_CircleCity_Apr22.xlsx