Coursework: This coursework consists of completing a Case Study based spreadsheet financial portfolio. The financial statements and transactions for a firm for the current and previous year will be given as a case study along with a self-directed learning handbook and the spreadsheet portfolio template. The task is to compute the required financial information and complete the spreadsheet portfolio. The case study used will reinforce the students' knowledge of accounting concepts learned throughout the module. The spreadsheet portfolio template will have sheets for the calculation of the financial information. Using the formulas available in the spreadsheet tool and the directions provided in the handbook, the various required financial information have to be computed in the spreadsheet. Primarily, this involves five tasks: QEAD-Template A Rev 04 vor 02 Date: 014 October 2018 Page 1 of 3 F/QAP/021/001 1. The Income Statement Preparation. 2. Bonus Calculation. 3. The Statement of Financial Position Preparation 4. Financial Charts presentation. 5. Financial Ratios calculations. The above tasks shall involve using the SUM function, IF function, VLOOKUP function, using charts available with the spreadsheet tools and ratio calculation by identifying the required information in the spreadsheets and using the financial ratio formulae studied in lectures and seminars. Quartet 1.500 lo C du 1.500 2 500 102.000 00 7.500 122.500 0.000 500 6500 7500 500 Momentancia Ace Account Asset No Current 1010 ung 1000 YE 514.000 1000 165,500 26000 10.000 100 0 472.000 115.500 42300 72.00 6. 7.00 100 0 59500 OM 15.200 10.000 Tech Ed 7.100 500 0 7500 Opg 000 19.500 001 no 12 21.00 1.500 10.000 www NUE cash at han Trades 101 41.100 11 2000 1000 2007 Vn 5 tialties Oriental Share 220,000 0.000 2000 Nonumentos 20 Currenties 000 2150 2015 2200 2011 Other Teat Ranked 5.000 Set 100 Prior Year Current Year Data Income Statement abomis payable Statement of Financial Position Charts Ratio analysis BIU 5 Merge & Co Format Painter Clipboard Font Alignment D24 f D E F Income Statement data Quarter 1 Quarter 2 Quarter 3 Quartet 200 000 0.60 0.35 5,000 500 1.000 9,300 200 7,500 7 Sales & Production units 180.000 190,000 190,000 8 Seling price per unit (OMR) 055 0.60 0.80 9 Cost per unit (OMR) 0.20 0.25 0.30 10 11 Omer expenses 12 Rent (OMR 5,000 5.000 5 000 13 Insurance (OMR) 500 500 500 14 Electricity (OMR) 1,000 1,000 1.000 15 Depreciation (OMR) 7,500 7,500 16 Loan interest (OMR) 200 17 18 19 20 21 22 23 BALANCE SHEET DATA 24 25 Accounttype Balance (OMR) 20 1010 Land & buildings 468,000 27 1020 Machinery 90 200 20 1033 Intangible assets 30.000 29 1050 Inventories 24000 30 1067 Cash at bank 31 1098 Trade receivables 79.800 32 2050 Ordinary share capital of OMR1 (pal 220,000 33 2000 Share premium account 50.000 34 2067 Retained profits 385,100 35 2099 Bank loan 12,000 36 2150 Trade payables 27,000 37 2215 Other payables 4,000 38 2260 Taxation 2,000 39 2079 Bank overdraft 1,000 40 41 42 43 44 Prior Year Current Year Data Income Statement &bonus payable Ready 9.100 Statement Type here to search Latement Q GUIDA YER Contes Cicero Matem wees SUN info the Parol Derlingo Ina How it JANU Estopad w d ELLEN Some 1 pedig met Depwoer Sched Dating Un Form AND Make sure you were per AND Make sure you use a need from the better in die Schen Borus pada pering pagelet Usono Mourning the Olunch Me sure you use are te forte sebe the French Calculate the end choreom For schemes4you call the hos, RAND/OR then gaforme Formando NO State whether to speable or nore the one HOT Prior Year Current Year Data Income Statement &bonus payable Statement of Financial Position Charts Ratio analysis Mount code 2 Balance (OMR Accounttype Assets Non cuarentants 1010 10.30 01011 1 11 1050 01007 TE Total assets Juse SUM function to calculate total assets 54 Populate lccounttypes and balance the statement of ancil Position in VLOOKUP formated red) Hint. The account and should be ined to look up the data in the table in the Yes Dattati Equity & Liabilities Equity 1 2000 000 192067 1 Non current liabilities Current liabilities 52150 2215 2260 2019 Total equity and liabilities Use SUM function to calculate total equity and liabilities Prior Year Current Year Data Income Statement & bonus payable Statement of Financial Position Charts Ratio analysis 1 Ratio Name Formula in words Calculation prior year Calculation - current year Comments 2 1 Operating profit margin Remember to use formulas, do not type in the answers! 32 Acid est ratio Return on capital employed Gearing ratio 65 Average trade receivables settlement period Coursework: This coursework consists of completing a Case Study based spreadsheet financial portfolio. The financial statements and transactions for a firm for the current and previous year will be given as a case study along with a self-directed learning handbook and the spreadsheet portfolio template. The task is to compute the required financial information and complete the spreadsheet portfolio. The case study used will reinforce the students' knowledge of accounting concepts learned throughout the module. The spreadsheet portfolio template will have sheets for the calculation of the financial information. Using the formulas available in the spreadsheet tool and the directions provided in the handbook, the various required financial information have to be computed in the spreadsheet. Primarily, this involves five tasks: QEAD-Template A Rev 04 vor 02 Date: 014 October 2018 Page 1 of 3 F/QAP/021/001 1. The Income Statement Preparation. 2. Bonus Calculation. 3. The Statement of Financial Position Preparation 4. Financial Charts presentation. 5. Financial Ratios calculations. The above tasks shall involve using the SUM function, IF function, VLOOKUP function, using charts available with the spreadsheet tools and ratio calculation by identifying the required information in the spreadsheets and using the financial ratio formulae studied in lectures and seminars. Quartet 1.500 lo C du 1.500 2 500 102.000 00 7.500 122.500 0.000 500 6500 7500 500 Momentancia Ace Account Asset No Current 1010 ung 1000 YE 514.000 1000 165,500 26000 10.000 100 0 472.000 115.500 42300 72.00 6. 7.00 100 0 59500 OM 15.200 10.000 Tech Ed 7.100 500 0 7500 Opg 000 19.500 001 no 12 21.00 1.500 10.000 www NUE cash at han Trades 101 41.100 11 2000 1000 2007 Vn 5 tialties Oriental Share 220,000 0.000 2000 Nonumentos 20 Currenties 000 2150 2015 2200 2011 Other Teat Ranked 5.000 Set 100 Prior Year Current Year Data Income Statement abomis payable Statement of Financial Position Charts Ratio analysis BIU 5 Merge & Co Format Painter Clipboard Font Alignment D24 f D E F Income Statement data Quarter 1 Quarter 2 Quarter 3 Quartet 200 000 0.60 0.35 5,000 500 1.000 9,300 200 7,500 7 Sales & Production units 180.000 190,000 190,000 8 Seling price per unit (OMR) 055 0.60 0.80 9 Cost per unit (OMR) 0.20 0.25 0.30 10 11 Omer expenses 12 Rent (OMR 5,000 5.000 5 000 13 Insurance (OMR) 500 500 500 14 Electricity (OMR) 1,000 1,000 1.000 15 Depreciation (OMR) 7,500 7,500 16 Loan interest (OMR) 200 17 18 19 20 21 22 23 BALANCE SHEET DATA 24 25 Accounttype Balance (OMR) 20 1010 Land & buildings 468,000 27 1020 Machinery 90 200 20 1033 Intangible assets 30.000 29 1050 Inventories 24000 30 1067 Cash at bank 31 1098 Trade receivables 79.800 32 2050 Ordinary share capital of OMR1 (pal 220,000 33 2000 Share premium account 50.000 34 2067 Retained profits 385,100 35 2099 Bank loan 12,000 36 2150 Trade payables 27,000 37 2215 Other payables 4,000 38 2260 Taxation 2,000 39 2079 Bank overdraft 1,000 40 41 42 43 44 Prior Year Current Year Data Income Statement &bonus payable Ready 9.100 Statement Type here to search Latement Q GUIDA YER Contes Cicero Matem wees SUN info the Parol Derlingo Ina How it JANU Estopad w d ELLEN Some 1 pedig met Depwoer Sched Dating Un Form AND Make sure you were per AND Make sure you use a need from the better in die Schen Borus pada pering pagelet Usono Mourning the Olunch Me sure you use are te forte sebe the French Calculate the end choreom For schemes4you call the hos, RAND/OR then gaforme Formando NO State whether to speable or nore the one HOT Prior Year Current Year Data Income Statement &bonus payable Statement of Financial Position Charts Ratio analysis Mount code 2 Balance (OMR Accounttype Assets Non cuarentants 1010 10.30 01011 1 11 1050 01007 TE Total assets Juse SUM function to calculate total assets 54 Populate lccounttypes and balance the statement of ancil Position in VLOOKUP formated red) Hint. The account and should be ined to look up the data in the table in the Yes Dattati Equity & Liabilities Equity 1 2000 000 192067 1 Non current liabilities Current liabilities 52150 2215 2260 2019 Total equity and liabilities Use SUM function to calculate total equity and liabilities Prior Year Current Year Data Income Statement & bonus payable Statement of Financial Position Charts Ratio analysis 1 Ratio Name Formula in words Calculation prior year Calculation - current year Comments 2 1 Operating profit margin Remember to use formulas, do not type in the answers! 32 Acid est ratio Return on capital employed Gearing ratio 65 Average trade receivables settlement period