Question

Excel functions and tools used:

            SLN function                ROUND function          Page Layout

            VDB function               Cell referencing

                        IF function                   Formatting

           

Some good friends of yours just opened ABC Company, a small home business, on 1/1/19. They’re using QuickBooks to track their transactions. They have a calendar year end.

They bought a computer, a printer and a used Camry. They also purchased a cargo box for the Camry so they could carry tools to the job site. (The cargo box is permanently attached to the vehicle.) These assets are only being used in the business (no personal use). They would like you to help them with the depreciation calculations. They’ve given you the following information

Asset	Date placed in service	Cost	Expected service life (in years)	Expected salvage value
Computer	1/1	$2,235	3	$0
LaserJet Copier/Printer	1/1	2,110	5	$0
2011 Camry	1/1	$16,965	4	$1,500
Cargo Box for the Camry	1/1	$595	4	$200

Required:

1. Create a workbook with the name DEPN_YOUR NAME. (Use your own first initial and last name.)
2. For each asset (the Camry and cargo box count as one asset), calculate depreciation expense, accumulated depreciation, and net book value using both straight-line and double declining balance methods for each year of its expected life. No decimals.
   1. Use a separate worksheet for each asset.
   2. You can put both depreciation methods on the same worksheet.
3. Prepare the worksheets so that each one could be copied and used for assets with lives up to 5 years. (Assume that depreciation is calculated annually.)
4. You must use IF, SLN and VDB functions.
5. Your worksheets should be formatted in a professional way, to include a report header.
6. Each worksheet should fit to one page using portrait orientation.

Choose one

      Option A: On a separate worksheet, determine DDB depreciation for the Camry assuming it was placed in service on 2/1. Use the VDB function. (Ignore the cargo box.)

      Option B: On a separate worksheet, prepare an amortization schedule assuming they put 10% down on the Camry and financed the balance over two years (monthly payments) at an annual interest rate of 5%.

Answer 1

Wrap Text Currency Calibri B TU 11A A E E Merge & Center - $ -% Ba Copy- Format Painter Clipboard C7 Conditional Format Cell Formatting as Table Styles Styles Alignment Number Font f =VDB(G7,48,49,87-1,B7) A B C D Click to add header VDB ( cost, salvage, life, period, factor) Calculated depreciation wih varible double-decling method Year Depreciatio Value 0 $2,235.00 1 $1,490.00 $745.00 2 $745.00 $0.00 $0.00 Cost $2,235.00 Salvage0 Life $745.00 0 Calculated depreciation with staright line method Depreciation cost-salvage value life of the asset $2,235.00 $745.00 LASER JET PRINTER 2011 CAMRY AND CARGO BOX COMPUTER Page: 1 of 1 eady

Wrap Text Currency Paste Calibri Da Copy : Format Painter B IU Clipboard Font B6 f =VDB(F6,F7, F8,A6-1,A6) Merge & Center $ % ... Conditional Format Formatting as Table Styles Alignment Number B НІ Click to add header VDB ( cost, salvage, life, period, factor) Calculated depreciation wih varible double-decling method Year Depreciation Value $2,110.00 $1,266.00 $316.50 $0.00 $2,110.00 $1,266.00 $316.50 $0.00 $844.00 $949.50 $316.50 Cost Salvage NM + in Life mino 9999 Calculated depreciation with staright line method Depreciation cost-salvage value life of the asset $2,110.00 $422.00 COMPUTER LASER JET PRINTER 2011 CAMRY AND

1 A * cut Da Copy Format Painter Clipboard Calibri B I -u - A A Wrap Text Merge & Center - Currency $ % Paste U - , & Conditional Forma Formatting as Table Styles Number B6 Font Alignment f =VDB(F6,F7,F8, A6-1,A6) 2T3T cDE Header VDB ( cost, salvage, life, period, factor) Calculated depreciation wih varible double-decling method Year Depreciation Value 0 $17,560.00 1 $8,780.00 $8,780.00 $7,080.00 $1,700.00 Cost Salvage Life $17,560.00 $8,780.00 1700 - 1700 $1,700.00 1700 2 1700 BEEG SELVA Calculated depreciation with staright line method Depreciatis = cost-salvage value = life of the asset = 17560-1700 20 21 = $15,860.00 = $3.172.00 LASERJET PRINTER 2011 CAMRY AND CARGO BOX COMPUTER
we choose option A to solve this problem