buestion 2 (15 marks) (write Excel formula only) Convert the following problems from their English expressions to Excel formulas that allow Excel to calculate the answers. Refer to the worksheet 'Sales April 2006 John Tam is the boss of New Best. an AV shop in Mongkok. Five different products are sold in NewBest: MP3 Player, MP4 Player, Digital Camera, Camcorder and LCD TV. John wants to see the sales, net profit, gross margin and operating expense totals for each product line in April 2006. The net sales figures for the products are given in cells C2 to G2 Write an Excel formula in cell B2 to calculate the total net sales for all products. The cost of goods sold figures for the products are given in cells C3 to G3. Write an 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 formulas in cells C4 to G4 to calculate the gross margin for each of the five products. Write an Excel formula in cell B4 to calculate the total gross margin for all five products. Each product of NewBest has the following operating expenses: Salaries & Commissions, Electricity, Media, Roll Show, Shop rental and Delivery. The amount for each of these operating expenses on each of the five products are given in cells C7 to G12 Write Excel formulas in cells B7 to B12 to calculate each operating expense total for all five products. Write Excel formulas in cells C13 to G13 to calculate the total expenses for cach 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 formulas to calculate the net profit in cells B14 to G14. Given that net profit as % of sales = net profit/ net sale 100% Write Excel formulas to calculate the net profit as % of sales in cells B15 to GIS. (Assume that you cannot use Excel cell formatting function, you need to multiply 100.) Worksheet: 'Sales April 2006 A B C D E F G 22 MP3 Player 56321 30256 22 MP4 Player 69857 46987 ?? Digital Camera Camcorder 53690 36984 29856 21333 ?? ?? LCD TV 196587 106988 ?? April 2006 Total 2 Net Sales 3 Cost of goods sold ?? 4 Gross margin ?? 5 6 Operating Expenses 7 Salaries & Commissions ?? 8 Electricity ?? 9 Media 10 Roll Show 11 Shop rental 12 Delivery 13 Total Expenses 14 Net Profit 15 net Profit as % of sales ?? 5980 1100 1500 1000 2000 5000 500 2000 1500 2000 8000 200 3000 2500 2000 7 987 250 3000 1500 5000 9050 1000 5500 3500 10500 8000 lo 22 ?? ?? ?? ?? ?? ?? ?? Based on the worksheet Sales April 2006" and the Table 1 below, John wants to forecast the net profits of MP4 Player and LCD TV in May 2006. Please complete the worksheet "Forecast May 2006" with Excel formulas assuming that the "Cost of goods sold" and all the operating expenses items are unchanged, and only the "Net Sales" will increase - see table 1 below: Table 1: Percentage%) increase in Net Sales over the previous month Products MP4 Player LCD TV 8 9 Worksheet: 'Forecast May 2006 B MP4 Player I c LCD TV ?? ?? ?? May 2006 2 Net Sales 3 Cost of goods sold 4 Gross margin 5 6 Operating Expenses ?? ?? Salaries & 7 Commissions Electricity 9 Media 10 Roll Show 11 Shop rental 12 Delivery 13 Total Expenses 14 Net Profit ?? ?? ?? ?? In 'Sales' worksheet, how many formula you wrote or typed ? How many formula you copied and pasted from other formula? In 'Forecast May 2006' worksheet, how many formula you wrote or typed? How many formula you copied and pasted from other formula? 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