In this assignment, you are required to create a spreadsheet for recording the summary transactions of a company, preparing the T-accounts and subsequently the Income Statement. You will benefit by using the template provided for the solution to Cornell Camps this spreadsheet is in Courseweb and altering the template to fit Problem 3-16. Your spreadsheet should have 3 blocks or sections: (1) data entry, (2) T-accounts for MATERIALS, MOH, WIP, FG and COGS, (no need for other T-accounts) and (3) Income statement.

The data entry section is meant to record all the given transactions relating to labor, materials, overhead items, sales, and inventories. The labels in the data input block for the transactions and other information may be in alphabetical order or follow a timeline acquisition of inputs, production and finished goods.

Once the data entry section is complete, you can create the relevant T-accounts. The idea here is to post the transactions in the data entry section directly into the appropriate T-accounts. This is accomplished using formulas/cell references of Excel. The labels and values in the income statement and T-accounts must be properly cross-referenced with data input block. (Labels and data for each transaction must be entered only once in the data entry section). The idea is that if the values for the transactions are changed at the data input block, these changes are automatically reflected in the T-accounts and income statement.

Create a spreadsheet along the lines discussed using the information given in problem 3-16 of your textbook. Verify whether your spreadsheet is correct by comparing the net income obtained in your spreadsheet with the solution posted. Then,

1. Print spreadsheet as shown in the sample displaying labels and values.

2. Print the spreadsheet displaying the formulae/cell reference used.

Make sure to size your sheet in such a way that all the blocks fit into a page. Presentation is as important as correct answers.

Now, assume that sales were $1,150,000, materials purchases were $290,000, and direct labor costs were $215,000. Also assume that the estimated direct labor dollars were $165,000.

T Accounts Descriptior transaction label Work in Process BB- WIP Job Cost Sheet for C12 B8-RM 10,000 2a 1 40,000 2b 16,000 18,000 9 48,000 DM used for C12 DL used for C12 Applied OH for C12 16,000 10800 35,800 EB-RM $15,950 Total Mfg. Cost 1 $40,000 EB -WIP $50,800 Finished Goods unit MFG. cost 10 36,000 Manufacturing Overhead DM used fo 2a DM used fe 2b Indirect Ma 10,800 18,000 50 7b Job Cost Sheet for F16 EB FG$17,000 DM used for F16 DL used for F16 Applied OH for F16 18,000 Cost of Goods Sold DL used for 4a DL used for 4b 10 36,000 To IS 36,250 Total Mfg. Cost Actual Manufacturing OH Cost:s unit MFG. cost Cornell Camps Inc. Income Statement Less Cost of goods sold (Adjusted) Gross Margin Less Selling & Administrative Expenses 17,750 $14,800.00 Applied MOH Calculation Net Income $2,950.00 MH Used b 7a MH Used b 7b 8 14,800 T Accounts Descriptior transaction label Work in Process BB- WIP Job Cost Sheet for C12 B8-RM 10,000 2a 1 40,000 2b 16,000 18,000 9 48,000 DM used for C12 DL used for C12 Applied OH for C12 16,000 10800 35,800 EB-RM $15,950 Total Mfg. Cost 1 $40,000 EB -WIP $50,800 Finished Goods unit MFG. cost 10 36,000 Manufacturing Overhead DM used fo 2a DM used fe 2b Indirect Ma 10,800 18,000 50 7b Job Cost Sheet for F16 EB FG$17,000 DM used for F16 DL used for F16 Applied OH for F16 18,000 Cost of Goods Sold DL used for 4a DL used for 4b 10 36,000 To IS 36,250 Total Mfg. Cost Actual Manufacturing OH Cost:s unit MFG. cost Cornell Camps Inc. Income Statement Less Cost of goods sold (Adjusted) Gross Margin Less Selling & Administrative Expenses 17,750 $14,800.00 Applied MOH Calculation Net Income $2,950.00 MH Used b 7a MH Used b 7b 8 14,800