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 final project.
The shop has been open for eight months. The grand opening was on November
1st of last year. The store's fiscal year will be July 1st–June 30th.
Barb Burke calculated the shop's unadjusted trial balance sheet in May after the
store had officially closed for the season.
There are five 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 Worksheet—Project, Part 1 (tab 1).
• The unadjusted balances have been inserted for your convenience.
• Enter the adjusting entries. (Specific information relating to these
entries can be found below.)
• Calculate the Adjusted Trial Balance. This should be similar to a
simplified 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 2—Aggregation 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 different.
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 eightmonth
period ending June 30th. This should be similar to that shown
on page 216 in your textbook.
4. Use Project, Part 4—Aggregated 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 different.
5. Use Project, Part 5—Balance 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 fixed-asset accounts.
• Computer equipment, computer software, and furniture and fixtures
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.
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 first 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 first 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.
Part 2: Aggregation of Income Account
Aggregate the Net Income account balances. You can use Project, Part 2, to
complete this task. This should be similar to that shown on page 184 in your
textbook. Remember, the format is slightly different.
Part 3: Income Statement/Vertical Analysis
Prepare an 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
different.
Part 5: Balance Sheet and Key Ratios
Prepare the Balance Sheet for the eight-month period ending June 30. Also, you
need to define and calculate the current ratio, quick ratio, and debt-to-equity
ratio for the Mile High Ski and Snowboard Shop.
