PROJECT: SPREADSHEET APPLICATIONS FOR

BOOKKEEPING

The Bernards had quite a successful year in their newly opened Pennsylvania

store.

Matt and Julie Bernard have engaged their bookkeeper, Barb Burke, to

prepare the shop's Trial Balance, Net Income Statement, and Balance Sheet, which

will be forwarded to their CPA.

The Pennsylvania shop will use the same Chart of Accounts set-up in Practice Set

1. You'll notice one or two accounts have been added for this nal project.

The shop has been open for eight months. The grand opening was on November

1st of last year. The store's scal year will be July 1st-June 30th.

Barb Burke calculated the shop's unadjusted trial balance sheet in May after the

store had ocially closed for the season.

There are ve separate worksheets for you to complete in this project. The

worksheets are in an Excel format.

The cells highlighted in light yellow are input

cells

. If you don't have Excel, you'll need to manually calculate the worksheets.

Be sure to read the all the instructions for this project

and review the worksheets

before you begin your work.

Worksheet Overview

1.

Use the Adjusted Trial Balance WorksheetProject, Part 1 (tab 1).

The unadjusted balances have been inserted for your convenience.

Enter the adjusting entries. (Specic information relating to these

entries can be found below.)

Calculate the Adjusted Trial Balance. This should be similar to a

simplied version shown on page 178 in your textbook.

Input from the Adjusted Trial Balance, the Balance Sheet, and the

Income Statement. This should be similar to that shown on page 184

in your textbook.

2.

Use Project, Part 2Aggregation of Income Account Worksheet (tab 2).

Aggregate the Income Statement Accounts. This should be similar

to that shown on pages 211 and 212 in your textbook, although the

format is slightly dierent.

If you'll be completing

the project without

spreadsheet software, you

can access PDF copies of

the required worksheets

in the Resources tab in

the left-hand menu.

var isIE = false; var f1 = [['t1_1',607],['t3_1',1528],['t5_1',1462],['t7_1',1340],['t8_1',1557],['t9_1',579],['ta_1',1537],['tb_1',1407],['tc_1',1542],['td_1',1193],['te_1',1513],['tf_1',772],['tg_1',1408],['th_1',648],['ti_1',812],['tk_1',1402],['tl_1',954],['tm_1',485],['tn_1',508],['to_1',598],['tq_1',1239],['ts_1',1277],['tu_1',1245],['tv_1',542],['tx_1',1191],['ty_1',1061],['t10_1',1236],['t11_1',1309],['t12_1',326],['t14_1',1359],['t16_1',1258],['t17_1',1293],['t18_1',500],['t19_1',427],['t1a_1',371],['t1b_1',502],['t1c_1',470],['t1d_1',466],['t1e_1',432],['t1f_1',381]]; function load1(){ var timeout = 100; if (navigator.userAgent.match(/iPhone|iPad|iPod|Android/i)) timeout = 500; setTimeout(function() {adjustWordSpacing(f1);},timeout); } 100

STATEMENTS, CONTROLS, AND RECORDS

3.

Use Project, Part 3, to prepare the company's Income Statement and

Vertical Analysis Worksheet (tab 3).

Prepare an Income Statement and vertical analysis for the eight-

month period ending June 30th. This should be similar to that shown

on page 216 in your textbook.

4.

Use Project, Part 4Aggregated Balance Sheet Worksheet (tab 4).

Aggregate the Balance Sheet Accounts. This should be similar to that

shown on pages 198 and 199 in your textbook; again, the format is

slightly dierent.

5.

Use Project, Part 5Balance Sheet and Key Ratio Worksheet (tab 5).

Prepare the Balance Sheet for the eight-month period ending June

30th. Also, calculate the current ratio, quick ratio, and debt-to-equity

ratio for the Mile High Ski and Snowboard Shop.

Part 1: Adjusted Trial Balance

Barb has found the following adjusting entries that need to be made to the

Unadjusted Trial Balance. Prepare the Adjusting Journal Entries, and enter them

onto the Adjusting Entries column of the Adjusted Trial Balance worksheet.

Denote the entry number of each adjustment in the entry # column.

Note: Some

items won't require an adjusting entry.

Read each item carefully.

1.

Business insurance was purchased in November for $12,000, which

covers a 12-month period. An entry needs to be made between prepaid

and insurance expense. This entry has been done for you and should be

used as an example of how the rest of the entries should be completed.

2.

Depreciation needs to be calculated for all of the xed-asset accounts.

Computer equipment, computer software, and furniture and xtures

will be depreciated using the straight-line method over 60 months.

Machinery will be depreciated over 84 months using the straight-line

method of accounting.

3.

An entry was made incorrectly recording the Note Payable interest

and principal payments to the Other Accrued Liability account. The

entry

should have been made to

Interest Expense and the Notes Payable

accounts. The loan has a monthly payment of $1001.61. The shop

has paid $2,439 in interest and $5,565 in principal over the past eight

months.

A.

The note was used to purchase a customer list. The asset will have a

life of 10 years, and it should be amortized evenly over the 10-year

period. You'll need to record the amortization.

var isIE = false; var f2 = [['t2_2',607],['t4_2',1300],['t5_2',664],['t7_2',1223],['t8_2',1318],['t9_2',575],['tb_2',1253],['td_2',1310],['te_2',1272],['tf_2',322],['th_2',1286],['tj_2',1274],['tk_2',1305],['tl_2',917],['tm_2',593],['tn_2',1432],['to_2',1513],['tp_2',1428],['tq_2',1300],['tr_2',210],['ts_2',699],['tt_2',474],['tv_2',1274],['tw_2',1370],['tx_2',1367],['ty_2',1363],['t10_2',1334],['t12_2',1309],['t13_2',1278],['t15_2',1319],['t16_2',437],['t18_2',1269],['t19_2',1278],['t1b_2',483],['t1c_2',753],['t1d_2',1243],['t1e_2',1315],['t1h_2',1285],['t1i_2',1264],['t1j_2',876]]; function load2(){ var timeout = 100; if (navigator.userAgent.match(/iPhone|iPad|iPod|Android/i)) timeout = 500; setTimeout(function() {adjustWordSpacing(f2);},timeout); }

Show entire document STATEMENTS, CONTROLS, AND RECORDS

101

B.

The loan has a monthly payment of $1,001.61. The short-term

portion of the loan (the next 12 months of payments) needs to be

reclassed from account 400-Note Payable to account 335-Short-

Term Portion of Notes Payable.

C.

As stated above, the original interest expense of $2,439 was

incorrectly recorded in the Other Accrued Liabilities account

number 320 for the eight-month period. You'll need to make an

adjusting journal entry to correct this. You'll need to reclassify the

error to the interest expense account.

D.

The principal amount of $5,565 was also incorrectly recorded in

Other Accrued Liabilities account number 320. You'll need to make

an adjusting entry, moving this from Accrued Other and reducing

the Long-Term Note Payable account.

4.

Several late payables were received but not recorded. These payables

were for clothing and accessories discounted after the season and will be

sold next year. The total late payable amount is $6,998.

5.

Inventory is valued correctly at the lower of cost or market.

6.

Since it's the shop's rst year, it could be hard to estimate what the

balance in the allowance for doubtful accounts should be. But based on

the Bernard's New York store and the similarities of both, Mrs. Bernard

estimated the allowance should be 5 percent of Accounts Receivable.

7.

Mr. and Mrs. Bernard collect their salary throughout the year. They're

paid one week in arrears. Their gross payroll for a week is $3,942, and the

employer portion of tax associated with the accrual is 15 percent. You'll

need to make an adjusting entry to record the one week of salary and

tax expense.

8.

Rent is $3,000 per month and is prepaid on the rst day of the month.

9.

Utilities this time of year are low and run $275 a month. Services are

billed for the previous month at the beginning of the next month. For

example, June's utility invoice would be sent at the beginning of July.

You'll need to adjust for this.

10.

The shop's CPA has advised the Bernards that their estimated tax liability

for the year will be $51,924. They've made payments through the end of

May of $45,434.

The remaining amount will need to be accrued.

Total the adjusting entries to be sure they zero out.

Your next step will be to

combine the Unadjusted Trial Balances and the Adjusting Entries to calculate the

Adjusted Trial Balance.

Next, divide the accounts into either Net Income or Balance Sheet accounts and

enter into the appropriate columns.

Hint:

The net balance of both the Income

and Balance Sheets accounts will be $158,977.

var isIE = false; var f3 = [['t1_3',607],['t4_3',1174],['t5_3',1254],['t6_3',1235],['t7_3',586],['t9_3',1137],['ta_3',1150],['tb_3',1210],['tc_3',1245],['td_3',716],['tf_3',1210],['tg_3',1270],['th_3',1250],['ti_3',715],['tj_3',42],['tk_3',1320],['tl_3',1394],['tm_3',1042],['to_3',1121],['tq_3',1263],['tr_3',1367],['ts_3',1328],['tt_3',1322],['tv_3',1305],['tw_3',1382],['tx_3',1354],['ty_3',1327],['tz_3',238],['t11_3',1327],['t13_3',1292],['t14_3',1327],['t15_3',1317],['t16_3',539],['t17_3',66],['t18_3',1379],['t19_3',1373],['t1a_3',300],['t1b_3',904],['t1c_3',971],['t1d_3',460],['t1e_3',1538],['t1f_3',428],['t1g_3',1520],['t1h_3',681],['t1j_3',683],['t1k_3',882]]; function load3(){ var timeout = 100; if (navigator.userAgent.match(/iPhone|iPad|iPod|Android/i)) timeout = 500; setTimeout(function() {adjustWordSpacing(f3);},timeout); }

102

STATEMENTS, CONTROLS, AND RECORDS

Part 2: Aggregation of Income Account

Aggregate the Net Income account balances.

You can use Project, Part 2, to

This should be similar to that shown on page 184 in your

textbook.

Remember, the format is slightly dierent.

Part 3: Income Statement/Vertical Analysis

n Income Statement and Vertical Analysis for the eight- month period

ending June 30. This should be similar to that shown on page 216 in your

textbook.

Part 4: Aggregated Balance Sheet

Aggregate the Balance Sheet Accounts. This should be similar to that shown

on pages 198 and 199 in your textbook. Again, remember the format is slightly

dierent.

Part 5: Balance Sheet and Key Ratios

Prepare the Balance Sheet for the eight-month period ending June 30. Also, you

need to dene and calculate the current ratio, quick ratio, and debt-to-equity

ratio for the Mile High Ski and Snowboard Shop