Question

Problem 10-10 (Some Useful Excel Functions for Modeling)

Newton Manufacturing produces scientific calculators. The models are N350, N450, and the N900. Newton has planned its distribution of these products around eight customer zones: Brazil, China, France, Malaysia, U.S. Northeast, U.S. Southeast, U.S. Midwest, and U.S. West. Data for the current quarter (volume to be shipped in thousands of units) for each product and each customer zone are given in the file Newton. Newton would like to know the total number of units going to each customer zone and also the total units of each product shipped. There are several ways to get this information from the data set. One way is to use the SUMIF function.

Newton Scientific Calculators
Customer Zone	Model	Volume (000 units)
Malaysia	N350	399.0
China	N350	3158.3
France	N350	1406.0
Brazil	N350	163.5
US Northeast	N350	68.7
US Southeast	N350	999.7
US Midwest	N350	544.9
US West	N350	1804.0
Malaysia	N450	228.0
China	N450	540.9
France	N450	289.8
Brazil	N450	240.5
US Northeast	N450	313.2
US Southeast	N450	681.3
US Midwest	N450	1720.3
US West	N450	2922.3
Malaysia	N900	1301.6
China	N900	1629.3
France	N900	1790.8
Brazil	N900	740.7
US Northeast	N900	48.3
US Southeast	N900	1522.3
US Midwest	N900	2217.5
US West	N900	1243.6

The SUMIF function extends the SUM function by allowing the user to add the values of cells meeting a logical condition. The general form of the function is:

=SUMIF (test range, condition, range to be summed).

The test range is an area to search to test the condition, and the range to be summed is the position of the data to be summed. So, for example, using the Newton file, we would use the following function to get the total units sent to Malaysia:

=SUMIF (A3:A26, A3, C3:C26).

Here, cell A3 contains the text "Malaysia", A3:A26 is the range of customer zones, and C3:C26 are the volumes for each product for these customer zones. The SUMIF looks for matches of "Malaysia" in column A and, if a match is found, adds the volume to the total. Use the SUMIF function to get each total volume by zone and each total volume by product.

(a)	Complete the below table for total number of units distributed in each customer zone.
	If required, round your answers to one decimal place.
	Customer Zone Total Volume (000 units) Brazil China France Malaysia US Midwest US Northeast US Southeast US West     Total
(b)	Complete the below table for the total units of each product shipped.
	If required, round your answers to one decimal place.
	Product Total Volume (000 units) N350 N450 N900     Total

Answer 1