UU Accounting Effect that Event Have on Assets & Equity Excel Sheet

OJ
Balance sheet as of December 31, 2021
(all amounts are in U.S. Dollars)
Cash
Accounts receivable
Inventory of novelty items
Prepaid rent on office space at OuiWork
iPad
100
50
30
150
240
Total assets
570
Interest payable
Trade accounts payable
Advance from customer
Loans payable
4
15
100
200
Total liabilities
319
Share capital
Retained earnings
25
226
Liabilities and equity
570
XYZ Example – Effect on Day 1
Account Name
Cash
Inventory
Assets
BB
+
100
10
200
5
EB
110
195
Liabilities and Equity
Account Name
BB
Liabilities
No effect
Retained earnings
Revenues
$ Effect
10
Expenses
Cost of sales
$ Effect
5
1,000
abilities and Equity
+
10
–
EB
5
1,005
Account Name
Assets
BB
+
$ Effect
Revenues
Expenses
$ Effect
Item Number 1 – Effect on January 6, 2022
Liabilities and Equity
EB
Account Name
BB
abilities and Equity
+
–
EB
Account Name
Assets
BB
+
$ Effect
Revenues
Expenses
$ Effect
Item Number 2 – Effect on January 6, 2022
Liabilities and Equity
EB
Account Name
BB
abilities and Equity
+
–
EB
Account Name
Assets
BB
+
$ Effect
Revenues
Expenses
$ Effect
Item Number 3 – Effect on January 25, 2022
Liabilities and Equity
EB
Account Name
BB
abilities and Equity
+
–
EB
Account Name
Assets
BB
+
$ Effect
Revenues
Expenses
$ Effect
Item Number 4 – Effect on January 25, 2022
Liabilities and Equity
EB
Account Name
BB
abilities and Equity
+
–
EB
Account Name
Assets
BB
+
$ Effect
Revenues
Expenses
$ Effect
Item Number 5 – Effect on January 31, 2022
Liabilities and Equity
EB
Account Name
BB
abilities and Equity
+
–
EB
Account Name
Assets
BB
+
$ Effect
Revenues
Expenses
$ Effect
Item Number 6 – Effect on January 31, 2022
Liabilities and Equity
EB
Account Name
BB
abilities and Equity
+
–
EB
Account Name
OJ Balance Sheet on January 31, 2022
Assets
Liabilities and Equity
Carrying Value
Comments
Account Name Carrying Value
s and Equity
Comments
OJ Income Statement for the month ending January 31, 2022
Line item
Amount
Comments
Overview
In the .pdf file named ACCTG 6000 – Assignment for Module Four – CASE, I provide
background information about the company named OJ and I describe all the relevant
transactions and events carried out by OJ during the month of January 2022. I refer to
each transaction/event as an item, I’ve assigned each item a number, and there are six (6)
items.
Required
Part 1
For each item that relates to a transaction and/or event that occurred during January
2022, describe:
1. The effect that the transaction/event had on OJ’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 OJ’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 OJ’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
•
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 2, you would use
the Excel worksheet named ITEM 2.
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.
▪ 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 OJ’s balance sheet as of January 31, 2022.
Show your answer in the Excel worksheet named BALANCE SHEET JAN-312022, 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 January 31 of OJ’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 JAN-31-2022. And, in the space under
the heading Comments, identify the cells where these calculations are located.
For example, suppose the ending balance on January 31 of OJ’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 OJ’s income statement for the month of January 31, 2022.
Show your answer in the Excel worksheet named INCOME STATEMENT JAN31-2022, 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/or 2. If so, in the space under the heading Comments, identify the
relevant worksheet. For example, suppose that OJ’s revenues for the month
of January 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 JAN-31-2022. And, in the space
under the heading Comments, identify the cells where these calculations are
located. For example, suppose that OJ’s revenues for the month of January
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.
General Instructions
1. This assignment has three parts and you must complete each part.
2. Enter your answers in the Excel workbook named ACCTG 6000 – Assignment
for Module Four – EXCEL FILE. In the quiz, I describe this workbook and I
provide you with detailed instructions about how to use it.
3. When you start the quiz, download the:
1. .pdf named ACCTG 6000 – Assignment for Module Four – CASE.
2. Excel workbook named ACCTG 6000 – Assignment for Module
Four – EXCEL FILE.
4. Regarding the .pdf file:
1. It contains background information and the list of transactions and
events that you are to account for.
2. Do not resubmit this file.
3. Do not enter you answers in this file.
5. Regarding the Excel workbook:
1. It contains ten (10) separate worksheets.
2. Once you’ve downloaded it, rename it Firstname_Lastname_M4.
For example, I would name my workbook Steve_Monahan_M4.
3. Make sure to upload the workbook after you’ve completed the
assignment.
6. Ignore the effect of taxes. That is, assume that OJ is not subject to income tax,
is not required to either withhold or pay sales tax, etc.