Q1 Q2 Assignment Data Kiara is a small UK organisation. The business is a sole trader organisation owned by Miss Kiara Gal and produces a small range of synthetic products used by larger home and office furniture suppliers. The company financial management office is preparing the business plan for the year ending 3.0 September 2022 and, to this end, the following expected quarterly financial data has been gathered from the business functional managers: Q3 04 31" Dec 21 31 Mar 22 30 Jun 22 30th Sep 22 E E E E Sales 60,000 80,000 107.000 71,800 Purchases 33,900 35,000 38,000 33,000 Other operating expenses 19,300 28,900 44,100 24 200 Fored assets 72,000 72,500 80,000 77,500 Inventory 6,000 6,200 3,500 5.200 Accounts receivable 6,100 7,000 7,000 7,500 Bank (Overdraft) (5,000) (4.100) 1,300 1,900 Accounts payable 2.800 3,000 3,000 5,000 Long-term loans 21,000 22,000 25,000 22,000 Other data: At 30 September 2021: 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 Kiara will withdraw 5,000 per month from the business for personal use (t.e. drawings) . 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- Opening balance (b/d) and dosing 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. Budgeted financial statements are the income statement and statement of financial position. 25% Note: QI Q2 Q 1 2 Default data: No action is required by you You need to enter the data manually You need to enter the data by developing and using 4. xe ng nh tay & Archioes 6 cost of goods sold 6 Goonprofits Maroon 10 ore 11 Netooerating profits 12 Maron 13 Hd 14 Inventory 15 Recables 16 Bank 17 Payables ta Bankoverdrant 19 Long term loans 20 Cataract 21 Nettront 22 Drawings 21 Cilaccounted 0 O 0 0 o 0 0 0 0 O 0 ETO dong bark wel o 0
