Part 2: (20 marks) (write Excel formula only) Please refer to worksheet 'Jan. 2023 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: oil supplies, sauce accessories, oven supplies, toaster supplies, writing supplies, and roasting ink. It needs to see the sales, net profit, gross margin and operating expense totals for each product line in worksheet 'Jan. 2023 Sales'. 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 C3 to H9. - Write Excel formula in cells B3 to B9 to calculate each operating expense total for all six products. - Write Excel formula in cells C10 to H10 to calculate the total expenses for each product. Also write the Excel formula in cell B10 for the total operating expenses for all products. - The net sales figure for the 6 products are given in cells C12 to H12. Write Excel formula in cell B12 to calculate the total net sales for all 6 products. - The cost of goods sold figure for the 6 products are given in cells C13 to H13. Write Excel formula in cell B13 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 C14 to H14 to calculate the gross margin for each of the six products. - Write an Excel formulae in cell B14 to calculate the total gross margin for all six products. - Given that net profit = gross margin - total expense. Write Excel formula to calculate the net profit in cells B16 to H16. - 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 B17 to H17. Forecast Feb. 2023 \begin{tabular}{|c|l|l|l|} \hline & \multicolumn{1}{|c|}{ A } & B & C \\ \hline 1 & Forecast Feb 2023 & Toaster Supplies & Roasting Ink \\ \hline 2 & Operating Expenses & & \\ \hline 3 & Salaries \& Commissions & & \\ \hline 4 & Travel \& Entertainment & & \\ \hline 5 & Media & & \\ \hline 6 & Supplies & & \\ \hline 7 & Advertisement & & \\ \hline 8 & Property Taxes & & \\ \hline 9 & Freight out & & \\ \hline 10 & Total Expenses & & \\ \hline 11 & & & \\ \hline 12 & Net Sales & & \\ \hline 13 & Cost of goods sold & & \\ \hline 14 & Gross margin & & \\ \hline 15 & & & \\ \hline 16 & Net Profit & & \\ \hline 17 & & & \\ \hline 18 & & & \\ \hline 19 & & & \\ \hline 20 & & & \\ \hline \end{tabular} Based on the worksheet "Jan. 2023 Sales" and the Table 1 below, Bradley wants to forecast the net profits of Toaster Supplies and Roasting Ink in Feb. 2023. Please complete the worksheet "Forecast Feb. 2023" 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: Important notes: - For those cells in worksheets 'Jan. 2023 Sales' that express \% figures, multiply the formula by 100. Don't use Excel's Format Cell function - Percentage Number. In 'Jan. 2023 Sales' worksheet, how many formula you typed? How many formula you copied and pasted from other formula? [5 marks] In 'Forecast Feb. 2023' worksheet, how many formula you typed? How many formula you copied and pasted from other formula? [6.5 marks]
