2. Excel Formula (7 marks): Please refer to worksheet (Sales'. Convert the following problems from their English expressions to Excel formula that allow Excel to calculate the answers. Bradley Imports sells five different products: paper supplies, desk accessories, desk accessories, printer supplies, computer supplies, writing supplies. It needs to see the sales, net profit, gross margin and operating expense totals for each product line. The net sales figure for the 5 products are given in cells C2 to G2. Write Excel formula in cell B2 to calculate the total net sales for all 5 products. . The cost of goods sold figure for the 5 products are given in cells C3 to G3. Write Excel formula in cell B3 to calculate the total cost of goods sold for all 5 products. Given that Gross Margin Net Sales - Cost of goods sold. Write Excel formula in cells C4 to G4 to calculate the gross margin for each of the five products. . Write an Excel formulae in cell B4 to calculate the total gross margin for all five products. Bradley Imports has the following operating expense for each of the five products: salaries & commissions, travel & entertainment, media, supplies, property taxes, freight out. The $ amount for each of these operating expenses on each of the five products are given in cells C7 to G12. Write Excel formula in cells B7 to B12 to calculate each operating expense total for all five products. .Write Excel formula in cells C13 to G13 to calculate the total expenses for each product. Also write the Excel formula in cell B13 for the total operating expenses for all products. . Given that net profit = gross margin total expense. Write Excel formula to calculate the net profit in cells B14 to G14. Given that net profit as % of sales net profit / net sale * 100%. Write Excel formula to calculate the net profit as % of sales in cells B15 to G15. In Sales' worksheet, how many formula you typed (1.5 marks)? How many formula you copied and pasted from other formula (1.5 marks)? 526 fx A B D E F G Printer Net Sales Cost of goods sold Gross margin Total ?? ?? ?? Paper Supplies 14800 7500 22 Desk Accessories 12500 6500 22 Supplies 23500 5500 22 Computer Supplies 7500 3875 ?? Writing Supplies 10800 2700 ?? WY WA WIM ZA Operating Expenses Salaries & Commissions ?? Travel & Entertainment ?? Media ?? Supplies ?? Property Taxes ?? Freight out ?? Total Expenses ?? Net Profit ?? net Profit as % of sales ?? 2960 905 200 65 18 0 22 ?? ?? 2500 0 1460 32 6.5 10 ?? 2? 122 4700 1265 1058 57 55 3350 ?? 2? 22 1500 250 O. 65 33 0 ?? ?? ?? 2160 875 1050 85 49 750 ?? ?? STES 1?? 1 2 How many formula you typed? How many formula you copied and pasted from other formula? 4 25 26 27 28 29 30 31 32 Worksheet 'Sales' B D E F G Total 7?(0.125 mark) ??(0.125 mark) ??(0.125 mark) Paper Desk Printer Computer Writing Supplies Accessories Supplies Supplies Supplies 14800 12500 23500 7500 10800 7500 6500 5500 3875 2700 ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) 1 2 Net Sales 3 Cost of goods sold 4 Gross margin 5 6 Operating Expenses 7 Salaries & Commissions 8 Travel & Entertainment 9 Media 10 Supplies 11 Property Taxes 12 Freight out 13 Total Expenses 14 Net Profit 15 net Profit as % of sales 16 ??(0.125 mark) ??(0.125 mark) 77(0.125 mark) 77(0.125 mark) ??(0.125 mark) 77(0.125 mark) 77(0.125 mark) 77(0.125 mark) ??(0.125 mark) 2960 2500 4700 905 0 1265 200 1460 1058 65 32 57 18 6.5 55 0 0 3350 77(0.125 mark) 77(0.125 mark) 77(0.125 mark) 77(0.125 mark) 77(0.125 mark) 77(0.125 mark) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) 1500 2160 250 875 0 1050 65 85 33 49 0 750 77(0.125 mark) 77(0.125 mark) 77(0.125 mark) 77(0.125 mark) ??(0.125 mark) ??(0.125 mark) Important notes: . For those cells in worksheets 'Sales' that express % figures, multiply the formula by 100. Don't use Excel's Format Cell function - Percentage Number. CamScanner