E F G H 1 J K L M N 0 P o R B 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". 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 Acumulated Depreciation Goodwill Other Intangible Assets (Net) Accounts payable - trade Accrued liabilities 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). 2 3 4 5 6 7 7 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 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. 9 -0 -1 -2 3 4 5 -6 -7 -8 -9 0 -1 2 3 4 5 C. Other Requirments and Instructions: 1. Details of grading for B1 and B2 above: Using the correct account: Using the appropriate fomat: Correct number for each account: Complete everyting within the framework the template provided 1 point 1 point 1 point 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. A D F 1 2 B ABC Inc. Monthly Balance Sheet Sep 30, 20xx (5000) Total Current Assets mino 99 als Total Net PPE 19 20 Total Assets 21 22 23 24 25 26 Total Liabilities and Equity 27 28 29 30 31 D A B D E F ABC Inc. Statement of Cash Flow For the Month Ended Sep 30, 20xx $0 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 B D E F Debit 4,911,305.44 2,239.84 2,000.00 12,746,083.65 Credit (4,547,616.86) (1,752.61) (3,709.17) (12,240,457.82) 1 Trail Balance Period: Sep 20xx 2 GL Account Account Description 3 0-1020-000 Cash Disbursement Acct-- 4 0-1030-000 Payroll Checking Acct-- 5 0-1040-000 Petty Cash Fund-- 60-1110-000 Accounts Receivable - Trade-- 7 0-1120-000 Allowance for Doubtful Accts-- 8 0-1140-000 A/R - Employee Loan-- 90-1200-000 Inventory in Transit- 10 0-1210-000 Raw Materials - QA-- 11 0-1220-000 Raw Materials - Main Production-- 12 0-1225-000 Raw Material - DMR-- 13 0-1230-000 Work In Process - Materials-- 14 0-1232-000 Work In Process - Labor-- 15 0-1234-000 Work In Process - Overhead-- 16 0-1236-000 Work In Process - SubContract-- 17 0-1240-000 Finished Goods - Assembly 1 -- 18 0-1250-000 Finished Goods - Assembly 2-- 19 0-1310-000 Reserve - Excess & Obsolete Inventory-- 20 0-1330-000 Reserve - Inventory Shrink-- 21 0-1410-000 Prepaid Liability Insurance- Period OB 165,745.78 23,960.65 3,138.74 13,066,491.45 (105,705.34) 5,585.00 254,034.47 7,689,700.92 4,898,111.96 402,635.93 1,772,614.24 308,546.06 1,660,331.00 227,009.67 1,462,498.05 7,676,687.07 (244,905.60) (32,297.00) 160,695.93 50.00 425,359.91 1,828,238.21 362,949.24 2,002,488.60 5,120,995.45 1,220,049.38 5,871,593.83 786,699.48 2,461,568.50 7,584,140.09 (316.67) (254,034.47) (2,318,036.93) (716,862.27) (2,102,304.10) (4,811,875.81) (1,194,584.45) (5,783,529.62) (758,502.34) (2,485,557.16) (8,295,669.27) (97,387.19) Period CB 529,434.36 24,447.88 1,429.57 13,572,117.28 (105,705.34) 5,318.33 425,359.91 7,199,902. 20 4,544,198.93 302,720.43 2,081,733.88 334,010.99 1,748,395.21 255,206.81 1,438,509.39 6,965,157.89 (342,292.79) (32,297.00) 142,571.17 (18,124.76) Debit 8,749.00 Credit (18,124.76) (14,681.80) (4,228.75) (306.00) (19,945.51) (28,921.00) (1,740.50) 30,139.98 48,590.13 109,567.13 83,866.88 1 Trail Balance Period: Sep 20xx 2 GL Account Account Description 21 0-1410-000 Prepaid Liability Insurance-- 22 0-1420-000 Prepaid Taxes-- 23 0-1430-000 Prepaid Workers Comp-- 24 0-1450-000 Prepaid Vehicle Insurance- 25 0-1470-000 Prepaid Deposits- 26 0-1490-000 Prepaid Other-- 27 0-1700-000 Machinery & Equipment (M&E)-- 28 0-1710-000 Capitalized Tools-- 29 0-1720-000 Furniture & Fixtures (F&F)-- 30 0-1730-000 Computers -- 31 0-1770-000 Leasehold Improvements (LI)-- 32 0-1790-000 Construction in Progress-- 33 0-1800-000 Accum Depreciation - M&E- 34 0-1810-000 Accum Depreciation - Capitalized Tools-- 35 0-1820-000 Accum Depreciation - F&F-- 36 0-1830-000 Accum Depreciation - Computers-- 37 0-1870-000 Accum Depreciation - LI-- 38 0-1900-000 Patents -- 39 0-1910-000 Accum Amortization - Patents-- Period OB 160,695.93 71,294.58 48,400.25 (306.00) 138,604.09 83,299.55 7,128,295.51 2,021,377.44 989,779.42 4,543,701.77 1,593,966.00 345,880.08 (3,455, 263.42) (1,737,731.33) (945,448.23) (3,788,883.04) (2,482,338.05) 4,882,314.64 (961,406.21) Period CB 142,571.17 65,361.78 44,171.50 (612.00) 148,798.56 102,968.68 7,236,122.14 2,105,244.32 989,779.42 4,643,708.00 1,613,962.01 103,413.27 (3,515,064.25) (1,758,836.25) (947,005.87) (3,811,640.70) 2,483,035.31) 4,882,314.64 (982,849.91) 100,006.23 19,996.01 70,969.44 1,740.50 (313,436.25) (61,541.33) (21,104.92) (1,557.64) (22,757.66) (697.26) - (21,443.70) C E F G H Debit Credit (21,443.70) B 1 Trail Balance Period: Sep 20xx 2 GL Account Account Description 38 0-1900-000 Patents - 39 0-1910-000 Accum Amortization - Patents- 40 0-1960-000 Goodwill-- 41 0-2110-000 Accounts Payable - Trade-- 42 0-2120-000 Accrued A/P - Receipt Not Invoiced --Trade 43 0-2130-000 Accrued Utilities and Services-- 44 0-2310-000 Advances from Customers-- 45 0-2410-000 Accrued Payroll- 46 0-2420-000 Accrued Vacation Pay-- 47 0-2430-000 Accrued Holiday Pay-- 48 0-2460-000 Accrued Sales Commission-- 49 0-2540-000 Accrued 401K Payables-- 50 0-2550-000 Accrued Employee Benefits-- 51 0-2560-000 Accrued Warranty Reserve-Short Term 52 0-2610-000 Notes Payable - Long Term 53 0-3100-000 Common Stock- 54 0-3200-000 Additional Paid in Capital 55 0-3300-000 Beginning Retained Earnings- 56 57 Period OB 4,882,314.64 (961,406.21) 14,020,638.99 (1,288,908.79) (1,639,367.28) (528,644.71) (558,552.13) (498,756.46) (735,909.81) (19,936.39) (77,950.56) (90,198.68) (160,277.50) (269,412.23) (125,431.45) (105.44) (91,152.88) (50,654,789.40) 5,151,561.31 2,617,584.00 2,970,171.68 438,787.43 123,222.88 307,837.82 106,057.72 26,271.84 46,303.66 217,966.15 453,913.44 (2,042,906.31) (1,785,840.59) (729,247.44) (275,896.12) (425,213.80) (95,429.47) (26,000.00) (52,981.34) (211,913.88) (296,695.60) (18,585.63) (55,989.63) (102.30) (88,694.10) Period CB 4,882,314.64 (982,849.91) 14,020,638.99 (714,231.10) (455,036.19) (819,104.72) (711,225.37) (616,132.44) (725,281.56) (19,664.55) (84,628.24) (84,146.41) (3,059.66) (287,997.86) (162,464.71) (207.74) (179,846.98) (50,654,789.40) 6,029,841.19 18,956.37 53,126,459.91 (52,248,180.03) E F G H 1 J K L M N 0 P o R B 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". 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 Acumulated Depreciation Goodwill Other Intangible Assets (Net) Accounts payable - trade Accrued liabilities 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). 2 3 4 5 6 7 7 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 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. 9 -0 -1 -2 3 4 5 -6 -7 -8 -9 0 -1 2 3 4 5 C. Other Requirments and Instructions: 1. Details of grading for B1 and B2 above: Using the correct account: Using the appropriate fomat: Correct number for each account: Complete everyting within the framework the template provided 1 point 1 point 1 point 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. A D F 1 2 B ABC Inc. Monthly Balance Sheet Sep 30, 20xx (5000) Total Current Assets mino 99 als Total Net PPE 19 20 Total Assets 21 22 23 24 25 26 Total Liabilities and Equity 27 28 29 30 31 D A B D E F ABC Inc. Statement of Cash Flow For the Month Ended Sep 30, 20xx $0 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 B D E F Debit 4,911,305.44 2,239.84 2,000.00 12,746,083.65 Credit (4,547,616.86) (1,752.61) (3,709.17) (12,240,457.82) 1 Trail Balance Period: Sep 20xx 2 GL Account Account Description 3 0-1020-000 Cash Disbursement Acct-- 4 0-1030-000 Payroll Checking Acct-- 5 0-1040-000 Petty Cash Fund-- 60-1110-000 Accounts Receivable - Trade-- 7 0-1120-000 Allowance for Doubtful Accts-- 8 0-1140-000 A/R - Employee Loan-- 90-1200-000 Inventory in Transit- 10 0-1210-000 Raw Materials - QA-- 11 0-1220-000 Raw Materials - Main Production-- 12 0-1225-000 Raw Material - DMR-- 13 0-1230-000 Work In Process - Materials-- 14 0-1232-000 Work In Process - Labor-- 15 0-1234-000 Work In Process - Overhead-- 16 0-1236-000 Work In Process - SubContract-- 17 0-1240-000 Finished Goods - Assembly 1 -- 18 0-1250-000 Finished Goods - Assembly 2-- 19 0-1310-000 Reserve - Excess & Obsolete Inventory-- 20 0-1330-000 Reserve - Inventory Shrink-- 21 0-1410-000 Prepaid Liability Insurance- Period OB 165,745.78 23,960.65 3,138.74 13,066,491.45 (105,705.34) 5,585.00 254,034.47 7,689,700.92 4,898,111.96 402,635.93 1,772,614.24 308,546.06 1,660,331.00 227,009.67 1,462,498.05 7,676,687.07 (244,905.60) (32,297.00) 160,695.93 50.00 425,359.91 1,828,238.21 362,949.24 2,002,488.60 5,120,995.45 1,220,049.38 5,871,593.83 786,699.48 2,461,568.50 7,584,140.09 (316.67) (254,034.47) (2,318,036.93) (716,862.27) (2,102,304.10) (4,811,875.81) (1,194,584.45) (5,783,529.62) (758,502.34) (2,485,557.16) (8,295,669.27) (97,387.19) Period CB 529,434.36 24,447.88 1,429.57 13,572,117.28 (105,705.34) 5,318.33 425,359.91 7,199,902. 20 4,544,198.93 302,720.43 2,081,733.88 334,010.99 1,748,395.21 255,206.81 1,438,509.39 6,965,157.89 (342,292.79) (32,297.00) 142,571.17 (18,124.76) Debit 8,749.00 Credit (18,124.76) (14,681.80) (4,228.75) (306.00) (19,945.51) (28,921.00) (1,740.50) 30,139.98 48,590.13 109,567.13 83,866.88 1 Trail Balance Period: Sep 20xx 2 GL Account Account Description 21 0-1410-000 Prepaid Liability Insurance-- 22 0-1420-000 Prepaid Taxes-- 23 0-1430-000 Prepaid Workers Comp-- 24 0-1450-000 Prepaid Vehicle Insurance- 25 0-1470-000 Prepaid Deposits- 26 0-1490-000 Prepaid Other-- 27 0-1700-000 Machinery & Equipment (M&E)-- 28 0-1710-000 Capitalized Tools-- 29 0-1720-000 Furniture & Fixtures (F&F)-- 30 0-1730-000 Computers -- 31 0-1770-000 Leasehold Improvements (LI)-- 32 0-1790-000 Construction in Progress-- 33 0-1800-000 Accum Depreciation - M&E- 34 0-1810-000 Accum Depreciation - Capitalized Tools-- 35 0-1820-000 Accum Depreciation - F&F-- 36 0-1830-000 Accum Depreciation - Computers-- 37 0-1870-000 Accum Depreciation - LI-- 38 0-1900-000 Patents -- 39 0-1910-000 Accum Amortization - Patents-- Period OB 160,695.93 71,294.58 48,400.25 (306.00) 138,604.09 83,299.55 7,128,295.51 2,021,377.44 989,779.42 4,543,701.77 1,593,966.00 345,880.08 (3,455, 263.42) (1,737,731.33) (945,448.23) (3,788,883.04) (2,482,338.05) 4,882,314.64 (961,406.21) Period CB 142,571.17 65,361.78 44,171.50 (612.00) 148,798.56 102,968.68 7,236,122.14 2,105,244.32 989,779.42 4,643,708.00 1,613,962.01 103,413.27 (3,515,064.25) (1,758,836.25) (947,005.87) (3,811,640.70) 2,483,035.31) 4,882,314.64 (982,849.91) 100,006.23 19,996.01 70,969.44 1,740.50 (313,436.25) (61,541.33) (21,104.92) (1,557.64) (22,757.66) (697.26) - (21,443.70) C E F G H Debit Credit (21,443.70) B 1 Trail Balance Period: Sep 20xx 2 GL Account Account Description 38 0-1900-000 Patents - 39 0-1910-000 Accum Amortization - Patents- 40 0-1960-000 Goodwill-- 41 0-2110-000 Accounts Payable - Trade-- 42 0-2120-000 Accrued A/P - Receipt Not Invoiced --Trade 43 0-2130-000 Accrued Utilities and Services-- 44 0-2310-000 Advances from Customers-- 45 0-2410-000 Accrued Payroll- 46 0-2420-000 Accrued Vacation Pay-- 47 0-2430-000 Accrued Holiday Pay-- 48 0-2460-000 Accrued Sales Commission-- 49 0-2540-000 Accrued 401K Payables-- 50 0-2550-000 Accrued Employee Benefits-- 51 0-2560-000 Accrued Warranty Reserve-Short Term 52 0-2610-000 Notes Payable - Long Term 53 0-3100-000 Common Stock- 54 0-3200-000 Additional Paid in Capital 55 0-3300-000 Beginning Retained Earnings- 56 57 Period OB 4,882,314.64 (961,406.21) 14,020,638.99 (1,288,908.79) (1,639,367.28) (528,644.71) (558,552.13) (498,756.46) (735,909.81) (19,936.39) (77,950.56) (90,198.68) (160,277.50) (269,412.23) (125,431.45) (105.44) (91,152.88) (50,654,789.40) 5,151,561.31 2,617,584.00 2,970,171.68 438,787.43 123,222.88 307,837.82 106,057.72 26,271.84 46,303.66 217,966.15 453,913.44 (2,042,906.31) (1,785,840.59) (729,247.44) (275,896.12) (425,213.80) (95,429.47) (26,000.00) (52,981.34) (211,913.88) (296,695.60) (18,585.63) (55,989.63) (102.30) (88,694.10) Period CB 4,882,314.64 (982,849.91) 14,020,638.99 (714,231.10) (455,036.19) (819,104.72) (711,225.37) (616,132.44) (725,281.56) (19,664.55) (84,628.24) (84,146.41) (3,059.66) (287,997.86) (162,464.71) (207.74) (179,846.98) (50,654,789.40) 6,029,841.19 18,956.37 53,126,459.91 (52,248,180.03)
