accounting chart
suzies_119
ACCT301AFA20Case1BSandCFSTMT.xlsxRequirments
| FA20 301A Case 1 Requirements and Instructions: | ||||
| A. Basic information about the case: | ||||
| The purpose of this case is to practice composing financial statements from trial balance data. | ||||
| Partial trial balance data in Sep 20xx from ABC Inc. is provided in the tab "Trial Balance". | ||||
| The trial balance data sheet only provides Balance Sheet accounts, not the Income Statement's. | ||||
| You will need to use this data sheet to complete the Balance Sheet in tab "BL" and the | ||||
| Statement of Cash Flow in tab "CF". | ||||
| Please submit this case on Titanium | ||||
| Deadline to submit the case is Wednesday 12/9/2020. | ||||
| B. Questions: | ||||
| 1. Please complete the consolidated Balance Sheet using the following account items (2 points): | ||||
| Cash | ||||
| Accounts Receivable | ||||
| Allowance | ||||
| Accounts Receivable (Net) | ||||
| Gross Inventories | ||||
| Inventory Reserves | ||||
| Inventory (Net) | ||||
| Prepaid Expenses | ||||
| Property Plant & Equipment | ||||
| Leasehold improvements | ||||
| Accumulated Depreciation | ||||
| Goodwill | ||||
| Other Intangible Assets (Net) | ||||
| Accounts payable - trade | ||||
| Accrued liabilities Kristin Li: Kristin Li: This includes all major regular accrued liabilities other than trade. |
||||
| Other liabilities | ||||
| Long Term Liabilities | ||||
| Common Stock | ||||
| Additional Paid in Capital | ||||
| Retained Earnings (Accumulated prior to current year) | ||||
| Net Income (Current Yr Accumulated) | ||||
| 2. Please complete the Statement of Cash Flow. Please fill in the blanks in each section of the statement (2). | ||||
| 3. Please implement self balance check mechanism using Excel functions for each financial statement (1 point) | ||||
| 4. Impress me with your excel skills in this case. You will receive extra 1 point if you can turn this case into a template by | ||||
| utilizing functions in excel to link the trial balance data to the BL and CF worksheets and have the system automatically | ||||
| search and calculate the number. In another word, if you are given another month' of trial balance data, all you need to | ||||
| to is to dump the data into the "Trial Balance" worksheet and the BL and CF will be automatically updated. | ||||
| C. Other Requirments and Instructions: | ||||
| 1. Details of grading for B1 and B2 above: | ||||
| Using the correct account: | 1 point | |||
| Using the appropriate format: | 1 point | |||
| Correct number for each account: | 1 point | |||
| Complete everyting within the | ||||
| framework the template provided | 1 point | |||
| 2. You will receive 50% reduction of your points if submit after deadline but before final exam. Case submited after final exam will be rejected. |
BL
| ABC Inc. | |
| Monthly Balance Sheet | |
| Sep 30, 20xx | |
| ($000) | |
| Total Current Assets | |
| Total Net PPE | |
| Total Assets | |
| Total Liabilities and Equity | |
CF
| ABC Inc. | |
| Statement of Cash Flow | |
| For the Month Ended Sep 30, 20xx | |
| ($000) | |
| CASH FLOWS from OPERATING ACTIVITIES: | |
| CASH FLOWS from INVESTING ACTIVITIES: | |
| NET CASH PROVIDED by (Used for) FINANCING ACTIVITIES | |
| NET (DEC)/INC IN CASH and CASH EQUIVALENTS | |
| BEGINNING CASH and CASH EQUIVALENTS | |
| ENDING CASH and CASH EQUIVALENTS |
Trial Balance
| Trail Balance | Period: Sep 20xx | ||||
| GL Account | Account Description | Period OB | Debit | Credit | Period CB |
| 0-1020-000 | Cash Disbursement Acct-- | 165,745.78 | 4,911,305.44 | (4,547,616.86) | 529,434.36 |
| 0-1030-000 | Payroll Checking Acct-- | 23,960.65 | 2,239.84 | (1,752.61) | 24,447.88 |
| 0-1040-000 | Petty Cash Fund-- | 3,138.74 | 2,000.00 | (3,709.17) | 1,429.57 |
| 0-1110-000 | Accounts Receivable - Trade-- | 13,066,491.45 | 12,746,083.65 | (12,240,457.82) | 13,572,117.28 |
| 0-1120-000 | Allowance for Doubtful Accts-- | (105,705.34) | - 0 | - 0 | (105,705.34) |
| 0-1140-000 | A/R - Employee Loan-- | 5,585.00 | 50.00 | (316.67) | 5,318.33 |
| 0-1200-000 | Inventory in Transit-- | 254,034.47 | 425,359.91 | (254,034.47) | 425,359.91 |
| 0-1210-000 | Raw Materials - QA-- | 7,689,700.92 | 1,828,238.21 | (2,318,036.93) | 7,199,902.20 |
| 0-1220-000 | Raw Materials - Main Production-- | 4,898,111.96 | 362,949.24 | (716,862.27) | 4,544,198.93 |
| 0-1225-000 | Raw Material - DMR-- | 402,535.93 | 2,002,488.60 | (2,102,304.10) | 302,720.43 |
| 0-1230-000 | Work In Process - Materials-- | 1,772,614.24 | 5,120,995.45 | (4,811,875.81) | 2,081,733.88 |
| 0-1232-000 | Work In Process - Labor-- | 308,546.06 | 1,220,049.38 | (1,194,584.45) | 334,010.99 |
| 0-1234-000 | Work In Process - Overhead-- | 1,660,331.00 | 5,871,593.83 | (5,783,529.62) | 1,748,395.21 |
| 0-1236-000 | Work In Process - SubContract-- | 227,009.67 | 786,699.48 | (758,502.34) | 255,206.81 |
| 0-1240-000 | Finished Goods - Assembly 1-- | 1,462,498.05 | 2,461,568.50 | (2,485,557.16) | 1,438,509.39 |
| 0-1250-000 | Finished Goods - Assembly 2-- | 7,676,687.07 | 7,584,140.09 | (8,295,669.27) | 6,965,157.89 |
| 0-1310-000 | Reserve - Excess & Obsolete Inventory-- | (244,905.60) | (97,387.19) | (342,292.79) | |
| 0-1330-000 | Reserve - Inventory Shrink-- | (32,297.00) | (32,297.00) | ||
| 0-1410-000 | Prepaid Liability Insurance-- | 160,695.93 | - 0 | (18,124.76) | 142,571.17 |
| 0-1420-000 | Prepaid Taxes-- | 71,294.58 | 8,749.00 | (14,681.80) | 65,361.78 |
| 0-1430-000 | Prepaid Workers Comp-- | 48,400.25 | - 0 | (4,228.75) | 44,171.50 |
| 0-1450-000 | Prepaid Vehicle Insurance-- | (306.00) | - 0 | (306.00) | (612.00) |
| 0-1470-000 | Prepaid Deposits-- | 138,604.09 | 30,139.98 | (19,945.51) | 148,798.56 |
| 0-1490-000 | Prepaid Other-- | 83,299.55 | 48,590.13 | (28,921.00) | 102,968.68 |
| 0-1700-000 | Machinery & Equipment (M&E)-- | 7,128,295.51 | 109,567.13 | (1,740.50) | 7,236,122.14 |
| 0-1710-000 | Capitalized Tools-- | 2,021,377.44 | 83,866.88 | - 0 | 2,105,244.32 |
| 0-1720-000 | Furniture & Fixtures (F&F)-- | 989,779.42 | - 0 | - 0 | 989,779.42 |
| 0-1730-000 | Computers -- | 4,543,701.77 | 100,006.23 | - 0 | 4,643,708.00 |
| 0-1770-000 | Leasehold Improvements (LI)-- | 1,593,966.00 | 19,996.01 | - 0 | 1,613,962.01 |
| 0-1790-000 | Construction in Progress-- | 345,880.08 | 70,969.44 | (313,436.25) | 103,413.27 |
| 0-1800-000 | Accum Depreciation - M&E-- | (3,455,263.42) | 1,740.50 | (61,541.33) | (3,515,064.25) |
| 0-1810-000 | Accum Depreciation - Capitalized Tools-- | (1,737,731.33) | - 0 | (21,104.92) | (1,758,836.25) |
| 0-1820-000 | Accum Depreciation - F&F-- | (945,448.23) | - 0 | (1,557.64) | (947,005.87) |
| 0-1830-000 | Accum Depreciation - Computers-- | (3,788,883.04) | - 0 | (22,757.66) | (3,811,640.70) |
| 0-1870-000 | Accum Depreciation - LI-- | (2,482,338.05) | - 0 | (697.26) | (2,483,035.31) |
| 0-1900-000 | Patents -- | 4,882,314.64 | 4,882,314.64 | ||
| 0-1910-000 | Accum Amortization - Patents-- | (961,406.21) | - 0 | (21,443.70) | (982,849.91) |
| 0-1960-000 | Goodwill-- | 14,020,638.99 | - 0 | - 0 | 14,020,638.99 |
| 0-2110-000 | Accounts Payable - Trade-- tc={17D64D7B-9A20-46EA-AF81-25FACF8018E4}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Trade refers to the transactions for the purchases of repeating regular tangible items, such as raw materials, warehouse supplies, office supplies, etc. | (1,288,908.79) | 2,617,584.00 | (2,042,906.31) | (714,231.10) |
| 0-2120-000 | Accrued A/P - Receipt Not Invoiced --Trade | (1,639,367.28) | 2,970,171.68 | (1,785,840.59) | (455,036.19) |
| 0-2130-000 | Accrued Utilities and Services-- | (528,644.71) | 438,787.43 | (729,247.44) | (819,104.72) |
| 0-2310-000 | Advances from Customers-- Kristin Li: Kristin Li: This is the payments received from customers before services/products delivered. | (558,552.13) | 123,222.88 | (275,896.12) | (711,225.37) |
| 0-2410-000 | Accrued Payroll-- | (498,756.46) | 307,837.82 | (425,213.80) | (616,132.44) |
| 0-2420-000 | Accrued Vacation Pay-- | (735,909.81) | 106,057.72 | (95,429.47) | (725,281.56) |
| 0-2430-000 | Accrued Holiday Pay-- | (19,936.39) | 26,271.84 | (26,000.00) | (19,664.55) |
| 0-2460-000 | Accrued Sales Commission-- | (77,950.56) | 46,303.66 | (52,981.34) | (84,628.24) |
| 0-2540-000 | Accrued 401K Payables-- | (90,198.68) | 217,966.15 | (211,913.88) | (84,146.41) |
| 0-2550-000 | Accrued Employee Benefits-- | (160,277.50) | 453,913.44 | (296,695.60) | (3,059.66) |
| 0-2560-000 | Accrued Warranty Reserve-Short Term | (269,412.23) | - 0 | (18,585.63) | (287,997.86) |
| 0-2610-000 | Notes Payable - Long Term | (125,431.45) | 18,956.37 | (55,989.63) | (162,464.71) |
| 0-3100-000 | Common Stock-- | (105.44) | (102.30) | (207.74) | |
| 0-3200-000 | Additional Paid in Capital | (91,152.88) | (88,694.10) | (179,846.98) | |
| 0-3300-000 | Beginning Retained Earnings-- | (50,654,789.40) | - 0 | (50,654,789.40) | |
| 5,151,561.31 | 53,126,459.91 | (52,248,180.03) | 6,029,841.19 | ||
