EXCEL assignment
Pkesh
Excel2.xlsmInstructions
| INSTRUCTIONS |
| 1) Create a formula in the yellow/"Financial Statement Line" column on tab (2) as follows: You should use an Excel formula tool. Your formula must identify and map each General Ledger account (column 1 of tab 2) to a financial statement line item. To do this, use the Trial Balance key at tab (1). Every General Ledger account should have a Financial Statement Line assigned. |
| 2) Create a formula in the yellow/"Financial Statement Line" column on tab (3) as follows: You should use an Excel formula tool, and your formula must identify and map each General Ledger account (column 1 of tab 3) to a financial statement line item. To do this, use the Trial Balance key at tab (1). Every General Ledger account should have a Financial Statement Line assigned. Next, complete the same for the yellow/"Natural Line item" column so that the entire table is complete with line item names assigned to each General Ledger account on the Income Statement. |
| 3) Using the completed tables from steps (1) and (2), create a pivot table of the balance sheet. Next, create a pivot table of the income statement. [A placeholder tab is in this file to demonstrate there will be a tab in your completed project. However, it will not be used as when creating a pivot table Excel will generate the new tab automatically and the labeled Pivot tab will become N/A as it was included here for visual/instruction purposes only]. |
| 4) Based on your pivot table(s), identify a trend (by month) or key observation in trend. The submission will be (1) your pivot table and (2) your presentation of the trend and (3) you write up of the trend. |
| 5) Present the trend identified in step (4) on the tab "Placeholder trend observation" |
| 6) Using business writing and presentation, provide written comments explaining the trend and why it was observed by you, and based on your analysis why it is meaningful. (Use tab Placeholder Written Comments) |
(1)Trial Balance Key
| GL Detail | Description | BS/IS | Financial Statement Line Item | GL Detail | Description | Natural Line Item | |
| 10000-0000-000 | Wells Fargo | BS | Cash & Cash equivalents | 40001-0000-000 | Domestic sales | Gross revenue | |
| 10100-0000-000 | Petty Cash | BS | Cash & Cash equivalents | 40001-0000-010 | International sales | Gross revenue | |
| 10600-0000-000 | Investments | BS | Cash & Cash equivalents | 40002-0000-000 | Other miscellaneous sales | Gross revenue | |
| 11000-0000-000 | Trade Receivables | BS | Accounts receivable, net | 40815-0000-000 | Discounts | Gross revenue | |
| 11001-0000-000 | Allowance for doubtful accounts | BS | Accounts receivable, net | 50001-0000-000 | Cost of sales | Cost of sales | |
| 13020-0000-000 | Raw materials | BS | Inventory | 50001-0000-010 | Variances | Cost of sales | |
| 13021-0000-000 | Work in progress | BS | Inventory | 50002-0000-000 | Indirect cost of sales | Cost of sales | |
| 13022-0000-000 | Finished goods | BS | Inventory | 50002-0000-010 | Inventory shrink provision | Cost of sales | |
| 13023-0000-000 | Inventory provision | BS | Inventory | 50003-0000-000 | Direct labor | People related cost | |
| 13820-0000-000 | Prepaid insurance | BS | Prepaid assets | 50003-0000-010 | Vacation | People related cost | |
| 13825-0000-000 | Prepaid property taxes | BS | Prepaid assets | 50004-0000-000 | Overtime | People related cost | |
| 15000-0000-000 | Equipment | BS | PP&E | 50004-0000-010 | Natural gas | Facility | |
| 15010-0000-000 | Land | BS | PP&E | 50005-0000-000 | Rent | Facility | |
| 15030-0000-000 | Accumulated depreciation | BS | PP&E | 50005-0000-010 | Bonuses | People related cost | |
| 20000-0000-000 | Accounts payable | BS | Payables | 60000-0000-000 | Salaries | People related cost | |
| 20010-0000-000 | Wages payable | BS | Payables | 60000-1000-000 | Vacation | People related cost | |
| 20015-0000-000 | Vacation payable | BS | Payables | 60000-1200-000 | Commission | People related cost | |
| 20050-0000-000 | Accrued expenses | BS | Accrued expenses | 60000-1220-000 | Bonuses | People related cost | |
| 23001-0000-000 | Short-term deferred revenue | BS | Short-term labilities | 60000-1240-000 | Overtime | People related cost | |
| 25006-0000-000 | Short-term debt | BS | Short-term labilities | 70000-0000-000 | Salaries | People related cost | |
| 26001-0000-000 | Long-term deferred revenue | BS | Long-term liabilities | 70000-1000-000 | Vacation | People related cost | |
| 26009-0000-000 | Long-term debt | BS | Long-term liabilities | 70000-1200-000 | Commission | People related cost | |
| 31000-0000-000 | Equity | BS | Equity | 70000-1220-000 | Bonuses | People related cost | |
| 40001-0000-000 | Domestic sales | IS | Revenue | 70000-2000-000 | Advertising | Marketing & advertising | |
| 40001-0000-010 | International sales | IS | Revenue | 70000-2200-000 | Promotional | Marketing & advertising | |
| 40002-0000-000 | Other miscellaneous sales | IS | Revenue | 70000-2220-000 | Trade shows | Marketing & advertising | |
| 40815-0000-000 | Discounts | IS | Revenue | 70000-2230-000 | Magazines | Marketing & advertising | |
| 50001-0000-000 | Cost of sales | IS | Cost of sales | 80000-0000-000 | Salaries | People related cost | |
| 50001-0000-010 | Variances | IS | Cost of sales | 80000-1000-000 | Vacation | People related cost | |
| 50002-0000-000 | Indirect cost of sales | IS | Cost of sales | 80000-1200-000 | Commission | People related cost | |
| 50002-0000-010 | Inventory shrink provision | IS | Cost of sales | 80000-1220-000 | Bonuses | People related cost | |
| 50003-0000-000 | Direct labor | IS | Cost of sales | 80000-3000-000 | Bank fees | Other SG&A | |
| 50003-0000-010 | Vacation | IS | Cost of sales | 80000-3200-000 | Equipment and repairs | Other SG&A | |
| 50004-0000-000 | Overtime | IS | Cost of sales | 80000-3220-000 | Insurance | Other SG&A | |
| 50004-0000-010 | Natural gas | IS | Cost of sales | 80000-3320-000 | Legal | Other SG&A | |
| 50005-0000-000 | Rent | IS | Cost of sales | 80000-3420-000 | insurance | Other SG&A | |
| 50005-0000-010 | Bonuses | IS | Cost of sales | ||||
| 60000-0000-000 | Salaries | IS | Selling | ||||
| 60000-1000-000 | Vacation | IS | Selling | ||||
| 60000-1200-000 | Commission | IS | Selling | ||||
| 60000-1220-000 | Bonuses | IS | Selling | ||||
| 60000-1240-000 | Overtime | IS | Selling | ||||
| 70000-0000-000 | Salaries | IS | Marketing | ||||
| 70000-1000-000 | Vacation | IS | Marketing | ||||
| 70000-1200-000 | Commission | IS | Marketing | ||||
| 70000-1220-000 | Bonuses | IS | Marketing | ||||
| 70000-2000-000 | Advertising | IS | Marketing | ||||
| 70000-2200-000 | Promotional | IS | Marketing | ||||
| 70000-2220-000 | Trade shows | IS | Marketing | ||||
| 70000-2230-000 | Magazines | IS | Marketing | ||||
| 80000-0000-000 | Salaries | IS | SG&A | ||||
| 80000-1000-000 | Vacation | IS | SG&A | ||||
| 80000-1200-000 | Commission | IS | SG&A | ||||
| 80000-1220-000 | Bonuses | IS | SG&A | ||||
| 80000-3000-000 | Bank fees | IS | SG&A | ||||
| 80000-3200-000 | Equipment and repairs | IS | SG&A | ||||
| 80000-3220-000 | Insurance | IS | SG&A | ||||
| 80000-3320-000 | Legal | IS | SG&A | ||||
| 80000-3420-000 | insurance | IS | SG&A |
(2) Raw - BS
| General Ledger | Description | Amount | Financial Statement Line | Date |
| 10000-0000-000 | Wells Fargo | 433,210 | Jan-10 | |
| 10100-0000-000 | Petty Cash | 513 | Jan-10 | |
| 10600-0000-000 | Investments | 100,000 | Jan-10 | |
| 11000-0000-000 | Trade Receivables | 323,513 | Jan-10 | |
| 11001-0000-000 | Allowance for doubtful accounts | (30,000) | Jan-10 | |
| 13020-0000-000 | Raw materials | 351,510 | Jan-10 | |
| 13021-0000-000 | Work in progress | 535,130 | Jan-10 | |
| 13022-0000-000 | Finished goods | 535,315 | Jan-10 | |
| 13023-0000-000 | Inventory provision | (100,000) | Jan-10 | |
| 13820-0000-000 | Prepaid insurance | 51,410 | Jan-10 | |
| 13825-0000-000 | Prepaid property taxes | 41,230 | Jan-10 | |
| 15000-0000-000 | Equipment | 340,000 | Jan-10 | |
| 15010-0000-000 | Land | 24,000 | Jan-10 | |
| 15030-0000-000 | Accumulated depreciation | (20,500) | Jan-10 | |
| 20000-0000-000 | Accounts payable | (10,414) | Jan-10 | |
| 20010-0000-000 | Wages payable | (40,421) | Jan-10 | |
| 20015-0000-000 | Vacation payable | (4,124) | Jan-10 | |
| 20050-0000-000 | Accrued expenses | (12,412) | Jan-10 | |
| 23001-0000-000 | Short-term deferred revenue | (12,521) | Jan-10 | |
| 25006-0000-000 | Short-term debt | (25,101) | Jan-10 | |
| 26001-0000-000 | Long-term deferred revenue | (12,312) | Jan-10 | |
| 26009-0000-000 | Long-term debt | (51,231) | Jan-10 | |
| 31000-0000-000 | Equity | (1,380,411) | Jan-10 | |
| 10000-0000-000 | Wells Fargo | 433,210 | Feb-10 | |
| 10100-0000-000 | Petty Cash | 513 | Feb-10 | |
| 10600-0000-000 | Investments | 100,000 | Feb-10 | |
| 11000-0000-000 | Trade Receivables | 323,513 | Feb-10 | |
| 11001-0000-000 | Allowance for doubtful accounts | (30,000) | Feb-10 | |
| 13020-0000-000 | Raw materials | 351,510 | Feb-10 | |
| 13021-0000-000 | Work in progress | 535,130 | Feb-10 | |
| 13022-0000-000 | Finished goods | 535,315 | Feb-10 | |
| 13023-0000-000 | Inventory provision | (100,000) | Feb-10 | |
| 13820-0000-000 | Prepaid insurance | 51,410 | Feb-10 | |
| 13825-0000-000 | Prepaid property taxes | 41,230 | Feb-10 | |
| 15000-0000-000 | Equipment | 340,000 | Feb-10 | |
| 15010-0000-000 | Land | 24,000 | Feb-10 | |
| 15030-0000-000 | Accumulated depreciation | (20,500) | Feb-10 | |
| 20000-0000-000 | Accounts payable | (10,414) | Feb-10 | |
| 20010-0000-000 | Wages payable | (40,421) | Feb-10 | |
| 20015-0000-000 | Vacation payable | (4,124) | Feb-10 | |
| 20050-0000-000 | Accrued expenses | (12,412) | Feb-10 | |
| 23001-0000-000 | Short-term deferred revenue | (12,521) | Feb-10 | |
| 25006-0000-000 | Short-term debt | (25,101) | Feb-10 | |
| 26001-0000-000 | Long-term deferred revenue | (12,312) | Feb-10 | |
| 26009-0000-000 | Long-term debt | (51,231) | Feb-10 | |
| 31000-0000-000 | Equity | (1,380,842) | Feb-10 | |
| 10000-0000-000 | Wells Fargo | 433,210 | Mar-10 | |
| 10100-0000-000 | Petty Cash | 513 | Mar-10 | |
| 10600-0000-000 | Investments | 100,000 | Mar-10 | |
| 11000-0000-000 | Trade Receivables | 323,513 | Mar-10 | |
| 11001-0000-000 | Allowance for doubtful accounts | (30,000) | Mar-10 | |
| 13020-0000-000 | Raw materials | 351,510 | Mar-10 | |
| 13021-0000-000 | Work in progress | 535,130 | Mar-10 | |
| 13022-0000-000 | Finished goods | 535,315 | Mar-10 | |
| 13023-0000-000 | Inventory provision | (100,000) | Mar-10 | |
| 13820-0000-000 | Prepaid insurance | 51,410 | Mar-10 | |
| 13825-0000-000 | Prepaid property taxes | 41,230 | Mar-10 | |
| 15000-0000-000 | Equipment | 340,000 | Mar-10 | |
| 15010-0000-000 | Land | 24,000 | Mar-10 | |
| 15030-0000-000 | Accumulated depreciation | (20,500) | Mar-10 | |
| 20000-0000-000 | Accounts payable | (10,414) | Mar-10 | |
| 20010-0000-000 | Wages payable | (40,421) | Mar-10 | |
| 20015-0000-000 | Vacation payable | (4,124) | Mar-10 | |
| 20050-0000-000 | Accrued expenses | (12,412) | Mar-10 | |
| 23001-0000-000 | Short-term deferred revenue | (12,521) | Mar-10 | |
| 25006-0000-000 | Short-term debt | (25,101) | Mar-10 | |
| 26001-0000-000 | Long-term deferred revenue | (12,312) | Mar-10 | |
| 26009-0000-000 | Long-term debt | (51,231) | Mar-10 | |
| 31000-0000-000 | Equity | (1,380,842) | Mar-10 | |
(3) Raw - IS
| General Ledger | Description | Amount | Financial Statement Line | Natural Line Item | Date |
| 40001-0000-000 | Domestic sales | (620,000) | Jan-10 | ||
| 40001-0000-010 | International sales | (720,999) | Jan-10 | ||
| 40002-0000-000 | Other miscellaneous sales | (50,000) | Jan-10 | ||
| 40815-0000-000 | Discounts | 10,000 | Jan-10 | ||
| 50001-0000-000 | Cost of sales | 341 | Jan-10 | ||
| 50001-0000-010 | Variances | 43,432 | Jan-10 | ||
| 50002-0000-000 | Indirect cost of sales | 43,242 | Jan-10 | ||
| 50002-0000-010 | Inventory shrink provision | 3,561 | Jan-10 | ||
| 50003-0000-000 | Direct labor | 66,131 | Jan-10 | ||
| 50003-0000-010 | Vacation | 12,356 | Jan-10 | ||
| 50004-0000-000 | Overtime | 614 | Jan-10 | ||
| 50004-0000-010 | Natural gas | 1,600 | Jan-10 | ||
| 50005-0000-000 | Rent | 6,000 | Jan-10 | ||
| 50005-0000-010 | Bonuses | 7,000 | Jan-10 | ||
| 60000-0000-000 | Salaries | 20,101 | Jan-10 | ||
| 60000-1000-000 | Vacation | 4,310 | Jan-10 | ||
| 60000-1200-000 | Commission | 34,134 | Jan-10 | ||
| 60000-1220-000 | Bonuses | 4,313 | Jan-10 | ||
| 60000-1240-000 | Overtime | 4,314 | Jan-10 | ||
| 70000-0000-000 | Salaries | 7,613 | Jan-10 | ||
| 70000-1000-000 | Vacation | 4,314 | Jan-10 | ||
| 70000-1200-000 | Commission | 3,141 | Jan-10 | ||
| 70000-1220-000 | Bonuses | 8,712 | Jan-10 | ||
| 70000-2000-000 | Advertising | 9,471 | Jan-10 | ||
| 70000-2200-000 | Promotional | 4,837 | Jan-10 | ||
| 70000-2220-000 | Trade shows | 342 | Jan-10 | ||
| 70000-2230-000 | Magazines | 4,314 | Jan-10 | ||
| 80000-0000-000 | Salaries | 8,971 | Jan-10 | ||
| 80000-1000-000 | Vacation | 4,314 | Jan-10 | ||
| 80000-1200-000 | Commission | 8,741 | Jan-10 | ||
| 80000-1220-000 | Bonuses | 9,431 | Jan-10 | ||
| 80000-3000-000 | Bank fees | 8,743 | Jan-10 | ||
| 80000-3200-000 | Equipment and repairs | 8,751 | Jan-10 | ||
| 80000-3320-000 | Legal | 974 | Jan-10 | ||
| 80000-3420-000 | insurance | 497 | Jan-10 | ||
| 40001-0000-000 | Domestic sales | (682,000) | Feb-10 | ||
| 40001-0000-010 | International sales | (793,099) | Feb-10 | ||
| 40002-0000-000 | Other miscellaneous sales | (55,000) | Feb-10 | ||
| 40815-0000-000 | Discounts | 11,000 | Feb-10 | ||
| 50001-0000-000 | Cost of sales | 375 | Feb-10 | ||
| 50001-0000-010 | Variances | 47,775 | Feb-10 | ||
| 50002-0000-000 | Indirect cost of sales | 47,566 | Feb-10 | ||
| 50002-0000-010 | Inventory shrink provision | 3,917 | Feb-10 | ||
| 50003-0000-000 | Direct labor | 72,744 | Feb-10 | ||
| 50003-0000-010 | Vacation | 13,592 | Feb-10 | ||
| 50004-0000-000 | Overtime | 675 | Feb-10 | ||
| 50004-0000-010 | Natural gas | 1,760 | Feb-10 | ||
| 50005-0000-000 | Rent | 6,600 | Feb-10 | ||
| 50005-0000-010 | Bonuses | 7,700 | Feb-10 | ||
| 60000-0000-000 | Salaries | 22,111 | Feb-10 | ||
| 60000-1000-000 | Vacation | 4,741 | Feb-10 | ||
| 60000-1200-000 | Commission | 37,547 | Feb-10 | ||
| 60000-1220-000 | Bonuses | 4,744 | Feb-10 | ||
| 60000-1240-000 | Overtime | 4,745 | Feb-10 | ||
| 70000-0000-000 | Salaries | 8,374 | Feb-10 | ||
| 70000-1000-000 | Vacation | 4,745 | Feb-10 | ||
| 70000-1200-000 | Commission | 3,455 | Feb-10 | ||
| 70000-1220-000 | Bonuses | 9,583 | Feb-10 | ||
| 70000-2000-000 | Advertising | 10,418 | Feb-10 | ||
| 70000-2200-000 | Promotional | 5,321 | Feb-10 | ||
| 70000-2220-000 | Trade shows | 376 | Feb-10 | ||
| 70000-2230-000 | Magazines | 4,745 | Feb-10 | ||
| 80000-0000-000 | Salaries | 9,868 | Feb-10 | ||
| 80000-1000-000 | Vacation | 4,745 | Feb-10 | ||
| 80000-1200-000 | Commission | 9,615 | Feb-10 | ||
| 80000-1220-000 | Bonuses | 10,374 | Feb-10 | ||
| 80000-3000-000 | Bank fees | 9,617 | Feb-10 | ||
| 80000-3200-000 | Equipment and repairs | 9,626 | Feb-10 | ||
| 80000-3220-000 | Insurance | 1,071 | Feb-10 | ||
| 80000-3320-000 | Legal | 547 | Feb-10 | ||
| 80000-3420-000 | insurance | 25,000 | Feb-10 | ||
| 40001-0000-000 | Domestic sales | (872,409) | Mar-10 | ||
| 40001-0000-010 | International sales | (60,500) | Mar-10 | ||
| 40002-0000-000 | Other miscellaneous sales | 12,100 | Mar-10 | ||
| 40815-0000-000 | Discounts | 413 | Mar-10 | ||
| 50001-0000-000 | Cost of sales | 52,553 | Mar-10 | ||
| 50001-0000-010 | Variances | 52,323 | Mar-10 | ||
| 50002-0000-000 | Indirect cost of sales | 4,309 | Mar-10 | ||
| 50002-0000-010 | Inventory shrink provision | 80,019 | Mar-10 | ||
| 50003-0000-000 | Direct labor | 14,951 | Mar-10 | ||
| 50003-0000-010 | Vacation | 743 | Mar-10 | ||
| 50004-0000-000 | Overtime | 1,936 | Mar-10 | ||
| 50004-0000-010 | Natural gas | 7,260 | Mar-10 | ||
| 50005-0000-000 | Rent | 8,470 | Mar-10 | ||
| 50005-0000-010 | Bonuses | 24,322 | Mar-10 | ||
| 60000-0000-000 | Salaries | 5,215 | Mar-10 | ||
| 60000-1000-000 | Vacation | 41,302 | Mar-10 | ||
| 60000-1200-000 | Commission | 5,219 | Mar-10 | ||
| 60000-1220-000 | Bonuses | 5,220 | Mar-10 | ||
| 60000-1240-000 | Overtime | 9,212 | Mar-10 | ||
| 70000-0000-000 | Salaries | 5,220 | Mar-10 | ||
| 70000-1000-000 | Vacation | 3,801 | Mar-10 | ||
| 70000-1200-000 | Commission | 10,542 | Mar-10 | ||
| 70000-1220-000 | Bonuses | 11,460 | Mar-10 | ||
| 70000-2000-000 | Advertising | 5,853 | Mar-10 | ||
| 70000-2200-000 | Promotional | 414 | Mar-10 | ||
| 70000-2220-000 | Trade shows | 5,220 | Mar-10 | ||
| 70000-2230-000 | Magazines | 10,855 | Mar-10 | ||
| 80000-0000-000 | Salaries | 5,220 | Mar-10 | ||
| 80000-1000-000 | Vacation | 10,577 | Mar-10 | ||
| 80000-1200-000 | Commission | 11,412 | Mar-10 | ||
| 80000-1220-000 | Bonuses | 10,579 | Mar-10 | ||
| 80000-3000-000 | Bank fees | 10,589 | Mar-10 | ||
| 80000-3200-000 | Equipment and repairs | 1,179 | Mar-10 | ||
| 80000-3220-000 | Insurance | 601 | Mar-10 | ||
| 80000-3320-000 | Legal | 33,000 | Mar-10 | ||
| 80000-3420-000 | insurance | 15,000 | Mar-10 | ||
