AC ase Study of the Accounting Cycle Using Spreadsheet Technology

Report as inappropriate Your report has been received

Shared by: HC12072700323

Tags

Stats

views:: 61
posted:: 7/26/2012
language:: English
pages:: 8

Public Domain

Document Sample

Accounting Information Systems Series:
A Case Study of the Accounting Cycle Using Spreadsheet Technology
By Nancy Jones and Jim Mensching
Both from California State University, Chico

FACULTY INFORMATION (This can be eliminated from the problem distributed to students):

This should be a review of material covered in the beginning financial accounting class. In our
curriculum we have the accounting information systems class as a mandatory co-requisite or
prerequisite to the first intermediate accounting class. This allows us in the AIS class to review the
manual accounting cycle, help improve or develop a student’s Excel skills, and gives the student a
refresher for beginning the intermediate accounting class.

The next assignment (Accounting Information Systems Series: A Case Study of the Accounting Cycle
Using Basic ERP Technology) is a continuation of this first assignment and it involves the journal
entries for the last half of the month and also the adjusting and closing entries. As you will see in the
second assignment, we also introduce the students to a computerized system. We use the SAP system
in a very minimal way in that assignment by only having the students make all transaction entries as
general journal entries. However, they do see some of the controls within a computerized system.
These controls are discussed in much more detail as the class matures.

The students also get to compare the amount of work involved in a manual system versus a
computerized system. If a student hadn’t yet been convinced that a computerized system is far
superior to a manual system, these two assignments should convince the student.

These assignments start the progression from a manual system to a fully integrated, real time
transaction-based information system. The last two assignments in the course (labeled assignments 6
and 7) involve transactions in a fully integrated ERP system.

INTRODUCTION:

Assignment 1 and part of assignment 2 are a review of general financial accounting principles and
procedures. In assignment 1, you will create general journal entries for a series of transactions. You
will also show the impact of these transactions on a set of t-accounts and create an interim trial
balance. Assignment 2 continues the review of accounting procedures with additional journal entries
and the adjusting journal entries for month-end close. You will again show the impact of the
transactions on t-accounts and create a trial balance. For the last part of the assignment you will
explore how your journal entries might be created in an automated system by entering your “manual”
journal entries into the SAP/R3 system. The SAP system will then be used to produce a set of
financial statements (balance sheet and income statement).

The following company information will be used for both assignment 1 and assignment 2:

Cottonwood Distribution, Inc.

Cottonwood Distribution, located in Red Bluff, CA was the “brain child” of two Chico State graduates.
Cottonwood purchases products from local producers and distributes them to rodeo event organizers
throughout the United States, Australia, Canada, and South America. Cottonwood has two main
product lines: “resale merchandise”, which includes belt buckles, apparel, posters, and other items
purchased by the event organizer and re-sold to customers at the rodeo stores; and “event

1
merchandise”, which includes any promotional materials, tools, tack, gloves, chaps, and so on used by
rodeo participants and organizers. Sales tax of 7.25% must be charged on only event merchandise (not
resale merchandise) sold and delivered or shipped within the state of California.

Cottonwood Distribution began business on January 1, 2004. Cottonwood’s fiscal year begins on
January 1 and ends on December 31. The company is organized as an S-corporation under California
Corporations Code section 100 and both owners own equal shares in the corporation.

The company currently rents a “complex” of two large warehouses linked to a 4000 sq.ft. office
building, located off highway 99. The company plans to build a new facility within the next five years,
but needs to make some improvements to the rented facilities in the meantime. In 2006, owners and
managers of Cottonwood agreed to a 2007 budget, which included adding industrial shelving in one of
the warehouses for inventory storage and new shrink-wrap equipment for packaging their products.

When the company first started, a manual accounting system was put in place. However, the company
has decided that it needs to computerize its accounting process to be more efficient. In addition, in
order to expand their sales to the PRCA, (Professional Rodeo Cowboy’s Association), Cottonwood
must have EDI capabilities by January 1, 2008. A project team has been established to meet this
deadline and the computerization of the accounting system is the first step in implementation of the
company’s new information system and their link to the PRCA.

Computer consultants have configured the new computer system and it is ready to use. The books of
the company were closed on December 31, 2006 to prepare for the transition to the new system. The
account balances are now ready to be transferred to the new computer system. Cottonwood will run
parallel systems during January 2007; that is, they will record transactions in both the manual system
and their new computerized system to make sure the new system is set up properly. This is a standard
business practice that reduces the risks associated with implementing new systems.

The next page shows Cottonwood Manufacturing’s chart of accounts followed by the account balances
as of December 31, 2006 and then the descriptions of events occurring during the first half of January
2007 for which you are to make general journal entries in a manual accounting system. Your manual
accounting system should include a general journal, t-accounts, and a trial balance. You will create
your general journal using Excel (see the sample problem for the format). Your t-accounts and trial
balance will also be generated in Excel. Don’t forget to include your beginning and ending balances in
your t-accounts.

For assignment 2, you will complete the journal entries for the second half of the month and then enter
all the data into the SAP R/3 system. If the SAP system has been configured properly and the data
entry is done correctly, the resulting financial information from the manual system and the SAP system
should match.

DETAILED REQUIREMENTS:

Record the daily transactions if appropriate, (some transactions may not involve journal entries), as
general journal entries into Excel. Also, post these journal entries into t-accounts and then calculate
account balances using cell formulas in Excel. Enter the t-account balances into your Excel document
as a trial balance. You should create links between spreadsheets to expedite this process and minimize
the risk of an error in data entry. Some Excel skills will be reviewed in class. Looking over the

2
answer to the sample problem should also be very helpful in reviewing your Excel skills

This assignment is due before class starts January 31, 2007. The electronic document is to be
submitted via WebCT email prior to 8:00 am that day. The printed document must be handed in at the
beginning of class or sooner.

Note: Since assignment 2 builds on documents you create in assignment 1, you will probably want to
review your instructor’s feedback on assignment 1 prior to investing significant time to assignment 2.

3
Chart of Accounts: Chart of Accounts - ACCT 321

G/L Account No. Account name

1001 Cash (Bank of America checking account)
1101 Accounts Receivable
1111 Allowance for doubtful accounts
1201 Resale Merchandise Inventory
1202 Event Merchandise Inventory
1301 Office Supplies
1302 Prepaid Insurance
1303 Prepaid Rent
1304 Prepaid Advertising
1310 Deposits
1401 Warehouse and Office Equipment
1411 Accumulated depreciation - Equipment
1501 Buildings
1511 Accumulated depreciation – Buildings
1601 Land
2001 Accounts Payable
2002 Accrued Wages Payable
2003 Accrued Payroll Taxes Payable
2004 Accrued Sales Tax Payable
2005 Accrued Interest Payable
2010 Other Payable
2020 Dividends Payable
2030 Current Maturities of Long-Term Debt
2099 Other Accrued Expenses Payable
2100 Notes Payables
3000 Common Stock - (no par)
3100 Retained Earnings
4001 Resale Merchandise Sales
4002 Event Merchandise Sales
4099 Miscellaneous Revenue
4100 Purchase Discounts
5001 Cost of Goods Sold – Resale Merchandise Sales
5002 Cost of Goods Sold – Event Merchandise Sales
6001 Advertising Expense
6002 Depreciation Expense
6003 Insurance Expense
6004 Interest Expense
6005 Office Supplies Expense
6006 Rent Expense
6007 Salaries/Wages Expense
6008 Shipping Expense
6009 Utilities Expense
6010 Computer Expense
6011 Bad Debt Expense
6012 Maintenance & Repairs
6099 Miscellaneous Expense

4
Beginning Account Balances as of December 31, 2006 – Cottonwood, Inc.

Acct # Account Name Debit Balance Credit Balance
1001 Cash 125,483 0
1101 Accounts Receivable 47,555 0
1111 Allowance for doubtful accounts 0 3,808
1201 Resale Merchandise Inventory 154,231 0
1202 Events Merchandise Inventory 167,492 0
1301 Office Supplies 853 0
1302 Prepaid Insurance 4,400 0
1303 Prepaid Rent 5,000 0
1304 Prepaid Advertising 0 0
1310 Deposits 0 0
1401 Warehouse and Office Equipment 162,000 0
1411 Accumulated depreciation - Equipment 0 32,400
1501 Buildings 0 0
1511 Accumulated depreciation - Buildings 0 0
1601 Land 0 0
2001 Accounts Payable 0 8,450
2002 Accrued Wages Payable 0 36,300
2003 Accrued Payroll Taxes Payable 0 0
2004 Accrued Sales Tax Payable 0 4,213
2005 Accrued Interest Payable 0 0
2010 Other Payable 0 0
2020 Dividends Payable 0 20,000
2030 Current Maturities of Long-Term Debt 0 11,206
2099 Other Accrued Expenses Payable 0 2,821
2100 Notes Payables 0 59,334
3000 Common Stock - (no par) 0 200,000
3100 Retained Earnings 0 288,482

5
DESCRIPTIONS OF EVENTS OCCURRING January 1 –15, 2007

Date Description of Event

1 January 2, 2007 Employees are paid monthly on the first day of the month for work
performed the previous month (because of the New Year’s holiday, this
month they are paid on the 2nd). Total wages paid on this date were
$36,300. (Ignore payroll taxes for this assignment.)

2 Cottonwood received a check for $14,075 from one of their customers as
payment for a previous order.

3 Cottonwood’s office manager picked up office supplies from Office Max
on her way into work. She checked the orders against Cottonwood’s
purchase order and stocked the supplies in the supply cabinet. The Office
Max invoice totaled $532 and payment terms are net the 12th of the
month.

4 Cottonwood signed and paid for an annual advertising agreement with
the PRCA for banner ads on the PRCA website. Cottonwood’s
advertisements will be posted to the website starting in March 2007 and
run until February 28, 2008. The contract cost is $6,000 plus any art and
setup charges which will be billed as they occur.

5 January 3, 2007 Cottonwood received a shipment of event merchandise from the Rodeo
Outfitters Company. This merchandise was ordered on December 20th
and was delivered via UPS. Rodeo Outfitters paid the shipping of $55.
Cottonwood is to pay Rodeo Outfitters $15,000 based on terms of 2/10
net 30.

6 Cottonwood received an order from the FFA rodeo in LaJunta Colorado,
(LJ FFA), for $22,310 in event merchandise. The Cottonwood customer
service representative confirmed that the LJ FFA Rodeo’s account was
paid current and they had sufficient credit available to cover the new
sale. The order was then sent to the warehouse where it was picked and
prepared for shipping. The merchandise was shipped via UPS at a cost of
$180, which was paid by Cottonwood. Cost of the merchandise shipped
was $8,470. Terms of the sale are net 30.

7 January 4, 2007 Cottonwood hired an additional employee for the inventory control area.
As with all of the other employees, this employee will be paid monthly
on the first day of the next month. The employee will be paid $2,850 per
month.

8 Cottonwood received their new product catalogs ordered from a local
print shop. The print shop billed Cottonwood $5,500 for 5,000 catalogs
with payment terms of net 10. Cottonwood considers catalogs as
advertising and expenses the catalogs at the end of the month based on

6
how many catalogs are sent out during the month.
.
9 January 5, 2007 Cottonwood received an order from the Bakersfield Oil City Rodeo for
$22,000 of resale merchandise. This is a new rodeo with no credit
history. Hence, Cottonwood has requested payment in full prior to the
delivery of goods. The cost of the goods ordered is $9,805.

10 Cottonwood placed a purchase order with the Lazy J Ranchers
Emporium for $36,000 in event merchandise. Payment terms to Lazy J
Ranchers are net 30 upon receipt of goods.

11 Cottonwood paid an outstanding vendor invoice of $8,450. There was
no payment discount associated with this invoice.

12 Cottonwood paid the December telephone bill to AT&T in the amount of
$278. Since the amount is not deemed material, these utility expenses
are not accrued at the end of the month.

13 January 8, 2007 Cottonwood’s sales rep sold event merchandise to the Dust Bowl Rodeo
in Kansas for a total sales amount of $114,000. Terms of the sale are net
15 and will be paid by electronic funds transfer (EFT). The order
information was sent to the warehouse where the merchandise was
picked and packaged for shipment. The order was picked up by CWX
Freightlines and shipping costs of $790 were paid by Cottonwood at the
time of shipment. Cost of the merchandise shipped was $59,600.

14 After extensive collection effort including having a collection agency
contact the party, Cottonwood was notified today that the Fly-by-Knight
Rodeo has gone out of business. They owed Cottonwood $2,300 on
account. Cottonwood now deems that debt as being uncollectible and
removes it from their books.

15 January 9, 2007 A wire transfer in the amount of $22,000 is received from the
Bakersfield Oil City Rodeo in payment of the order placed on January
5th. The goods are picked, packaged and shipped via UPS. Cottonwood
pays the shipping in the amount of $105.

16 Cottonwood paid the December’s P.G. & E. bill in the amount of $2,821
using their bank’s automated bill payment system. This amount is
considered material and hence the expense was accrued in December.

17 Alamo Conference Center in Texas placed an order via email.
Cottonwood’s sales rep wrote up the order, checked their credit and sent
the order information to the warehouse for shipping. The sale amount
was $95,240 which included $75,240 in resale merchandise and $20,000
in event merchandise. The cost of the resale merchandise was $44,173
and the cost of the event merchandise was $9,800. The goods were
shipped that day. Cottonwood paid the shipping expense of $1,706.

7
Payment terms for the order are net 15.

18 Cottonwood received customer checks totaling $20,200 for payment on
outstanding accounts.

19 January 10, 2007 Cottonwood’s warehouse received the January 5th order from Lazy J
Ranchers Emporium. The inventory was counted and placed on the
shelves. Proof of receipt and the vendor’s invoice was sent to
accounting. Lazy J paid the shipping of $1,230.

20 January 11, 2007 Cottonwood placed a purchase order with a local vendor for the new
industrial shelving for the warehouse that was approved in Cottonwood’s
budget. The total price for the shelving is $20,000 plus 7.25% sales tax.
Installation costs are quoted at $1,600. Vendor payment terms are net 30
upon receipt of goods.

21 Cottonwood paid Office Max for the supplies picked up on January 2nd.

22 January 12, 2007 The Bozeman Convention Center, (BCC), in Montana contacted
Cottonwood with an order for $30,000 in resale merchandise. Cost of the
merchandise was $14,750. BCC has never purchased from Cottonwood
before, but has already submitted the appropriate paperwork to
Cottonwood’s credit department.

23 Cottonwood paid the invoice for the shipment from Rodeo Outfitters
received on January 3rd and took the 2% discount because of early
payment.

24 Cottonwood paid for their printed catalogs received January 4th.

25 January 15, 2007 Cottonwood’s credit department approved BCC for up to $20,000 credit
at terms of net 15. BCC has been asked to send payment of $10,000 so
that their order can be shipped.