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 NewBest, 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 5 products are given in cells C2 to G2. Write an Excel formula in cell B2 to calculate the total net sales for all 5 products. The cost of goods sold figures for the 5 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 cach operating expense total for all five products. Write Excel formulas 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 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 BI5 to GIs. (Assume that you cannot use Excel cell formatting function, you need to multiply 100.) Worksheet: "Sales April 2006 A B E MP3 Player 56321 30256 ?? MP4 Player 69857 46987 ?? Digital Camera Camcorder 53690 29856 ?? April 2006 Total ?? ?? ?? 1 LCD TV 2 Net Sales 3 Cost of goods sold 4 Gross margin 36984 21333 ?? 196587 106988 ?? 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 100 5000 500 2000 1500 2000 8000 200 3000 2500 2000 7987 250 3000 1500 5000 9050 ?? ?? ?? ?? ?? ?? ?? ?? 1000 1500 5500 1000 3500 2000 10500 8000 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? 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 Worksheet: 'Forecast May 2006' 3332 A B May 2006 2 Net Sales 3 Cost of goods sold 4 Gross margin MP4 Player LCD TV ?? ?? ?? ?? ?? ?? 5 6 Operating Expenses Salaries & 7 Commissions 8 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.