Using EXCEL, create the trial balance in the normal structure (current assets, long term assets, current liabilities, etc.). Also, include in the workbook a Cost of Goods Manufactured Statement, an Income Statement, a Retained Earnings Statement, and a Balance Sheet.
Spreadsheet Assignment
The following trial balance information is available for Bimbit, Inc. as of December 31, 2015
|
Accounts payable
|
265.00
|
Depreciation expense for equipment
|
880.00
|
|
Accounts receivable (Gross)
|
615.00
|
Interest expense
|
150.00
|
|
Accrued interest payable
|
80.00
|
Income tax expense
|
500.00
|
|
Accumulated. depreciation.-equipment
|
1,700.00
|
Office Supplies expense
|
16.00
|
|
Accumulated depreciation- building
|
6,000.00
|
Materials inventory, 1/1
|
75.00
|
|
Allowance for doubtful accounts
|
125.00
|
Materials purchased
|
2,480.00
|
|
Bonds payable
|
800.00
|
Materials inventory, 12/31
|
120.00
|
|
Building
|
12,000.00
|
Miscellaneous plant overhead expenses
|
35.00
|
|
Cash
|
4,900.00
|
Notes payable
|
1,200.00
|
|
Common stock
|
4,100.00
|
Payroll Payable
|
950.00
|
|
Transportation Out
|
165.00
|
Other general and administrative expense
|
185.00
|
|
Depreciation expense for Building.
|
1,200.00
|
Other selling expenses
|
300.00
|
|
Direct labor salary
|
1,800.00
|
Overtime premium for assembly workers
|
178.00
|
|
Discount on bonds payable
|
350.00
|
Paid in capital in excess of par
|
300.00
|
|
Dividends
|
600.00
|
Plant supervisory salaries expense
|
300.00
|
|
Equipment
|
4,500.00
|
Materials purchase allowances and returns
|
360.00
|
|
Factory cleanup workers salary
|
650.00
|
Income Tax Payable
|
350.00
|
|
Factory Supplies Expense
|
110.00
|
Retained earnings, 1/1
|
7,091.00
|
|
Finished goods inventory, 1/1
|
85.00
|
Sales
|
13,520.00
|
|
Finished goods inventory, 12/31
|
160.00
|
Sales commissions and salaries
|
3,250.00
|
|
General and administrative salaries
|
300.00
|
Sales returns and allowances
|
120.00
|
|
Goodwill
|
700.00
|
Transportation in
|
217.00
|
|
Heat, Light, and Power Expense for Building
|
110.00
|
Work in process inventory, 1/1
|
145.00
|
|
Heat, Light, and Power Expense Payable
|
110.00
|
Work in process inventory, 12/31
|
175.00
|
|
Advertising Expense
|
35.00
|
|
|
Other Information:
- The Note Payable account represents a 1-year note due on December 30, 2016.
- The Bonds Payable account represents 10-year bonds due in 8 years.
- 55% of the building is for fabrication of the product, 15% for storage of the finished product prior to delivery to customer, and 30% for the sales force.
- 85% of the time the factory equipment is used will be to build the product; 10% of the time the equipment is used for test runs during maintenance; and 5% of the time the equipment will be used by the sales force to demonstrate the company product.
REQUIRED:
Using EXCEL, create the trial balance in the normal structure (current assets, long term assets, current liabilities, etc.). Also, include in the workbook a Cost of Goods Manufactured Statement, an Income Statement, a Retained Earnings Statement, and a Balance Sheet. These reports should be prepared in good form, such that you might give them to your boss or to stockholders.
You should design a spreadsheet that is user friendly such that a clerk could enter the information and generate the reports. This means that your solution should include instructions to the clerk who will process the reports each period. The information entered by the user should then be linked directly to your output area so that the financial statements are automatically generated. The output should not contain any numeric data typed into the cells.
You will be graded on correctness of the solution (10 points), functionality of the spreadsheet (5 points), and presentation of the information (5 points).