Description Apply yours skills, chapter 5, ea5-a2 & chapter 6, ea6-a2. Complete the spreadsheets in Excel 4 attachmentsSlide 1 of 4attachment_1attachment_1attachment_2attachment_2attachment_3attachment_3attachment_4attachment_4.slider-slide > img { width: 100%; display: block; } .slider-slide > img:focus { margin: auto; } Unformatted Attachment Preview EA5-A2 Create a Bank Reconciliation for Tasters Club Corp. In this exercise, you will create a bank reconciliation for Tasters Club Corp. for the month ended December 31, 2016. The reconciliation should be partly based on these figures: Bank Statement Balance (12/31/2016) equals $16,200; Notes Receivable equals $395; NSF Check equals $4,000; Bank Charges equals $550. During the month, the bank erroneously deposited a $505 check written to Pepper Products into the bank account of Tasters Club Corp. 1. Open a Blank Workbook and save the file in your Chapter 05 folder as: EA5-A2-BankRec-[YourName] 2. Enter the bank reconciliation headers in the range A1:A3; apply bold formatting. 3. Apply the Merge & Center command (from columns A-G) to each row in this range. 4. Using examples from the chapter, enter descriptions for the initial bank and book balances in appropriate locations. Type the Add: and Deduct: labels in the proper cells in rows 6 and 9. 5. Set the width of columns A and E to 24 and the width of column D to 1. 6. Enter the bank statement balance in the appropriate location. 7. Open EA5-A2-QBReport from your Chapter 05 folder and arrange the window next to the bank reconciliation window. 8. Type Outstanding Checks in an appropriate location in the bank reconciliation. Apply Your Skills 153 9. Drag and drop each outstanding check as well as the book balance from the QuickBooks report to the appropriate cells in columns B and G of the bank reconciliation 10. Copy the book balance in the bank reconciliation and use Paste Values in the same cell to override the existing formula with the value itself. 11. Close the QuickBooks report without saving. 12. If necessary, insert row(s) in the Add: section of the bank reconciliation to account for all items that will appear there. 13. Import the deposits in transit from EA5-A2-Deposits in your Chapter 05 folder to the proper location in the bank reconciliation. (Hint: Use the Data tab.) 14. Type a description for the imported amounts in the proper cell of column A. Use Format Painter and Quick Styles 15. Type the descriptions and amounts for the Notes Receivable, NSF Check, Bank Charges, and Check Error in the appropriate locations. 16. In the appropriate location, enter a formula to sum the items in the Add: section of the bank side of the reconciliation. 17. Sum the items in the Deduct: section of the bank side of the reconciliation by copying the previous formula and pasting it to the appropriate cell. If necessary, modify the copied formula. 18. If necessary, sum the items in the Deduct: section on the book side of the reconciliation by inserting appropriate formulas. 19. In the appropriate row, type a description for the final figures on each side of the bank reconciliation. Hint: Create two entries. 20. In the appropriate locations, enter formulas to calculate all remaining necessary figures on the bank side and book side of the reconciliation. 21. Apply the Currency format with zero decimal places to the beginning balance on the bank side and then use Format Painter to apply the formatting to all other dollar amounts. 22. Adjust column widths so all information is appropriately displayed. 23. Apply a bottom border to cells below which a calculation is completed and apply a bottom double border to cells that represent the final calculation in a column. 24. Apply the Heading 4 Quick Style to the descriptions for the beginning and adjusted balances on each side of the reconciliation. Hint: Apply the setting to four cells. Finalize the Bank Reconciliation 25. Create a new conditional formatting rule to the beginning bank statement balance such that the cell will contain a yellow fill color and blue text if it drops below $20,000. 26. Insert the Current Time code in the right section of the header and the Current Date code in the left section. 27. Insert the File Path code in the left section of the footer. 28. Save and close your file. our le EA6-A2 Complete a Depreciation Schedule for Furniture Resellers In this exercise, you will create a depreciation schedule for Furniture Resellers as of 12/31/2016 using an Excel table. You will then sort, filter, and analyze the data in the table. These fixed assets, with associated data as of 12/31/2015, were acquired prior to the current year. Cost Salvage Value Useful Life (years) Accumulated Depreciation $6,750 $8,200 10 $700 Fixed Asset Date of Acquisition Machinery 1/1/2007 Garage 1/1/2009 Equipment Computers 1/1/2013 $8,400 $11,000 9 $200 $2,400 $4,000 $0 5 Furniture Resellers also acquired lobby furniture on 1/1/2016. The lobby furniture had a cost of $2,000, an estimated salvage value of $400, and an estimated useful life of eight years. 1. Open a Blank Workbook and save the file in your Chapter 06 folder as: EA6-A2-Depreciation-[YourName) 2. Enter appropriate bank reconciliation headers in the range A1:A2; apply italic formatting 3. Enter the fixed-asset data in the range A4:F7; for the accumulated depreciation, enter the title but not the dollar amounts. Apply Your Skills 185 4. Format the range A4:F7 as a table using Blue, Table Style Dark 6 and add Depreciation _ Schedule as the table name. 5. Add a total row to the table that shows sums in columns C:D and an average in column E 6. Turn on the First Column and Last Column table style options. Adjust Table Rows and Columns 7. From within cell F7, extend the table to add one additional row and enter all fixed-asset information for the newly acquired asset. 8. Add two columns to the far right of the table with the headers Depreciation Expense and Year-End Book Value, in that order. 9. Wrap the text in the headers. 10. Apply appropriate column widths and, if necessary, autofit the header row height. 11. Change the orientation of the worksheet to Landscape. Use Depreciation Functions 12. Use an Excel function to calculate depreciation expense for each fixed asset under the sum of the years’ digits method. Hint: Turn off calculated columns when entering the formulas for each fixed asset. 13. Replace the formula with one that uses an Excel function to calculate depreciation expense for each fixed asset under the straight-line depreciation method. Use calculated columns so you can enter the formula in a single row and have it automatically extend to all other fixed assets. 14. Use structured references to calculate the accumulated depreciation for each fixed asset. 15. Use structured references with calculated columns to calculate the year-end book value for each fixed asset. 16. Use the Sum function on columns F:H. Sort and Filter a Table 17. Sort the data based on accumulated depreciation from smallest to largest. 18. Filter the table to not display fixed assets with a salvage value of $200. 19. Clear all filters; apply a new filter that displays only fixed assets with a useful life greater than six years. Create a Chart Using Quick Analysis 20. Use Quick Analysis to create a bar chart displaying the year-end book value for each fixed asset presently displayed in the table. Position the chart below the table. 21. Apply the Accounting number format with zero decimal places to all dollar amounts 22. Save and close your file. Purchase answer to see full attachment Tags: Outstanding checks Adjusted bank balance Deposits in transit Beginning bank balance Bank charges User generated content is uploaded by users for the purposes of learning and should be used following Studypool’s honor code & terms of service.

Description

Apply yours skills, chapter 5, ea5-a2 & chapter 6, ea6-a2. Complete the spreadsheets in Excel

4 attachmentsSlide 1 of 4attachment_1attachment_1attachment_2attachment_2attachment_3attachment_3attachment_4attachment_4.slider-slide > img { width: 100%; display: block; }
.slider-slide > img:focus { margin: auto; }

Unformatted Attachment Preview

EA5-A2 Create a Bank Reconciliation for Tasters Club Corp.
In this exercise, you will create a bank reconciliation for Tasters Club Corp. for the month ended
December 31, 2016. The reconciliation should be partly based on these figures: Bank Statement
Balance (12/31/2016) equals $16,200; Notes Receivable equals $395; NSF Check equals $4,000;
Bank Charges equals $550. During the month, the bank erroneously deposited a $505 check
written to Pepper Products into the bank account of Tasters Club Corp.
1. Open a Blank Workbook and save the file in your Chapter 05 folder as:
EA5-A2-BankRec-[YourName]
2. Enter the bank reconciliation headers in the range A1:A3; apply bold formatting.
3. Apply the Merge & Center command (from columns A-G) to each row in this range.
4. Using examples from the chapter, enter descriptions for the initial bank and book
balances in appropriate locations. Type the Add: and Deduct: labels in the proper cells
in rows 6 and 9.
5. Set the width of columns A and E to 24 and the width of column D to 1.
6. Enter the bank statement balance in the appropriate location.
7. Open EA5-A2-QBReport from your Chapter 05 folder and arrange the window next to
the bank reconciliation window.
8. Type Outstanding Checks in an appropriate location in the bank reconciliation.
Apply Your Skills 153
9. Drag and drop each outstanding check as well as the book balance from the QuickBooks
report to the appropriate cells in columns B and G of the bank reconciliation
10. Copy the book balance in the bank reconciliation and use Paste Values in the same cell to
override the existing formula with the value itself.
11. Close the QuickBooks report without saving.
12. If necessary, insert row(s) in the Add: section of the bank reconciliation to account for all
items that will appear there.
13. Import the deposits in transit from EA5-A2-Deposits in your Chapter 05 folder to the
proper location in the bank reconciliation. (Hint: Use the Data tab.)
14. Type a description for the imported amounts in the proper cell of column A.
Use Format Painter and Quick Styles
15. Type the descriptions and amounts for the Notes Receivable, NSF Check, Bank Charges,
and Check Error in the appropriate locations.
16. In the appropriate location, enter a formula to sum the items in the Add: section of the
bank side of the reconciliation.
17. Sum the items in the Deduct: section of the bank side of the reconciliation by copying
the previous formula and pasting it to the appropriate cell. If necessary, modify the
copied formula.
18. If necessary, sum the items in the Deduct: section on the book side of the reconciliation
by inserting appropriate formulas.
19. In the appropriate row, type a description for the final figures on each side of the bank
reconciliation.
Hint: Create two entries.
20. In the appropriate locations, enter formulas to calculate all remaining necessary figures
on the bank side and book side of the reconciliation.
21. Apply the Currency format with zero decimal places to the beginning balance on the bank
side and then use Format Painter to apply the formatting to all other dollar amounts.
22. Adjust column widths so all information is appropriately displayed.
23. Apply a bottom border to cells below which a calculation is completed and apply a bottom
double border to cells that represent the final calculation in a column.
24. Apply the Heading 4 Quick Style to the descriptions for the beginning and adjusted
balances on each side of the reconciliation.
Hint: Apply the setting to four cells.
Finalize the Bank Reconciliation
25. Create a new conditional formatting rule to the beginning bank statement balance such
that the cell will contain a yellow fill color and blue text if it drops below $20,000.
26. Insert the Current Time code in the right section of the header and the Current Date
code in the left section.
27. Insert the File Path code in the left section of the footer.
28. Save and close your file.
our le
EA6-A2 Complete a Depreciation Schedule for Furniture
Resellers
In this exercise, you will create a depreciation schedule for Furniture Resellers as of 12/31/2016
using an Excel table. You will then sort, filter, and analyze the data in the table. These fixed assets,
with associated data as of 12/31/2015, were acquired prior to the current year.
Cost
Salvage
Value
Useful Life
(years)
Accumulated
Depreciation
$6,750
$8,200
10
$700
Fixed Asset Date of
Acquisition
Machinery 1/1/2007
Garage
1/1/2009
Equipment
Computers 1/1/2013
$8,400
$11,000
9
$200
$2,400
$4,000
$0
5
Furniture Resellers also acquired lobby furniture on 1/1/2016. The lobby furniture had a cost of
$2,000, an estimated salvage value of $400, and an estimated useful life of eight years.
1. Open a Blank Workbook and save the file in your Chapter 06 folder as:
EA6-A2-Depreciation-[YourName)
2. Enter appropriate bank reconciliation headers in the range A1:A2; apply italic
formatting
3. Enter the fixed-asset data in the range A4:F7; for the accumulated depreciation, enter
the title but not the dollar amounts.
Apply Your Skills 185
4. Format the range A4:F7 as a table using Blue, Table Style Dark 6 and add
Depreciation _ Schedule as the table name.
5. Add a total row to the table that shows sums in columns C:D and an average in
column E
6. Turn on the First Column and Last Column table style options.
Adjust Table Rows and Columns
7. From within cell F7, extend the table to add one additional row and enter all fixed-asset
information for the newly acquired asset.
8. Add two columns to the far right of the table with the headers Depreciation
Expense and Year-End Book Value, in that order.
9. Wrap the text in the headers.
10. Apply appropriate column widths and, if necessary, autofit the header row height.
11. Change the orientation of the worksheet to Landscape.
Use Depreciation Functions
12. Use an Excel function to calculate depreciation expense for each fixed asset under the
sum of the years’ digits method.
Hint: Turn off calculated columns when entering the formulas for each fixed asset.
13. Replace the formula with one that uses an Excel function to calculate depreciation
expense for each fixed asset under the straight-line depreciation method. Use calculated
columns so you can enter the formula in a single row and have it automatically extend to
all other fixed assets.
14. Use structured references to calculate the accumulated depreciation for each fixed asset.
15. Use structured references with calculated columns to calculate the year-end book value
for each fixed asset.
16. Use the Sum function on columns F:H.
Sort and Filter a Table
17. Sort the data based on accumulated depreciation from smallest to largest.
18. Filter the table to not display fixed assets with a salvage value of $200.
19. Clear all filters; apply a new filter that displays only fixed assets with a useful life greater
than six years.
Create a Chart Using Quick Analysis
20. Use Quick Analysis to create a bar chart displaying the year-end book value for each
fixed asset presently displayed in the table. Position the chart below the table.
21. Apply the Accounting number format with zero decimal places to all dollar amounts
22. Save and close your file.

Purchase answer to see full
attachment

Tags:
Outstanding checks

Adjusted bank balance

Deposits in transit

Beginning bank balance

Bank charges

User generated content is uploaded by users for the purposes of learning and should be used following Studypool’s honor code & terms of service.