Posted: November 29th, 2022

BOOKKEEPING FINAL PROJECT

BOOKKEEPING FINAL PROJECT
For this project, you complete a workbook for Blue Lake Sports by importing a text file and formatting data as an Excel table. You filter and sort data, create a PivotTable, build a PivotChart, and insert a sunburst chart. In addition, you name ranges and build formulas.
[Student Learning Outcomes: [1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.8, 2.1, 2.2, 2.3, 2.6, 2.7, 3.1, 3.3, 3.4, 3.7, 4.1, 4.3, 4.4, 4.6, 4.8]
Files Needed: BlueLake_Project2-Excel-ACP-2.xlsx and BlueLake_Project2.txt
Completed Project File Name: [your name]-BlueLake_Project2-Excel-ACP-2.xlsx
Skills Covered in this Project
• Open, rename, and save a workbook.
• Import a text file.
• Format data in a worksheet.
• Copy a worksheet and rename a tab.
• Create an Excel table.
• Sort and filter data in a table.
• Create a PivotTable.
• Create a PivotChart object.
• Add, edit, and format chart elements.
• Size and position a chart object.
• Create a sunburst chart sheet.
• Name a cell range.
• Use VLOOKUP in a formula.
• Create a formula with multiple mathematical operations.
• Build a SUMPRODUCT formula.
• Set document properties and page setup options.
1. Open the BlueLake_Project2-Excel-ACP-2 Excel workbook start file. If the document opens in Protected View, click the Enable Editing button so you can modify it.
2. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.

FIGURE 1TEXT FILE IMPORTED AS TABLE
3. Import the tab-delimited BlueLake_Project2.txt file and load it to cell A19 in Sheet1. Verify that the Add this data to the Data Model box in the Import Data dialog box is not selected before you click OK. The data imports in an Excel table and repositions the existing data (Figure 1).
4. Select cells A20:B33 and copy them to the Clipboard.
5. Select cell C19 and open the Paste gallery [Home tab, Clipboard group]. Choose Formulas to paste the data without formatting. Press Esc to cancel the moving border.
6. Select and delete columns A:B and close the Queries and Connections pane.
7. Apply formats to cells in Sheet1.
a. Select cells A4:C60 and format the cells as an Excel table with Blue, Table Style Medium 2.
b. Select cell A1:A3 and set the font size to 14 pt.
c. Set the width of each of columns A:C to 15.00 (110 pixels).
d. Select cells A1:C3, click the Alignment launcher [Home tab], and apply the Center Across Selection command.
e. Press Ctrl+Home.
8. Rename the worksheet as Transactions.

FIGURE 2DATA IS FILTERED AND DISPLAYS A TOTAL
9. Copy and rename a worksheet.
a. Right-click the Transactions tab name make a copy of the sheet at the end of the tabs.
b. Name the copied sheet as Filtered.
c. Make another copy of the Transactions sheet at the end and name it SunburstData.
10. Sort and filter data.
a. Select the Filtered worksheet tab.
b. Sort the data in ascending order by Department.
c. Show the Total row for the table.
d. Filter the table to show only the Bike & Skate data (Figure 2).
11. Create a PivotTable.
a. Select the Transactions worksheet tab.
b. Select cell A5 and create a blank PivotTable layout on a new worksheet.
c. Show the Department, City, and Transactions fields in the PivotTable.
d. In the PivotTable Fields task pane, arrange the City field in the Filters area, the Department field in the Rows area, and the Transactions field in the Values area (Figure 3).
e. Rename the PivotTable sheet as PivotTable.

FIGURE 3CITY FIELD IS A FILTER
12. Create and format a Clustered Column PivotChart.
a. Select cell A4 in the PivotTable and insert a Clustered Column PivotChart.
b. Position and size the chart object to start at cell D2 and reach to cell M18.
c. Select one of the columns in the PivotChart and click the Format Selection button [PivotChart Tools Format tab, Current Selection group].
d. Find and expand the Fill command group and select the Vary colors by point box.
e. Close the Format Data Series task pane.
f. Click the Total title box in the chart and edit the text to display # of Transactions by Department.
g. Select cell A20 (Figure 4).

FIGURE 4PIVOTTABLE AND ITS CHART
13. Create and format a sunburst chart.
a. Select the SunburstData tab name.
b. Select cell B4. With the move pointer, drag cell B4 to cell A4 to rearrange the columns so that the City column is column A. The top level in a hierarchy chart should be leftmost in the data.
c. Select cell A5 and insert a Sunburst chart on its own sheet named SunburstChart.
d. Edit the Chart Title placeholder text to display # of Transactions by Department. The city names are the inner ring of the chart, the top level in the hierarchy (Figure 5).

FIGURE 5SUNBURST CHART
14. Name a cell range and insert a column.

a. Select the Lookup_Data tab name.

FIGURE 6VLOOKUP ARGUMENTS
b. Select cells A2:B5 and name the range TaxRates. Note that the first column of data is sorted in ascending order.
c. Select the TransactionData sheet and insert a column at column C.
d. Select cell C5 and type Tax Rate.
15. Build a VLOOKUP formula.
a. Select cell C6 on the TransactionData tab.

FIGURE 7MISSING PARENTHESIS IS FOUND
b. Build a VLOOKUP formula to lookup the value in cell A6 in the TaxRates range and display the tax rate. The range_lookup argument is not necessary because the data in the Table_array is sorted (Figure 6).
c. Copy the VLOOKUP formula in column C and format the results as Percent Style with 2 decimals.
16. Use order of precedence in a formula.
a. Select cell D6 on the TransactionData tab.
b. Type = to start a formula and click cell B6.
c. Type *( to multiply and enter the opening parenthesis.
d. Type 1+ and click cell C6. The sales amount is multiplied by 1 plus the tax rate to calculate the total bill.
e. Press Enter. The missing parenthesis is noted.
f. Click Yes (Figure 7).
g. Copy the formula to complete the data.

FIGURE 8EDITED FORMULA TO CALCULATE TOTAL BY CARD TYPE
17. Complete borders.
a. Preview the worksheet.
b. Select cell A4 and apply a Left Border.
c. Apply a Right Border to cell D4.
d. Press Ctrl+Home and preview the worksheet again.

FIGURE 9PROPERTIES DIALOG BOX
18. Use SUMPRODUCT to calculate fees by location.
a. Select the CardFees tab name and select cell C15. The formula multiplies the fee times the number of transactions for each type of card.
b. Click the Math & Trig button [Formulas tab, Function Library group] and select SUMPRODUCT.
c. Select cells C7:C10 for the Array1 argument and press F4 (FN+F4).
d. Click the Array2 box, select cells D7:D10 for the Atlanta values as a relative reference, and click OK.
e. Copy the formula to cells D15:F15.
f. Format cells C15:F15 as Accounting Number Format with zero decimal places.
19. Use order of precedence and relative references in a formula.
a. Select cell E22. The formula multiplies the sum of cells D7:G7 times the fee for the card.
b. Click the Math & Trig button [Formulas tab, Function Library group] and select SUM.
c. Select cells D7:G7 for the Number1 argument and click OK.
d. Edit the formula to multiply the results by cell C7 for the White Card fee (Figure 8). The sum is calculated first, and the result is multiplied by the value in cell C7.
e. Copy the formula to cells E23:E25.
f. Format cells E22:E25 as Accounting Number Format with zero decimal places.
g. Select cells D7:G10 and apply Comma Style with zero decimal places.
h. Press Ctrl+Home.
20. Finalize the workbook by setting page options and document properties.
a. Open the Properties dialog box [File tab, Info group].
b. Type Transactions Data in the Title box; type your name in the Author box.
c. Click the Comments box, type First week of September, and return to the workbook (Figure 9).
====
To complete the bookkeeping final project for Blue Lake Sports, follow these steps:

Open the start file “BlueLake_Project2-Excel-ACP-2.xlsx” and enable editing if necessary.
Import the text file “BlueLake_Project2.txt” and format it as a table in Sheet1.
Copy cells A20:B33 to Sheet1 and paste as formulas in C19.
Format the table, rename the worksheet to “Transactions”.
Make copies of the Transactions worksheet and name the sheets “Filtered” and “SunburstData”.
Sort and filter the data in the Filtered worksheet to show only “Bike & Skate” data.
Create a PivotTable in the Transactions worksheet, arrange the fields, and rename the worksheet to “PivotTable”.
Insert a Clustered Column PivotChart in the PivotTable and format it.
Create and format a sunburst chart in the SunburstData worksheet.
Name cell ranges and use VLOOKUP and SUMPRODUCT formulas.
Set document properties and page setup options.
d. Select the PivotTable sheet tab. Change the page orientation to landscape and scale the sheet to fit a single page.
e. Select the Transactions sheet tab and scale it to fit a single page.
21. Save and close the workbook (Figure 10).

Order | Check Discount