Accounting Information Systems AC341

Project #1

The ledger of AISExperts Inc. showed the following balances after adjustment, but before closing, on December 31, 2020, the end of the current year:

Accounts payable

82000

Accounts receivable

100580

Accumulated depreciation - equipment

36540

Depreciation expense

10000

Marketable securities

11250

Interest revenue

1100

Cash

78470

Finished goods inventory

73500

Available-for-sale securities

6000

Common stock (10,000 shares outstanding)

100000

Prepaid rent

14500

Cost of goods sold

625650

Dividends declared

32000

Insurance expense

2200

Lease obligation

5200

Equipment

114450

General expenses

110250

Interest expense

5800

Raw materials inventory

51000

Rent expense

2000

Prepaid insurance

11325

Retained earnings

?

Dividend revenue

10250

Work-in-process inventory

52200

Leased equipment

6000

Salaries payable

26700

Sales

987500

Selling expenses

136210

The president of AISExperts Inc., has asked you to develop a flexible financial statement package (call the file yournameFinState20), using Excel that includes:

a data entry sheet

an adjusted trial balance,

a single-step income statement,

a multi-step income statement,

a statement of retained earnings,

a classified balance sheet, and

a post-close trial balance

You can ignore taxes in this project.

Each statement must be on one sheet in the file and this file should allow the financial statements to be prepared quickly by entering account balances in the appropriate cells on the first sheet of the file (book) - data entry sheet.

Include percentages in the Income Statement (vertical analysis) i.e. gross profit percentage, % of selling expenses to sales, etc.

Your file must contain documentation with comments (Excel feature).

Use the financial information above as input for your statements.Only formulas/cell references should appear on all sheets except your data entry sheet (except titles and dates; dates and titles should be placed at the top of the each sheet; do not use headers).

Arriving at the correct solution using appropriate formulas and the correct format (along with the spelling of account names) for financial statements will allow you to keep your current position (with a score of 80).The presentation of the financials (i.e. underlining, bold, font, commas, color etc.) will potentially enable you to secure a promotion (an additional 20 points).Good Luck.

Basic Grading Rubric:

Data entry sheet

5

Adjusted trial balance

10

Single-step income statement

5

Multi-step income statement

10

Statement of Retained Earnings

10

Classified balance sheet

10

Post-close trial balance

10

Flexibly linked data

20

Professionally presented/comments

20

Deliverables:

1 Excel spreadsheet file (workbook) with multiple sheets loaded to the Blackboard classroom under the Project's submit link