The company financial management office is preparing the business plan for the year ending 30 September...

 

Transcribed Image Text:

The company financial management office is preparing the business plan for the year ending 30 September 2022 and, to this end, the following expected quarterly financial data has been gathered from the business functional managers: Inventory Accounts receivable Bank (Overdraft) Accounts payable Long-term loans Sales Purchases Other operating expenses Fixed assets Other data: At 30 September 2021: . . . Q1 31" Dec 21 E 60,000 33.900 19,300 72,000 6,000 6,100 (5,000) 2,800 21,000 Q2 Q3 Q4 31 Mar 22 30th Jun 22 30 Sep 22 E E E 107,000 38,000 44,100 80,000 35,000 28,900 72,500 6,200 7,000 (4,100) 3,000 22,000 80,000 5,500 7,000 1,300 3,000 25,000 71,800 33,000 24,200 77,500 5,200 7,500 1.900 5,000 22.000 The inventory valuation is £4,500 The Miss Kiara capital account balance is £62,000 It is expected that, for the year to 30 September 2022, Miss Klara will withdraw £3,000 per month from the business for personal use (e. drawings) FP&SA Assignment 2021 (Individual) - Page 4 Assignment requirements: a. Explain the importance of financial technologies (FinTechs) in financial planning. (This should be done ciocument with no more using "Word' 200 words). 20% than b. Open a new Excel spreadsheet and prepare a worksheet named 'Data' that displays the above given data and calculates the following for each quarter and for the whole year: 1- Cost of goods sold. 2- Gross profit. 3- Gross profit margin. 4 Net operating profit. 5. Net operating profit margin. 6- Contribution to sales ratio per unit. 7- Opening balance (b/d) and closing balance (c/d) of capital account. 20% c. Relying on the data in 'Data Worksheet' and the use of named data range and look up table approach, create a new worksheet named 'Report that will report the quarterly budgeted financial statements in the way that the budgeted financial statements of a certain quarter will be displayed by the worksheet when this quarter is selected (requested) by the user. Note: Budgeted financial statements are the income statement and statement of financial position. 25% d. (i) Define and explain the importance of verification in financial planning and (ii) prepare verification workings for: Gross and net profits . • Gross and net profit margins Total current assets, current liabilities and net current assets The quarterly movement in the Selma Cooke capital account Note: . . . In the first part of 'd' requirement, you are required to write about the importance of information verification in financial planning and this should be done using 'Word' document with no more than 100 words. In the second part of 'd' requirement, you are required to show the use of formulae (in your prepared spreadsheet) that detect and avoid errors in the calculations of the specified financial outcomes. 15% e. Create a worksheet named 'Results and create appropriate charts that will show quarterly sales, gross profits and net profits, dependent upon the financial report selected 10% f. Create a worksheet named 'Bank' and create an appropriate chart that will show quarterly closing bank balances (for each quarter) 10% Assignment Total = 100% Assignment submission guidance: Written work should be prepared on a "Word' document and submitted via Turnitin. You can prepare graph, chart etc. in excel, then copy & paste in word document. FP&SA Assignment 2021 (Individual) - Page 5 The company financial management office is preparing the business plan for the year ending 30 September 2022 and, to this end, the following expected quarterly financial data has been gathered from the business functional managers: Inventory Accounts receivable Bank (Overdraft) Accounts payable Long-term loans Sales Purchases Other operating expenses Fixed assets Other data: At 30 September 2021: . . . Q1 31" Dec 21 E 60,000 33.900 19,300 72,000 6,000 6,100 (5,000) 2,800 21,000 Q2 Q3 Q4 31 Mar 22 30th Jun 22 30 Sep 22 E E E 107,000 38,000 44,100 80,000 35,000 28,900 72,500 6,200 7,000 (4,100) 3,000 22,000 80,000 5,500 7,000 1,300 3,000 25,000 71,800 33,000 24,200 77,500 5,200 7,500 1.900 5,000 22.000 The inventory valuation is £4,500 The Miss Kiara capital account balance is £62,000 It is expected that, for the year to 30 September 2022, Miss Klara will withdraw £3,000 per month from the business for personal use (e. drawings) FP&SA Assignment 2021 (Individual) - Page 4 Assignment requirements: a. Explain the importance of financial technologies (FinTechs) in financial planning. (This should be done ciocument with no more using "Word' 200 words). 20% than b. Open a new Excel spreadsheet and prepare a worksheet named 'Data' that displays the above given data and calculates the following for each quarter and for the whole year: 1- Cost of goods sold. 2- Gross profit. 3- Gross profit margin. 4 Net operating profit. 5. Net operating profit margin. 6- Contribution to sales ratio per unit. 7- Opening balance (b/d) and closing balance (c/d) of capital account. 20% c. Relying on the data in 'Data Worksheet' and the use of named data range and look up table approach, create a new worksheet named 'Report that will report the quarterly budgeted financial statements in the way that the budgeted financial statements of a certain quarter will be displayed by the worksheet when this quarter is selected (requested) by the user. Note: Budgeted financial statements are the income statement and statement of financial position. 25% d. (i) Define and explain the importance of verification in financial planning and (ii) prepare verification workings for: Gross and net profits . • Gross and net profit margins Total current assets, current liabilities and net current assets The quarterly movement in the Selma Cooke capital account Note: . . . In the first part of 'd' requirement, you are required to write about the importance of information verification in financial planning and this should be done using 'Word' document with no more than 100 words. In the second part of 'd' requirement, you are required to show the use of formulae (in your prepared spreadsheet) that detect and avoid errors in the calculations of the specified financial outcomes. 15% e. Create a worksheet named 'Results and create appropriate charts that will show quarterly sales, gross profits and net profits, dependent upon the financial report selected 10% f. Create a worksheet named 'Bank' and create an appropriate chart that will show quarterly closing bank balances (for each quarter) 10% Assignment Total = 100% Assignment submission guidance: Written work should be prepared on a "Word' document and submitted via Turnitin. You can prepare graph, chart etc. in excel, then copy & paste in word document. FP&SA Assignment 2021 (Individual) - Page 5

Answer rating: 100% (QA)

Sales revenue Sales priceQuantity so... View the full answer