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)?

Worksheet Sales

Important notes:

For those cells in worksheets Sales that express % figures, multiply the formula by 100. Dont use Excels Format Cell function - Percentage Number.

A B E F Total ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) Paper Supplies 14800 7500 ??(0.125 mark) Desk Accessories 12500 6500 ??(0.125 mark) Printer Supplies 23500 5500 ??(0.125 mark) Computer Writing Supplies Supplies 7500 10800 3875 2700 ??(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) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) 2960 905 200 65 18 0 ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) 2500 0 1460 32 6.5 0 ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) 4700 1265 1058 57 55 3350 ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) 1500 2160 250 875 0 1050 65 85 33 49 0 750 ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) ??(0.125 mark) Clipboard Font Ahgnment Number Styles B2 Xf ?? B C Paper Supplies 14800 7500 22 Desk Accessories 12500 6500 22 E Printer Supplies 23500 5500 72 Computer Supplies 7500 3875 ?? Writing Supplies 10800 2700 32 Total 2 Net Sales 22 3 Cost of goods sold 22 4 Gross margin 122 5 6 Operating Expenses 7 Salaries & Commissions 7? 8 Travel & Entertainment ?? 9 Media 22 10 Supplies 22 11 Property Taxes 72 12 Freight out 22 13 Total Expenses 22 14 Net Profit ?? 15 net Profit as % of sales ?? 16 17 2960 905 200 65 18 0 22 22 122 2500 0 1460 32 6.5 0 22 22 2? 4700 1265 1058 57 55 3350 22 22 22 1500 250 10 65 33 0 22 ?? 22 2160 875 1050 85 49 750 22 22 12? 18 19 21 How many formula you typed? How many formula you copied and pasted from other formula? 22