Assignment Tasks

In this assignment, students will have hands-on experience in developing an integrated framework for controlling, monitoring, and analysis of company financial information. In addition, the task aims to help the student understand financial issues from a corporate management perspective. It will provide the platform for continuing management development All required data given in the attached Construction Finance-Coursework-Data in Excel sheet. Assuming the current date as AUG 2020, the up to date financial reports (Balance Sheet, Income Statement and Statement of Cash Flow) data are given in Table1, Table2, and Table 3 respectively. The additional transaction data is given in Table 4 Requirements: The students are required to develop a report (not less than 1200-1500 words) along with Excel spreadsheets as an aid in preparing and updating of financial reports. Each student is requested to: 1. Use the given transaction data (Table 4) to update the missed items in the final financial reports and critically discuss the company financial performance trend (use Excel Formulas to do any required calculations and create Excel charts as a helping tool in the analysis of financial trend Horizontal Analysis). 2. Use Excel to prepare the balance sheet , income statement and prepare a tabular analysis which shows the effects of the transactions on the accounting equation . 3. Based on the financial ratios calculations, critically discuss ( any five ) in detail the company financial position and the linkages between the different types of financial report (use Excel Formulas to calculate financial ratios and use Excel charts to support your discussion).

BALANCE SHEELS DECEMBER 31, 2019, 2018 AND 2017 2019 2018 2017 OMR OMR ? ? 565,052 15,449 329,975 12,205 693,320 19,638 254,416 10,633 ? LASSES Current Assets: Cash Certificate of deposit Accounts receivable - trade, net Accounts receivable - other Costs and estimated earnings in excess of billings on uncompleted contracts Inventory Prepaid Expenses Employee Advances ? ? 23,687 144,525 24,337 15,000 29,980 182,140 28,177 ? Total Current Assets ? OMR ? Fixed Assets: Land Buildings Furniture and Equipment Computer Equipment Vehicles Less: Accumulated Depreciatio Total Fixed Assets ? 1,720,145 617,841 278,637 55,490 145,690 OMR 2,110,232 787 589 234,819 58,321 109,989 2 2 CW DATA B Total Fixed Assets ? OMR 464,531 OMR 595,761 Other Assets: Trademarks Patents Security Deposits Other Assets Total Other Assets 2 480.374 ? 374,961 ? ? ? OMR 152,289 OMR 169,554 LIABILITIES Current Liabilities: Current maturities of long-tern Billings in excess of costs and estimated earnings on uncompleted contracts Accounts Payable Business Credit Cards Sales Tax Payable Payroll Liabilities Accrued liabilities Deferred income taxes Total Current Liabilities ? ? ? ? 26,273 358,457 72,847 22,334 22,365 16,249 12,959 23,583 401,241 76,027 25,406 26,956 14,380 13,835 ? OMR OMR Long-Term Liabilities: Notes Payable Mortgage Payable Less: Current portion of Long-te CW DATA 88,245 108,856 (12,000) 75,377 130,293 (12,000) ? ? (12,000) (12,000) Less: Current portion of Long-te Total Long-Term Liabilities ? OMR OMR 2 EQUITY Capital Stock/Partner's Equity Opening Retained Earnings Dividends Paid/Owner's Draw Net Income (Loss) Total Equity 20.000 449,182 (15,000) 14,041 20,000 452,076 (15,000) 10,719 2 2 Table 2: AAA Company Income Statement AAA CONSTRUCTION COMPANY INC. INCOME STATEMENT YEARS ENDED DECEMBER 31, 2019, 2018 AND 2017 2018 2017 OMR OMR 6,207,089 5,211,737 7,454,302 4,992,475 2019 CONTRACT REVENUE OMR 6,827,798 CONTRACT COSTS 5,732,911 Gross profit GENERAL AND ADMINISTRATIVE E OMR 582,386 Operating profit 12 OMR 529,442 OMR 443,399 2 IP Company Income Statement OMR OMR OMR OTHER INCOME (EXPENSE) Customer finance charges Miscellaneous income Gain on sale of assets 8,163 5,888 5,566 7,421 5,353 5,060 5,438 3,896 5,447 CA OW DATA Type here to search O 6 AAAle Interest income Interest expense Total other income (expense) 5,574 (40,225) 5,067 (36,568) 4,975 (36,498) Earnings before income tax expense INCOME TAX EXPENSE OMR 30,373 OMR 27,612 OMR 21,760 NET EARNINGS Table 3: AAA Company Cash Flow Statement AAA CONSTRUCTION COMPANY, INC. STATEMENTS OF CASH FLOWS DECEMBER 31, 2019, 2018 AND 2017 2019 2018 OPERATING ACTIVITIES Net earnings OMR 99,112 OMR 90,102 2017 OMR 90,360 ? ? Adjustments to reconcile net earnings to net cash provided by operating activities: Depreciation Gain on sale of assets Change in allowance for doul Change in deferred income ta (Increase) decrease in: Accounts receivable - trade A --Unternishin othor CW DATA (5,511) 3,314 2,959 (5,010) 3,013 2,690 (6,272) 6,542 (5 559) 419,375 381,250 563,017 02 1000 Apa 419,375 11,887 381,250 10,806 563,017 14,825 30,507 37,463 15,887 27,734 34,057 14,443 20,754 32,773 12,009 AAA Company Cash Flow Stataman Accounts receivable - trade Accounts receivable - other Costs and estimated earnings in excess of billings on uncompleted contracts Inventory Prepaid expenses Increase (decrease): Accounts payable - trade Billings in excess of costs and estimated earnings on uncompleted contracts Federal income tax payable Accrued liabilities 38,902 35,365 42,487 16,915 15,377 11,213 20,308 19,169 18,462 17,426 12,744 17,126 NET CASH PROVIDED BY OPERATING ACTIVITIES INVESTING ACTIVITIES Proceeds from sale of assets OMR Purchase of property and equipi' OMR OMR 12,448 (57,611) 11,316 (52,374) 8,876 (76,768) NET CASH USED IN INVESTING ACTIVITIES FINANCING ACTIVITIES Proceeds from issuance of lon OMR 62,721 OMR 57,019 OMR 50,151 CW DATA i Type here to search OMR OMR HNANCING ACTIVITIES Proceeds from issuance of lon OMR 62,721 Reduction of long-term debt (279,547) NET CASH USED IN FINANCING ACTIVITIES 57,019 (254,134) 50,151 (217,063) NET INCREASE IN CASH Cash at beginning of year Cash at end of year ? OMR 59,823 89,854 ? it is required to find the value in this cell Any missing/incorrect data should be reasonably; able 4: Detailed Transactions during 2018 Amount OMR Detailed Description of Transaction AAA invested cash in the business. Paid for office rent for the month Withdrew cash for personal use Received a cash payment for services provided Purchased gasoline on account Made a cash payment on the note payable Paid for utilities Paid for employee salaries Collected of accounts receivable 362,662 13,746 59,384 127,881 3,955 128,048 32,552 218,413 132,682 CW DATA BALANCE SHEELS DECEMBER 31, 2019, 2018 AND 2017 2019 2018 2017 OMR OMR ? ? 565,052 15,449 329,975 12,205 693,320 19,638 254,416 10,633 ? LASSES Current Assets: Cash Certificate of deposit Accounts receivable - trade, net Accounts receivable - other Costs and estimated earnings in excess of billings on uncompleted contracts Inventory Prepaid Expenses Employee Advances ? ? 23,687 144,525 24,337 15,000 29,980 182,140 28,177 ? Total Current Assets ? OMR ? Fixed Assets: Land Buildings Furniture and Equipment Computer Equipment Vehicles Less: Accumulated Depreciatio Total Fixed Assets ? 1,720,145 617,841 278,637 55,490 145,690 OMR 2,110,232 787 589 234,819 58,321 109,989 2 2 CW DATA B Total Fixed Assets ? OMR 464,531 OMR 595,761 Other Assets: Trademarks Patents Security Deposits Other Assets Total Other Assets 2 480.374 ? 374,961 ? ? ? OMR 152,289 OMR 169,554 LIABILITIES Current Liabilities: Current maturities of long-tern Billings in excess of costs and estimated earnings on uncompleted contracts Accounts Payable Business Credit Cards Sales Tax Payable Payroll Liabilities Accrued liabilities Deferred income taxes Total Current Liabilities ? ? ? ? 26,273 358,457 72,847 22,334 22,365 16,249 12,959 23,583 401,241 76,027 25,406 26,956 14,380 13,835 ? OMR OMR Long-Term Liabilities: Notes Payable Mortgage Payable Less: Current portion of Long-te CW DATA 88,245 108,856 (12,000) 75,377 130,293 (12,000) ? ? (12,000) (12,000) Less: Current portion of Long-te Total Long-Term Liabilities ? OMR OMR 2 EQUITY Capital Stock/Partner's Equity Opening Retained Earnings Dividends Paid/Owner's Draw Net Income (Loss) Total Equity 20.000 449,182 (15,000) 14,041 20,000 452,076 (15,000) 10,719 2 2 Table 2: AAA Company Income Statement AAA CONSTRUCTION COMPANY INC. INCOME STATEMENT YEARS ENDED DECEMBER 31, 2019, 2018 AND 2017 2018 2017 OMR OMR 6,207,089 5,211,737 7,454,302 4,992,475 2019 CONTRACT REVENUE OMR 6,827,798 CONTRACT COSTS 5,732,911 Gross profit GENERAL AND ADMINISTRATIVE E OMR 582,386 Operating profit 12 OMR 529,442 OMR 443,399 2 IP Company Income Statement OMR OMR OMR OTHER INCOME (EXPENSE) Customer finance charges Miscellaneous income Gain on sale of assets 8,163 5,888 5,566 7,421 5,353 5,060 5,438 3,896 5,447 CA OW DATA Type here to search O 6 AAAle Interest income Interest expense Total other income (expense) 5,574 (40,225) 5,067 (36,568) 4,975 (36,498) Earnings before income tax expense INCOME TAX EXPENSE OMR 30,373 OMR 27,612 OMR 21,760 NET EARNINGS Table 3: AAA Company Cash Flow Statement AAA CONSTRUCTION COMPANY, INC. STATEMENTS OF CASH FLOWS DECEMBER 31, 2019, 2018 AND 2017 2019 2018 OPERATING ACTIVITIES Net earnings OMR 99,112 OMR 90,102 2017 OMR 90,360 ? ? Adjustments to reconcile net earnings to net cash provided by operating activities: Depreciation Gain on sale of assets Change in allowance for doul Change in deferred income ta (Increase) decrease in: Accounts receivable - trade A --Unternishin othor CW DATA (5,511) 3,314 2,959 (5,010) 3,013 2,690 (6,272) 6,542 (5 559) 419,375 381,250 563,017 02 1000 Apa 419,375 11,887 381,250 10,806 563,017 14,825 30,507 37,463 15,887 27,734 34,057 14,443 20,754 32,773 12,009 AAA Company Cash Flow Stataman Accounts receivable - trade Accounts receivable - other Costs and estimated earnings in excess of billings on uncompleted contracts Inventory Prepaid expenses Increase (decrease): Accounts payable - trade Billings in excess of costs and estimated earnings on uncompleted contracts Federal income tax payable Accrued liabilities 38,902 35,365 42,487 16,915 15,377 11,213 20,308 19,169 18,462 17,426 12,744 17,126 NET CASH PROVIDED BY OPERATING ACTIVITIES INVESTING ACTIVITIES Proceeds from sale of assets OMR Purchase of property and equipi' OMR OMR 12,448 (57,611) 11,316 (52,374) 8,876 (76,768) NET CASH USED IN INVESTING ACTIVITIES FINANCING ACTIVITIES Proceeds from issuance of lon OMR 62,721 OMR 57,019 OMR 50,151 CW DATA i Type here to search OMR OMR HNANCING ACTIVITIES Proceeds from issuance of lon OMR 62,721 Reduction of long-term debt (279,547) NET CASH USED IN FINANCING ACTIVITIES 57,019 (254,134) 50,151 (217,063) NET INCREASE IN CASH Cash at beginning of year Cash at end of year ? OMR 59,823 89,854 ? it is required to find the value in this cell Any missing/incorrect data should be reasonably; able 4: Detailed Transactions during 2018 Amount OMR Detailed Description of Transaction AAA invested cash in the business. Paid for office rent for the month Withdrew cash for personal use Received a cash payment for services provided Purchased gasoline on account Made a cash payment on the note payable Paid for utilities Paid for employee salaries Collected of accounts receivable 362,662 13,746 59,384 127,881 3,955 128,048 32,552 218,413 132,682 CW DATA