Reference no: EM133793775

Project 1: Ch09_HOE_CircleCity

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.

Steps to Perform:

Step Instructions Points Possible
1 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. 0
2 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. 6
3 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. 6
4 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. 5
5 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. 12
6 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.) 5
7 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. 5
8 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. 5
9 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. 5
10 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. 5
11 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. 5
12 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. 5
13 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. 3
14 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. 4
15 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. 2.5
16 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. 2.5
17 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. 5
18 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. 12
19 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. 4
20 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. 3
21 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. 0
22 Close Exp22_Excel_Ch09_HOE_CircleCity.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100

Project 2: Ch10_HOE - Commodities 1.1

Project Description:
You are a financial analyst for a brokerage firm. Your manager wants you to analyze commodity sales patterns of the top five brokers for the first quarter. Unfortunately, the data required to complete the analysis are distributed among several key data sources. You received basic broker information through an email and transaction information from an Access database, and you will need to retrieve real-time NASDAQ trading information from the Web. You do not want to simply copy and paste the data into the worksheet; you want to connect and transform data in Excel so that the constantly changing values are always up to date.
You also want to create data visualizations to provide geospatial information and a business dashboard.

Steps to Perform:

Step Instructions Points Possible
1 Start Excel. Download and open the file named Exp22_Excel_Ch10_HOE_Commodities.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 You have a list of client information stored as a CSV file. You want to use Get & Transform Data (Power Query) to import the file, so the information will update as new clients are added.

Use Get & Transform (Power Query) to import the e10h1Client_Info.csv. Load the data. Rename the newly created worksheet Clients. 14
3 All commodity transactions are stored in an Access database. You will use the Get & Transform tools to import this data while maintaining a connection to the database. You want to use the Power Query Editor to shape the data.

Use Get & Transform (Power Query) to import the transactions table from the e10h1Transactions.accdb database. Load the data to a new worksheet. 10
4 The database table you imported contains data that was incorrectly formatted. You will use the Power Query Editor to reformat the data. In addition, a coworker created a list of broker contact information as a tab-delimited file in Notepad. You will use the Power Query Editor to shape the data by splitting the columns and providing unique data labels before importing it to Excel.

Use the Power Query Editor to change the data type of the date field in the Transaction query from Date/Time to Date format and the Purchase_Price and Selling_Price fields to Currency.

Use Get & Transform (Power Query) to load the e10h1Broker_Info.txt file in the Power Query Editor. Use Tab as the delimiter. Split the Name column using Space as the delimiter splitting the column at the Left-most occurrence. Rename the newly split columns Name.1 and Name.2 First and Last respectively. 6
5 Split the City, State column using Comma as the delimiter. Use Power Query to Trim the excess space off the newly created City, State.2 column. Split the City, State.2 column using Space as the delimiter. Then click or press the X located to the left of the step Changed Type3 in the Applied Steps box located in the Query Settings pane.

Rename the City, State.1 column City, the City, State.2.1 column State, and the City, State.2.2 column Zip Code. Close & Load the transformed data and then rename the worksheet Brokers. 4
6 Transactional information for Quarter 4 sales are stored in a separate Access database. You will use Power Query to import the Quarter 4 data as a query and then append the information, from the existing Transactions table.

Use Get & Transform to load the 2024_Q4_Transactions table from the e10h1Q4_Append.accdb database in the Power Query Editor. Then append the existing Transactions query with the data from the 2024_Q4_Transactions query.

Use the Power Query Editor to change the data type of the Date field to Date. Close and load the appended query. 14
7 You want to finalize the data in your report so it will not update if the external source is modified. You will ensure the external connection properties are set to not refresh the data when the file is opened. This change will ensure that when your report is distributed there will be consistent data with no external connection errors.

Edit the connection properties for each of the external connections so background refresh and refresh the connection on refresh all are disabled. 0
8 You have decided to enhance your report by using Power Pivot to create a PivotTable and PivotChart. You will first enable the Power Pivot add-in, add the existing data to a data model, and then create relationships. You want to use Power Pivot to analyze the data that was imported using Get & Transform. Because the data has already been imported, you will add the existing data to a data model.

Add the transaction information located in the range A1:I76 on the Transactions worksheet to the data model.

Add the broker information located in the range A1:G6 on the Brokers worksheet to the data model.

Add the client information located in the range A1:F19 on the Clients worksheet to the data model. 24
9 After adding all imported data to the data model, you will define the relationships between the transactions database table, the broker information, and client information.

Create a relationship between the Transactions table Account field and e10h1Client_Info Account field.

Create a relationship between the Transactions table Broker_ID field and e10h1Broker_Info Broker_ID. 12
10 You want to summarize the sales of each agent in a PivotTable based on commodity. As your last step, you will use the relational data in the data model to create a PivotTable and PivotChart.

Create a PivotTable starting in cell A1 based on the existing data model in Power Pivot. Add the Last field to the Rows box, Date field to the Filter box, and Commodity to the Columns box.

Add the Selling_Price field to the Values box and apply Accounting Number format to the range B5:E10.

Add a Clustered Column PivotChart based on the PivotTable. Position the chart so the upper right corner is in cell F3. Add the chart title Sales Data. Ensure the chart title appears at the top of the chart.

Rename the worksheet Sales_Analysis. 16
11 You want to create a 3D Map tour that displays your current client locations. You want to visualize locations and client salaries.

Create a 3D map using the City field from the e10h1Client_Info file as the location. Add Earnings as the Height dimension and the Account field as the Category dimension.

Remove the legend and save the 3D map. 0
12 After adding the dimensional visualizations to the 3D Map, you want to create a tour to better view the data from different angles.

Center the map so the United States is in the center of the map area for scene 1. Add a new scene that repositions the map to show the Eastern seaboard. Edit scene 1 to use the Fly Over effect for a duration of 4 seconds. Edit scene 2 to use the Push In effect. 0
13 Save and close Exp22_Excel_Ch10_HOE_Commodities.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100

Project 3: Ch09_Cumulative_Theatre

Project Description:
You are an assistant to the manager of Sugarhouse District Theatre, where touring Broadway shows are performed. You created a workbook for the weekend evening performances to track the number of seats available and sold in each section of the theatre, along with gross revenue. You are ready to complete the workbook.

Steps to Perform:

Step Instructions Points Possible
1 Start Excel. Download and open the file named Exp22_Excel_Ch09_Cumulative_Theatre.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. 0
2 The first column in the Template worksheet contains cells with a different font color and levels of indentation. You want to fill this formatting across the Friday, Saturday, and Sunday worksheets.

Group the Template, Friday, Saturday, and Sunday worksheets. Fill the formatting for the range A5:A20. 5
3 You notice that a formula is missing on worksheets. You will insert the formula simultaneously.

With the worksheets still grouped, in cell E20, enter a formula to subtract the Grand Total Percentage Sold from 1. Apply Bold to the cell. 4
4 The range I4:J7 displays the average number of seats sold in each section. However, two results display #DIV/0! because the template does not contain sample values in some sections. You will edit the formula to avoid the error.

With the worksheets still grouped, edit cell J5 by enclosing the AVERAGEIF function within an IFERROR function. The value_if_error argument should display the text Missing data. Copy the function to the range J6:J7. Fill Without Formatting to preserve the borders. 10
5 Hide the Template worksheet. 3
6 The Totals worksheet contains summary data. You will insert a hyperlink to the number of seats sold in each respective worksheet.

On the Totals worksheet, in cell A5, insert a hyperlink to cell C20 in the Friday worksheet with the ScreenTip text Friday Seats Sold. In cell A6, insert a hyperlink to cell C20 in the Saturday worksheet with the ScreenTip text Saturday Seats Sold. In cell A7, insert a hyperlink to cell C20 in the Sunday worksheet with the ScreenTip text Sunday Seats Sold. Test the hyperlinks to ensure the work correctly. 9
7 Now you are ready to insert a formula with a reference to the number of seats sold for Friday's performance.

In cell B5, insert a formula with a 3-D reference to cell C20 in the Friday worksheet. 3
8 Now you are ready to insert a formula with a reference to the number of seats sold for Saturday's performance.

In cell B6, insert a formula with a 3-D reference to cell C20 in the Saturday worksheet. 3
9 Now you are ready to insert a formula with a reference to the number of seats sold for Sunday's performance.

In cell B7, insert a formula with a 3-D reference to cell C20 in the Sunday worksheet. 3
10 The next section of the Totals worksheet is designed to display total gross revenue by section.

In cell B11, insert the SUM function with a 3-D reference to calculate the total Orchestra Front gross revenue (cell G9) for the Friday, Saturday, and Sunday worksheets. 4
11 You are ready to insert a function to calculate the total Orchestra Back gross revenue.

In cell B12, insert the SUM function with a 3-D reference to calculate the total Orchestra Back gross revenue (cell G14) for the Friday, Saturday, and Sunday worksheets. 4
12 You are ready to insert a function to calculate the total gross revenue for the Balcony.

In cell B13, insert the SUM function with a 3-D reference to calculate the total Balcony gross revenue (cell G19) for the Friday, Saturday, and Sunday worksheets. 4
13 The worksheet contains a circular error. Now you want to find and correct it.

On the Totals worksheet, check for errors and correct the formula with the circular reference. 4
14 Although the Totals worksheet contains specified aggregated data, such as the number of seats sold and gross revenue by section, you want to create another summary that consolidates data from the three daily worksheets. In the Details worksheet, you will list the detailed section seating and gross revenue by day for those seating sections.

In the Details worksheet, in cell A2, use the Consolidate tool to select and add the range I10:J19 in the Friday, Saturday, and Sunday worksheets. Use the top row and left column labels when consolidating data. 10
15 Now you want to format the consolidated data.

Select the range B2:D2. Apply bold and center horizontal alignment. 1
16 The third section of the Totals worksheet needs to link to a value in another workbook.

Open the Exp22_Excel_Ch09_Cumulative_TheatreVenue.xlsx workbook. Go back to the Exp22_Excel_Ch09_Cumulative_Theatre.xlsx workbook. In cell B17 on the Totals worksheet, insert a link to the Weekend Evening value (cell C5) in the TheatreVenue workbook. Close the TheatreVenue workbook. 5
17 In cell B19 in the Totals worksheet, multiply the Weekend Evening Fee by the No. of Evening Performances. 3
18 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 just one input cell.

Select the range C6:C8 in the Friday worksheet, create a validation rule to allow whole numbers less than or equal to =B6. Enter the input message title Seats Sold and the input message Enter the number of seats sold. (including the period). Use the Stop alert with the error alert title Invalid Number and the error alert message The number of seats sold cannot exceed the total seats in the section. (including the period). Test the data validation by attempting to enter 360 in cell C7 and then cancel the change. Copy the range C6:C8 in the Friday worksheet. Select the range C11:C13. Use the Paste Special Validation to copy the validation settings. Select the range C16:C18 and paste the validation again. 15
19 You want to unlock data-entry cells so that the user can change the number of seats sold in the worksheets.

Group the Friday, Saturday, and Sunday worksheets. Select the ranges C6:C8, C11:C13, and C16:C18 and unlock these cells. 5
20 Now that you unlocked data-entry cells, you are ready to protect the worksheets to prevent users from changing data in other cells.

Individually, protect the Friday, Saturday, and Sunday worksheets using the default allowances with the password Expl0r!ng. 5
21 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. 0
22 Save and close Exp22_Excel_Ch09_Cumulative_Theatre.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100

Project 4: Ch10_CumulativeAssessment - Movie Rentals

Project Description:
You are the systems manager for Blue City Movie Rentals, and you have been asked to create a report on historical sales data. To complete your task, you will combine and edit data from multiple sources using Excel's Power add-ins, XML, and text functions.

Steps to Perform:

Step Instructions Points Possible
1 Start Excel. Download and open the file named Exp22_Excel_Ch10_CumulativeAssessment-MovieRentals.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 Use Get & Transform Data (Power Query Editor) to load the file e10c2Movies.txt to the existing worksheet starting in cell A1. Load the information to the worksheet and select Add this Data to the Data Model.

Note: On the Data tab, in the Get & Transform Data group, click Get Data, and then click Launch Power Query Editor. In the Power Query Editor, in the New Query group, click New Source, point to File, and then click Text/CSV. In the Power Query Editor, in the Close group, click Close & Load, then select Close & Load To... In the Import Data dialog box, select Existing Worksheet (=$A$1) and Add this Data to the Data Model. 10
3 Rename the worksheet Movies. 4
4 Use Get & Transform Data (Power Query Editor) to load the Account table from the xml file e10c2Contacts.xml into a new worksheet. Load the information to a new worksheet and select Add this Data to the Data Model.

Note: On the Data tab, in the Get & Transform Data group, click Get Data, and then click Launch Power Query Editor. In the Power Query Editor, in the New Query group, click New Source, point to File, and then click XML. In the Power Query Editor, in the Close group, click Close & Load, then select Close & Load To... In the Import Data dialog box, select New Worksheet and Add this Data to the Data Model. 10
5 Rename the worksheet Contacts. 4
6 Use Get & Transform Data (Power Query Editor) to load the Rates table from the file e10c2Rates.xlsx into a new worksheet. Load the information to a new worksheet and select Add this Data to the Data Model.

Note: On the Data tab, in the Get & Transform Data group, click Get Data, and then click Launch Power Query Editor. In the Power Query Editor, in the New Query group, click New Source, point to File, and then click Excel Workbook. In the Power Query Editor, in the Close group, click Close & Load, then select Close & Load To... In the Import Data dialog box, select New Worksheet and Add this Data to the Data Model. 10
7 Rename the worksheet Rates. 4
8 Use Get & Transform Data (Power Query Editor) to load the StoreInfo table located in the database e10c2Transactions.accdb into a new worksheet. Load the information to a new worksheet and select Add this Data to the Data Model.

Note: On the Data tab, in the Get & Transform Data group, click Get Data, and then click Launch Power Query Editor. In the Power Query Editor, in the New Query group, click New Source, point to Database, and then click Access. In the Power Query Editor, in the Close group, click Close & Load, then select Close & Load To... In the Import Data dialog box, select New Worksheet and Add this Data to the Data Model. 10
9 Rename the worksheet StoreInfo. 4
10 Use Get & Transform Data (Power Query Editor) to load the Transactions table located in the database e10c2Transactions.accdb in the Power Query Editor. Use the Power Query Editor to change the format of the Date column from Date and Time to Date (Click the Data Type arrow and select Date). Load the edited data to a new worksheet and select Add this Data to the Data Model.

Note: On the Data tab, in the Get & Transform Data group, click Get Data, and then click Launch Power Query Editor. In the Power Query Editor, in the New Query group, click New Source, point to Database, and then click Access. In the Power Query Editor, in the Close group, click Close & Load, then select Close & Load To... In the Import Data dialog box, select New Worksheet and Add this Data to the Data Model. 12
11 Rename the worksheet Transactions. 4
12 Create a relationship between the Account table Account field and Transactions table Account field. 5
13 Create a relationship between the StoreInfo table Store# and the Transactions table Store#. 5
14 Use PowerPivot to create a PivotTable and clustered column PivotChart Horizontal on a new worksheet. Name the worksheet TotalEarnings.

For the PivotTable use the Transactions table Date field in the Filters area, Account table Type field and Last field in the Rows area, and Transactions table Total field in the Values area.

For the PivotChart, use the Transactions table Date field in the Filters area, Account table Type field in the Axis area, , and Transactions table Total field in the Values area.

Note: In the Power Pivot for Excel window, on the Home tab, click the PivotTable arrow and select Chart and Table Horizontal. 14
15 Add the chart title Total Earnings. 4
16 Use 3D Map (Power Maps) to create a geographical visualization of sales data by store. Use the StoreInfo Zip as the location dimension and the Transactions Total as the height dimension. Zoom in as needed to view results. 0
17 Save and close Exp22_Excel_Ch10_CumulativeAssessment-MovieRentals.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100