UT XYZ Example Accounting Impacts Spreadsheet

General Instructions1. Regarding the .pdf file:
1. It contains the list of transactions/events that you are to account
for.
2. Regarding the Excel workbook:
1. It contains 18 separate worksheets.
3. You must enter all of your answers and supporting calculations in the Excel
workbook. In the space below, you are given specific instructions regarding
how to use the workbook.
4. There are three parts and you must complete each part.
5. Ignore the effect of taxes. That is, assume that CCC is not subject to income
tax, is not required to withhold sales tax, etc.
In the space below, I describe all the relevant transactions/events carried out by CCC during the
months of April and May 2023. I refer to each transaction/event as an item. I’ve assigned each
item a number. There are 15 items.
April 30, 2023
CCC started doing business on April 30, 2023. The following transactions/events occurred on that
day.
ITEM 1: On April 30, 2023, CCC sold equity to Chloe and Missy Vélo. Missy is a bicycling
enthusiast and private investor. Chloe and Missy each transferred $11,500 in cash to CCC in
exchange for half of CCC’s shares.
ITEM 2: On April 30, 2023, CCC signed a loan contract with Sudden Financial. The terms of the
contact are:
• On April 30, 2023, Sudden Financial transferred $11,500 to CCC.
• The loan matures in three years. That is, CCC must repay the entire principle of the loan
(i.e., $11,500) on April 30, 2026.
• CCC will make annual interest payments. The annual interest rate is 9.00 percent. The first
payment is due on April 30, 2024. The second and third payments are due on April 30,
2025 and April 30, 2026, respectively.
ITEM 3: On April 30, 2023, CCC signed an employment contract with Chloe.
• The contract became effective on May 1, 2023 and it has an indefinite term.
• Chloe’s monthly salary is $4,600.
• The $4,600 will be paid to Chloe in two equal payments of $2,300. The first payment will
be made on the 16th of the month and the second payment will be made on the first day of
the subsequent month. For example, half of Chloe’s salary for May 2023 was paid on May
16, 2023 and the remaining half was paid on June 1, 2023.
ITEM 4: On April 30, 2023, CCC purchased insurance from Urban Insurers.
• The policy provides CCC with “umbrella” coverage.
• The policy became effective on May 1, 2023 and terminates on April 30, 2024.
• On April 30, 2023, CCC prepaid the entire annual premium of $1,380.
ITEM 5; On April 30, 2023, CCC signed a rental agreement with Ruff Truck Rentals.
• The contract became effective on May 1, 2023 and it has a six-month term.
• Per the contract, CCC has the right to use a van that is owned by Ruff Truck Rentals.
• Per the contract, the monthly rent is $862.50.
• Ruff Truck Rentals demanded that CCC prepay the first two months of rent and, on April
30, 2023, CCC did this. Starting in July, rental payments will be due on the beginning of
each month.
• CCC will not account for the rental agreement as a lease. That is, CCC will not recognize
a right of use asset or a lease obligation.
Page 1 of 3
ITEM 6: On April 30, 2023, CCC paid $9,000 for a set of specialized tools and equipment.
• The tools and equipment are fixed assets.
• Chloe estimates that the tools and equipment have a five-year useful life.
• CCC uses straight line depreciation.
ITEM 7: On April 30, 2023, CCC purchased a Lenovo laptop for $276.
• Chloe estimates that laptop have a two-year useful life.
• CCC uses straight line depreciation.
ITEM 8: On April 30, 2023, CCC purchased an assortment of replacement parts. CCC will sell
these to customers. For example, if, while repairing a bicycle, CCC replaces a part, CCC will
charge the customer for both labor and the new part.
• The invoice price of the replacement parts was $6,900.
• On April 30, CCC paid the supplier $4,600 and promised to pay the remaining $2,300 on
or before July 15, 2023.
The following transactions and events occurred during May 2023
ITEM 9: On May 6, 2023, Chloe represented CCC at a local bike rally. During the rally she met
with local cycling clubs and told them about her business. While doing this, she received a proposal
from Nicole Bicicletta. Nicole is the organizer of the local cycling club Chain Reaction. After
some negotiations, CCC and Chain Reaction sign a contract with the following terms:
• Starting in May 2023, CCC will come to Chain Reaction’s clubhouse on the second and
fourth Saturday of each month. CCC will arrive at 1:00 PM and depart at 5:00 PM.
• While it is at the clubhouse, CCC will repair bicycles on a first-come-first-served basis.
o Only members of Chain Reaction will be allowed to use the service.
o Individual members of Chain Reaction will not pay for any services that CCC
performs. Rather, CCC will charge Chain Reaction a flat fee of $920 per month.
o Each member of Chain Reaction who has their bicycle repaired will be responsible
for paying for any replacement parts installed by CCC.
• The contract has a term of one year and, during the year, Chain Reaction will make six
payments of $1,840. The first payment is due on or before May 31, 2023; the second
payment is due on or before July 31, 2023; the third payment is due on or before September
30, 2023; etc.
ITEM 10: On May 16, 2023, CCC paid Chloe $2,300, which represents the first half of her salary
for May.
ITEM 11: On May 17, 2023, Chloe received a call from Günter Fahrrad. Günter is the president
of the local cycling club Wheels of Steel. Günter heard about CCC’s arrangement with Chain
Reaction and he is interested in a similar arrangement for Wheels of Steel. After some negotiations,
he and CCC sign a contract with the following terms:
• Starting in May 2023, CCC will come to Wheels of Steel’s clubhouse on the last Sunday
of each month. CCC will arrive at 2:00 PM and depart at 8:00 PM.
• While it is at the clubhouse, CCC will repair bicycles on a first-come-first-served basis.
o Only members of Wheels of Steel will be allowed to use the service.
Page 2 of 3
•
o Individual members of Wheels of Steel will not pay for any services that CCC
performs. Rather, CCC will charge Wheels of Steel a flat fee of $690 per month.
o Each member of Wheels of Steel who has their bicycle repaired will be responsible
for paying for any replacement parts installed by CCC.
The contract has a term of one year and, during the year, Wheels of Steel will make three
payments of $2,760. The first payment is due on or before July 31, 2023; the second
payment is due on or before November 30, 2024; and the third payment is due on or before
March 31, 2024.
ITEM 12: On May 31, 2023, CCC paid the amount it owed its supplier of replacement parts.
ITEM 13: On May 31, 2023, CCC purchased additional replacement parts. The parts cost $4,600.
CCC paid $3,450 in cash and agreed to pay the remaining $1,150 by July 15, 2023.
ITEM 14: During May, CCC provided repair services to individual customers. Note that these
customers were not members of either Chain Reaction or Wheels of Steel. The relevant details
regarding these services are:
• Repair services: CCC charged individual customers a total of $11,500.
• Replacement parts: CCC charged individual customers a total of $6,325 for replacement
parts that had an original cost of $3,162.50.
• These customers paid CCC $13,800 cash. The customers who did not pay in full agreed to
do so by June 30, 2023. CCC expects to collect all of the amounts owed.
ITEM 15: During May, CCC provided bicycle repair services per its agreements with Chain
Reaction and Wheels of Steel.
• On May 31, 2023, CCC received the first payment of $1,840 from Chain Reaction.
• While providing these services, CCC sold replacement parts with an initial cost of $3,450
for $6,900. CCC received cash of $5,175 and the customers who did not pay in full agreed
to do so by June 30, 2023. CCC expects to collect all of the amounts owed.
Page 3 of 3
Overview
Chloe is a longtime bicycling enthusiast. Although she is a good cyclist, Chloe is not at
the elite level, and thus she cannot earn a living racing bicycles. That said, she is very
skilled at repairing them. With this in mind, she has decided to start a new business:
Chloe’s Cycle Club, which I’ll refer to as CCC. CCC will provide bicycle repair services.
In the .pdf file named PDF FOR MIDTERM EXAM, I describe all the relevant
transactions/events carried out by CCC during the months of April and May 2023. I refer
to each transaction/event as an item. I’ve assigned each item a number. There
are 15 items.
Required
Part 1
For each item, describe:
1. The effect that the transaction/event had on CCC’s assets, liabilities, and
equity on the day that the transaction/event occurred.
1. You must list all the accounts that were affected. Do not list
accounts that were not affected.
2. For each account that was affected, you must state the:
1. Beginning balance—i.e., the carrying value of the account
before the transaction/event occurred.
2. The amount by which the carrying value of the account
changed.
3. Ending balance—i.e., the carrying value of the account
after the transaction/event occurred.
2. The effect that the transaction/event had on CCC’s revenues for the day that
the transaction/event occurred.
1. You must list the dollar effect that the transaction/event had on
revenues.
2. If the transaction/event did not affect revenues, you should enter
“No Effect.”
3. The effect that the transaction/event had on CCC’s expenses for the day that
the transaction/event occurred.
1. You must list each expense account that was affected and the dollar
effect that the transaction/event had on this account.
2. If, on the day it occurred, the transaction/event did not affect any
expenses, you should enter “No Effect.”
Further instructions for Part 1
•
•
•
•
Regarding items 14 and 15, which relate to transactions and events that
occurred throughout the month of May, you should describe the related
transactions/events as if they occurred on May 31, 2023 (i.e., the last day of
the month).
In the Excel workbook there is a separate worksheet for each item and you
should enter your answers in the worksheet that relates to the corresponding
item. For example, if you are describing the effect of item 10, you would use
the Excel worksheet named ITEM 10.
To illustrate how to enter your answers in the Excel worksheet, let’s consider
an example involving XYZ. Suppose the following:
o At the beginning of Day 1 XYZ’s (1) cash account had a balance of
100; (2) inventory account had a balance of 200; and (3) retained
earnings account had a balance of 1,000.
o During Day 1 a transaction/event caused XYZ’s (1) cash to increase
by 10; (2) inventory to decrease by five; (3) revenues to increase by
10; and (4) cost of sales to increase by five.
In the Excel worksheet named XYZ EXAMPLE, I show how to describe the
effect of the transaction/event on XYZ’s assets, liabilities, equity, revenues,
and expenses. Note:
o BB and EB are the acronyms for beginning balance and ending
balance.
o + and – denote positive and negative changes.
o I do not list all of the line items on XYZ’s balance sheet and income
statement. Rather, I only list the line items that were affected.
o The transaction did not affect any of XYZ’s liabilities. Hence, I do
not list any specific liabilities. Rather, I enter “Liabilities” in cell G4,
and then I enter “No effect” in cell H4.
Part 2
•
•
•
Prepare CCC’s balance sheet as of May 31, 2023.
Show your answer in the Excel worksheet named BALANCE SHEET MAY-312023, which is contained in the Excel workbook.
o Show the name of each account in the space under the
heading Account Name.
o Show the ending balance of each account in the space under the
heading Carrying Value.
o Identify the location of your supporting calculations in the space
under the heading Comments.
Your answer to Part 2 might depend on some of your answers to Part 1. If so,
in the space under the heading Comments. identify the relevant worksheet.
For example, suppose the ending balance on May 31 of CCC’s cash account
can be found in the worksheet named ITEM X. Then, in the row pertaining to
•
cash you would enter “see ITEM X” in the space below the
heading Comments.
Your answer to Part 2 might depend on additional calculations that you did
not make when answering Part 1. If so, show these calculations in the
worksheet named BALANCE SHEET MAY-31-2023. And, in the space under
the heading Comments, identify the cells where these calculations are located.
For example, suppose the ending balance on May 31 of CCC’s cash account is
based on a set of calculations that you performed in cells A25 through A30.
Then, in the row pertaining to cash you would enter “see cells A25 through
A30” in the space below the heading Comments.
Part 3
•
•
•
•
Prepare CCC’s income statement for the month of May 2023.
Show your answer in the Excel worksheet named INCOME STATEMENT
MAY-31-2023, which is contained in the Excel workbook.
o Show the name of each line item (e.g. Revenues) in the space under
the heading Line Item.
o Show the amount of each line item in the space under the
heading Amount.
o Identify the location of your supporting calculations in the space
under the heading Comments.
Your answer to Part 3 might depend on some of your answers to Parts 1 and
2. If so, in the space under the heading Comments, identify the relevant
worksheet. For example, suppose that CCC’s revenues for the month of May
can be found in the worksheet named ITEM Y. Then, in the row pertaining to
revenues you would enter “see ITEM Y” in the space below the
heading Comments.
Your answer to Part 3 might depend on additional calculations that you did
not make when answering Parts 1 and 2. If so, show these calculations in the
worksheet named INCOME STATEMENT MAY-31-2023. And, in the space
under the heading Comments, identify the cells where these calculations are
located. For example, suppose that CCC’s revenues for the month of May are
based on a set of calculations that you performed in cells A25 through A30.
Then, in the row pertaining to revenues you would enter “see cells A25
through A30” in the space below the heading Comments.
XYZ Example – Effect on Day 1
Assets
Account Name
Cash
Inventory
BB
100
200
Liabilities and Equity
+
10
5
EB
110
195
Account Name
Liabilities
Retained earnings
Revenues
$ Effect
10
Expenses
Cost of sales
$ Effect
5
1
Liabilities and Equity
BB
+
No effect
1,000
10
–
EB
5
1,005
Item Number 1 – Effect on April 30, 2023
Assets
Account Name
Cash
Assets
BB
0
0
Liabilities and Equity
+
23,000
23,000
0
EB
23,000
23,000
Account Name
Share Capital
Equity
Liabilities and Equity
Revenues
Expenses
No effect
$ Effect
No effect
$ Effect
0, 2023
Liabilities and Equity
BB
+
0
23,000
0
23,000
0
23,000
0
EB
23,000
23,000
0
23,000
Item Number 2 – Effect on April 30, 2023
Assets
Account Name
Cash
Assets
BB
23,000
23,000
Liabilities and Equity
+
11,500
11,500
0
EB
34,500
34,500
Account Name
Loan Payable
Liabilities
Liabilities and Equity
$ Effect
Revenues
Expenses
$ Effect
0, 2023
Liabilities and Equity
BB
+
0
11,500
0
11,500
23,000
0
EB
11,500
11,500
0
34,500
Item Number 3 – Effect on April 30, 2023
Assets
Account Name
Cash
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
0, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 4 – Effect on April 30, 2023
Assets
Account Name
Cash
Insurance Policy
Assets
BB
34,500
0
34,500
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
1,380
1,380
1,380
1,380
EB
33,120
1,380
34,500
Account Name
Liabilities and Equity
0, 2023
Liabilities and Equity
BB
+
34,500
–
EB
34,500
Item Number 5 – Effect on April 30, 2023
Assets
Account Name
Cash
Assets
BB
33,120
34,500
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
1,725
EB
31,395
Account Name
0, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 6 – Effect on April 30, 2023
Assets
Account Name
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
0, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 7 – Effect on April 30, 2023
Assets
Account Name
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
0, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 8 – Effect on April 30, 2023
Assets
Account Name
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
0, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 9 – Effect on May 6, 2023
Assets
Account Name
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 10 – Effect on May 16, 2023
Assets
Account Name
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
6, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 11 – Effect on May 17, 2023
Assets
Account Name
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
7, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 12 – Effect on May 31, 2023
Assets
Account Name
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
1, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 13 – Effect on May 31, 2023
Assets
Account Name
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
1, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 14 – Effect on May 31, 2023
Assets
Account Name
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
1, 2023
Liabilities and Equity
BB
+
–
EB
Item Number 15 – Effect on May 31, 2023
Assets
Account Name
BB
$ Effect
Revenues
Expenses
$ Effect
Liabilities and Equity
+
–
EB
Account Name
1, 2023
Liabilities and Equity
BB
+
–
EB
Account Name
CCC Balance Sheet as of May 31, 2023
Assets
Liabilities and Equity
Carrying Value
Comments
Account Name Carrying Value
s and Equity
Comments
CCC Income Statement for the month ending May 31, 2023
Line item
Amount
Comments