Convert the following problems from their English expressions to Excel formulas that allow Excel to calculate...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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' 333°2 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. 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' 333°2 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.
Expert Answer:
Answer rating: 100% (QA)
PART A SUMC2G2 SUMC3G3 Gross Margin Formula tabulated below C2C3 D2D3 E2E3 F2F3 G2G3 Note You can co... View the full answer
Related Book For
Organic Chemistry
ISBN: 978-1118133576
11th edition
Authors: Graham Solomons, Craig Fryhle, Scott Snyder
Posted Date:
Students also viewed these organizational behavior questions
-
Calculate UTX's gross, operating and net profit margin for each year 2010-2012.
-
Write structural formulas for all the products that would be obtained when each of the following alkyl halides is heated with sodium ethoxide in ethanol. When more than one product results, you...
-
Write structural formulas for all the products that would be obtained when each of the following alkyl halides is heated with potassium tert-butoxide in tert-butyl alcohol. When more than one product...
-
A double-ended queue or deque (pronounced "deck") is a collection that is a combination of a stack and a queue. Write a class Deque that uses a linked list to implement the following API: public...
-
Submersibles with clear plastic hulls have the potential to revolutionize underwater leisure. One small submersible vehicle has a depth-control system as illustrated in Figure E4.9. (a) Determine the...
-
The graph of g(x) = cos x is shown below. Explain whether the points lie on the graph of y = arccos x. 1 2 (2). (a.) (0.7). 3 and (-1-3)
-
Derive the equation of motion for free transverse vibration of a simply supported, specially orthotropic plate that is subjected to in-plane loads per unit length \(N_{x}\) and \(N_{y}\) as shown in...
-
An object of irregular shape has a characteristic length of L = 1 m and is maintained at a uniform surface temperature of Ts = 325 K. It is suspended in an airstream that is at atmospheric pressure...
-
4. How are organisation health and safety procedures, health and safety signage and safe work practices going to be monitored at ANIBT Hotel? Include how you will monitor ongoing compliance. 5. What...
-
Selected information from the Iowa Instruments accounting records for April follows: Additional information for April follows: The labor wage rate was $30 per hour. During the month, sales revenue...
-
a) First, define the competitive equilibrium for the real- intertemporal model ( b) Suppose there are two countries identical in every way except that Country A has more capital than Country B. What...
-
Determine the IRR on the following projects: a. An initial outlay of \($12,000\) resulting in a free cash flow of \($2,041\) at the end of each year for the next 11 years b. An initial outlay of...
-
Determine the IRR on the following projects: a. An initial outlay of \($12,000\) resulting in a single free cash flow of \($17,231\) after 7 years b. An initial outlay of \($12,000\) resulting in a...
-
Holland at Home is considering introducing a variation of its current breakfast cereal, Zonnatura Regular Muesli Rich. The new cereal will be similar to the old with the exception that it will...
-
An astronaut who recently landed on an unfamiliar planet wants to measure the acceleration due to gravity. He constructs a simple pendulum of length \(40.0 \mathrm{~cm}\) and then finds that the...
-
TSMC Corporation is considering selling one of its old wafer fabrication machines. The machine, purchased for \($3,000,000\) 5 years ago, had an expected life of 10 years and an expected salvage...
-
Complete the missing amounts and labels in the T-accounts. i (Click the icon to view the T-accounts.) Begin with the Work-in-Process-Cutting T-account, then complete each of the remaining T-accounts....
-
Will the prediction interval always be wider than the estimation interval for the same value of the independent variable? Briefly explain.
-
Make a reaction flowchart (roadmap diagram), as in previous problems, to organize the information provided to solve this problem. An optically active compound A (assume that it is dextrorotatory) has...
-
Write a mechanism for the following reaction. cat. H.SO OMe
-
The 1 H NMR spectrum of cyclooctatetraene consists of a single line located at 5.78. What does the location of this signal suggest about electron delocalization in cyclooctatetraene?
-
Describe what sexual harassment is and what forms it can take.
-
Explain public policies that protect employees from unlawful discharge.
-
Discuss under what conditions unemployment compensation can be denied to a claimant.
Study smarter with the SolutionInn App