Question 2 (20 marks) (write Excel formula only)

Please refer to worksheet Sept. 2022 Sales. Convert the following problems from their English expressions to Excel formula that allow Excel to calculate the answers.

Bradley Imports sells six different products: paper supplies, desk accessories, desk accessories, printer supplies, computer supplies, writing supplies, and printing ink. It needs to see the sales, net profit, gross margin and operating expense totals for each product line in worksheet Sept. 2022 Sales.

The net sales figure for the 6 products are given in cells C2 to H2. Write Excel formula in cell B2 to calculate the total net sales for all 6 products.

The cost of goods sold figure for the 6 products are given in cells C3 to H3. Write Excel formula in cell B3 to calculate the total cost of goods sold for all 6 products.

Given that Gross Margin = Net Sales Cost of goods sold. Write Excel formula in cells C4 to H4 to calculate the gross margin for each of the six products.

Write an Excel formulae in cell B4 to calculate the total gross margin for all six products.

Bradley Imports has the following operating expense for each of the six products: salaries & commissions, travel & entertainment, media, supplies, advertisement, property taxes, freight out. The $ amount for each of these operating expenses on each of the six products are given in cells C7 to H13.

Write Excel formula in cells B7 to B13 to calculate each operating expense total for all six products.

Write Excel formula in cells C14 to H14 to calculate the total expenses for each product. Also write the Excel formula in cell B14 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 B15 to H15.

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 B16 to H16.

Worksheet Sept. 2022 Sales

Worksheet Forecast October 2022

Based on the worksheet Sept. 2022 Sales and the Table 1 below, Bradley wants to

forecast the net profits of Printer Supplies and Printing Ink in October 2022. Please

complete the worksheet Forecast October 2022 with Excel formulas assuming

that all the operating expenses items are unchanged, and only the Net Sales will

increase, the Cost of goods sold will decrease see table 1 below:

Table 1:

Products	Percentage(%) increase in Net Sales over the previous month	Percentage(%) decrease in Cost of goods sold over the previous month
Printer Supplies	8		1
Printing Ink	9		2

Important notes:

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

In Sept. 2022 Sales worksheet, how many formula you typed? How many formula you copied and pasted from other formula?

[5 marks]

In Forecast October 2022 worksheet, how many formula you typed? How many formula you copied and pasted from other formula?

[6.5 marks]