Excel for Accounting Multi-Chapter Project Chapters 47 In this project, you will use skills and procedures presented in Chapters 47 together as you complete accounting-related tasks for CraftWorld. Follow all instructions and use the approaches presented in the chapters to complete the requirements in good form. Submit your work per your instructors directions. Robert Rosenberg has been a fixture at CraftWorld in Syosset, NY, where he has worked (and often painted, much to the delight of customers) for more than ten years. He was recently selected as a contestant on the nationally syndicated reality competition So You Think You Can Paint! and abruptly quit his job at CraftWorld to move to Los Angeles, CA, and pursue his dream. Arnold Norris, owner of CraftWorld (and your boss), has asked you to assume some of Roberts previous responsibilities. To begin, as of January 31, 2020, Arnold would like you to perform an inventory analysis and complete a bank reconciliation, a depreciation schedule, and a payroll register. Unfortunately, because Arnold is unable to locate most of Roberts files, you will have to complete much of this work from scratch. Project Requirements Requirement #1. Use the EA-MCP-Inventory file to perform an inventory analysis. (Chapter 4) Use a function to enter the total number of product categories in the appropriate location. For each product category, use functions to enter the total number of years for which an inventory balance is displayed (include zero balances) and the average, maximum, and minimum inventory balances in appropriate locations. Then, use a function to enter the total inventory for each of the five years in the appropriate locations. Determine the fifth-year inventory balance for Artist Canvas using the LIFO, FIFO, and weighted average methods (use formulas to create the names of each of the three methods in appropriate locations). Use formulas to determine the inventory balance under each method. Ensure the calculations are entered in appropriate locations on the Artist Canvas tab; label each calculation. Use a formula to enter the LIFO inventory balance from the Artist Canvas tab on the Inventory Listing tab where it is missing. Create a PivotTable that displays the total Year 4 inventory balances (on a per- popularity level basis) for products in store location Section A with a customer popularity level of 3 or higher. Include the same for Section D within the PivotTable as well. Label the new worksheet tab appropriately and use slicers to create the PivotTable. Create a PivotChart displaying the results of the PivotTable, using appropriate labeling and formatting throughout. Position the PivotChart and slicers in a logical manner. Microsoft Excel for Accounting, 2nd Edition Page 1 of 3

Chapters 47 Project Requirement #2. Complete a bank reconciliation (Chapter 5) based on this information: Deposits in Transit: $800 and $1,100 NSF Check: $200 Notes Receivable: $4,000 Outstanding Checks: $1,300 and $2,000 Bank Charges: $100 When recording check #206 for $5,000, the company erroneously reduced the cash balance by $1,200. The bank statement reflects a 1/31/20 balance of $18,300, while the companys general ledger reflects a 1/31/20 cash balance of $17,000. Modify the appearance of the reconciliation using Format Painter and Quick Styles. Set a conditional formatting rule that will highlight the beginning bank balance if it exceeds $16,000. Apply a header that displays the current date and a footer that displays the filename. Ensure that all column widths, row heights, and font sizes are appropriately adjusted. Requirement #3. Complete a depreciation schedule to enter fixed-asset data in an Excel table, calculate depreciation expense, and analyze the table data. (Chapter 6) Use this information, provided as of 1/31/2019, to create the depreciation schedule as of 1/31/2020 using appropriate headers and a table in which all pertinent information is displayed: Fixed Asset Date of Acquisition Cost Salvage Value Useful Life (years) Accumulated Depreciation Automobile 1/31/2014 $32,000 $2,000 10 $15,000 Store Fixtures 1/31/2018 $11,300 $1,300 5 $2,000 Office Equipment 1/31/2015 $6,900 $900 6 $4,000 CraftWorld uses the straight-line method of depreciation. In addition to the fixed assets shown, the company acquired new furniture on 1/31/2019. It cost $3,100, has an estimated four-year useful life, and an estimated salvage value of $300. Create the table so each of the six categories shown above is displayed in a separate column (note that some figures may appear different in your 1/31/2020 table than in the 1/31/2019 chart). Include columns for the annual depreciation expense and year-end book value. Use a function to calculate depreciation expense and use structured references when calculating the year-end book value. Sort the table by accumulated depreciation in ascending order and include a total row to sum each column (except Salvage Value; display the average for that column). Filter the table to display only assets for which the cost is greater than Microsoft Excel for Accounting, 2nd Edition Page 2 of 3

Chapters 47 Project $5,000. Last, use Quick Analysis to apply data bars to the figures in the Salvage Value column. (Hint: Begin by highlighting the figures for which you will display the data bars.) Requirement #4. Using the EA7-E1-Payroll file, finish the partially completed payroll register to enter earnings, deductions, and net pay data. (Chapter 7) Modify the payroll register to ensure optimal layout and formatting. Use an IF function to populate the Overtime Earnings and Total Earnings columns so that N/A is displayed in the Overtime Earnings column for employees who did not work overtime hours during the period. Populate the FWT and SWT columns using these figures: Employee Name FWT SWT Check Number Alarcon, G $20 $14 402 Foreman, B $11 $8 403 Salinger, F $16 $10 404 Owens, T $23 $16 405 Willis, S $21 $7 406 Create a nested IF function to calculate Social Security tax and a separate formula to calculate Medicare tax for each employee. Use absolute cell references where appropriate. Use the appropriate LOOKUP function to calculate the charitable contributions and medical plan payment amounts for each employee. The percentages in the range C25:D29 represent the percent of total earnings contributed to the respective voluntary withholdings; use these in your LOOKUP formulas. Also use an appropriate defined name for the table array referenced in your LOOKUP formulas. Complete the payroll register by entering the check numbers, creating a formula to calculate each employees net pay, and creating formulas to total columns where necessary. Adjust the worksheet layout by adding borders around distinct sets of data, adjusting column widths and/or row heights between sets of data, and minimizing blank space between data sets. Last, use appropriate formatting to distinguish the headers in rows 68 from the data below and conditional formatting to highlight the two smallest Net Pay amounts.