Bus299: Excel

To be answered using appropriate Excel functions and/or formulas.

There is no partial credit for incorrect usage.

Use appropriate formatting of cells where needed. Save frequently.

Keep saving your work on your USB drive beside the desktop folder, every 5 minutes.

You are provided a store and regional database providing sales data for computers and peripherals showing sales ID, store number, sales regions, item number, description, etc. See the Excel file for details.

1. In the worksheet labeled "Revenue by Region" answer the following questions:

a) Use SUMIF and external reference to summarize sales for each region

b) Format the sales as currency

c) Develop a pie chart that compares sales percentages (data labels) by each region and use data explosion for South.

For Reference:

Thanks for the help!!

AutoSave OFF buca BUS299-01_E1_S22_RED 890 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Q Comments Calibri (Body) v LG 11 ~ = = = Wrap Text General Ev 28 Or FO IN Analyze Data Paste B B I Insert av A. Av Merge & Center Delete $ %) V Format V .00 0 .00 Conditional Format Cell Formatting as Table Styles Sort & Filter Find & Select F4 . X fx 09 Pie Chart Q9 B D E F G H I J K L M 1 Total Revenue 99 Pie Chart 2 3 Sales 4 Region 5 North 6 South 7 West 8 East 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 PC Store Orientation Revenue by + Ready 3 166% AutoSave OFF ACO Pineda_Abril del Cielo_E1_red Q go Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 LG . PIL! = = = Custom a Wrap Text ,M,,, 28- O Paste a. A ~ BI == Insert E Merge & Center v Cell Delete .00 $ % ) V V Format V IN Analyze Data Conditional Format Formatting as Table Styles .00 Sort & Filter Find & Select F2 X fx F G . K L M N O P RED Q1 A B D E 1 Store and Regional Database 2 Average Unit Price 3 How many sales transactions were conducted in East sales region= 4 5 6 Q1 Discount 8.50% Q2 & 04 Q3 Q5 Q6 08 Bonus Q1 Bonus Q2 Sales ID Sales Region Store No. Q7 Combine Item No and description Item No. Item Description Units Sold Unit Price Date of Order Total Revenue Discounted Expected Unit Price Shipping Date Item Type New Unit Price Month of Order Eligible? 7 8 9 1 1 1 South 2005 1 1 1 2 3 3 4 5 6 7 8 1 1 1 28 30 9 30 35 39 28 3 38 25 27 33 1 1 1 9 10 11 12 1 South South South South South South South South South South South South South South South South South South South South 2005 2005 3006 3006 3006 6050 6050 6050 8500 8500 8500 2005 2005 2005 3006 3006 3006 6050 6050 6050 1 2 13 8 8 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 14 15 24inch Monitor 24inch Monitor 24inch Monitor Wireless Keyboard Wireless Keyboard Wireless Keyboard PC Mouse PC Mouse PC Mouse 17 inch Laptop 17inch Laptop 17inch Laptop 24inch Monitor 24inch Monitor 24inch Monitor Wireless Keyboard Wireless Keyboard Wireless Keyboard PC Mouse PC Mouse PC Mouse 17 inch Laptop 17 inch Laptop 17 inch Laptop 24inch Monitor 24inch Monitor 24inch Monitor Wireless Keyboard Wireless Keyboard $229.00 $229.00 $229.00 $19.95 $19.95 $19.95 $8.95 $8.95 $8.95 $849.95 $849.95 $849.95 $229.00 $229.00 $229.00 $19.95 $19.95 $19.95 $8.95 $8.95 $8.95 $849.95 $849.95 $849.95 $229.00 $229.00 $229.00 $19.95 $19.95 10 12/3/14 12/1/14 12/21/14 12/3/14 12/1/14 12/9/14 12/1/14 12/24/14 12/2/14 12/5/14 12/5/14 12/5/14 12/10/14 12/20/14 12/13/14 12/24/14 12/3/14 12/5/14 12/5/14 12/10/14 12/5/14 12/20/14 12/10/14 12/14/14 12/19/14 12/24/14 12/17/14 12/3/14 12/24/14 2 2 2 2 2 2 16 8 17 18 8 8 9 19 20 2 21 22 2 2 2 2 23 24 9 8 8 18 18 20 38 30 3 South South South South South South South South 25 26 27 28 29 8500 8500 8500 2005 2005 2005 3006 3006 3 3 3 3 3 30 32 3 PC Store Revenue by Region + Ready 3 11 AutoSave OFF ACO Pineda_Abril del Cielo_E1_red Q go Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 Co PIL! LG ~ = = = Custom ab Wrap Text 28 O O Paste .00 Insert av A Av BI Merge & Center Delete V $ % ) Format V V .00 Conditional Format Cell Formatting as Table Styles Sort & Filter Find & Select Analyze Data F2 - fx A B B C D E F F G H L M N O P 12/3/14 35 36 37 3 3 3006 3006 30 32 12/24/14 3 28 29 30 31 32 33 38 12/14/14 12/13/14 12/4/14 12/9/14 39 40 41 42 34 35 36 3 3 3 3 3 3 4 33 25 5 26 28 27 29 18 20 4 12 43 44 37 38 45 46 39 4 4 4 4 South South South South South South South South South North North North North North North North North North North North North North North North North 4 47 48 49 50 24 40 41 42 43 4 4 36 29 11 38 44 45 4 4 4 4 46 Wireless Keyboard Wireless Keyboard Wireless Keyboard PC Mouse PC Mouse PC Mouse 17inch Laptop 17inch Laptop 17inch Laptop 24inch Monitor 24inch Monitor 24inch Monitor Wireless Keyboard Wireless Keyboard Wireless Keyboard PC Mouse PC Mouse PC Mouse 17inch Laptop 17inch Laptop 17inch Laptop 24inch Monitor 24inch Monitor 24inch Monitor Wireless Keyboard Wireless Keyboard Wireless Keyboard PC Mouse PC Mouse PC Mouse 17inch Laptop 17 inch Laptop 17inch Laptop 24inch Monitor 24inch Monitor 24inch Monitor Wireless Keyboard 3006 6050 6050 6050 8500 8500 8500 2005 2005 2005 3006 3006 3006 6050 6050 6050 8500 8500 8500 2005 2005 2005 3006 3006 3006 6050 6050 6050 8500 8500 8500 2005 2005 2005 3006 47 48 49 $19.95 $19.95 $19.95 $8.95 $8.95 $8.95 $849.95 $849.95 $849.95 $229.00 $229.00 $229.00 $19.95 $19.95 $19.95 $8.95 $8.95 $8.95 $849.95 $849.95 $849.95 $229.00 $229.00 $229.00 $19.95 $19.95 $19.95 $8.95 $8.95 $8.95 $849.95 $849.95 $849.95 $229.00 $229.00 $229.00 $19.95 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 4 5 5 5 12/7/14 12/23/14 12/13/14 12/3/14 12/12/14 12/4/14 12/27/14 12/7/14 12/8/14 12/15/14 12/30/14 12/15/14 12/31/14 12/26/14 12/7/14 12/7/14 12/14/14 12/7/14 12/23/14 12/14/14 12/16/14 12/21/14 12/28/14 12/20/14 12/6/14 12/31/14 12/17/14 12/16/14 12/5/14 12/5/14 12/10/14 50 51 5 52 5 21 24 30 27 25 23 80 82 75 65 24 55 55 53 5. 5. 54 55 56 57 5 5 5 5 North North North North North North 58 66 59 5 57 67 5 47 68 6 60 61 62 63 North North East East East East 69 6 24 85 56 52 6 70 71 64 6 PC Store Revenue by Region + Ready 3 a 110% AutoSave OFF ACO Pineda_Abril del Cielo_E1_red Q go Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments v Co Calibri (Body) LG 11 ~ = = = a Wrap Text Custom A M 28- O Paste av A Av BIU E Insert Cell Merge & Center Delete .00 $ % ) V Format V V IN Analyze Data Conditional Format Formatting as Table Styles .00 Sort & Filter Find & Select F2 - fx B B D E F G H L M N O P 64 65 6 6 East East East 66 6 52 58 69 35 39 44 67 6 68 6 East East East 69 6 6 6 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 70 71 72 73 74 75 76 6 7 7 3006 3006 3006 6050 6050 6050 8500 8500 8500 2005 2005 2005 3006 3006 3006 6050 East East East East East East 78 88 99 34 36 35 49 7 7 77 47 78 7 7 7 7 79 80 East East East East East East East East Wireless Keyboard Wireless Keyboard Wireless Keyboard PC Mouse PC Mouse PC Mouse 17inch Laptop 17inch Laptop 17inch Laptop 24inch Monitor 24inch Monitor 24inch Monitor Wireless Keyboard Wireless Keyboard Wireless Keyboard PC Mouse PC Mouse PC Mouse 17inch Laptop 17inch Laptop 17inch Laptop 24inch Monitor 24inch Monitor 24inch Monitor Wireless Keyboard Wireless Keyboard Wireless Keyboard PC Mouse PC Mouse PC Mouse 17inch Laptop 17inch Laptop 17inch Laptop $19.95 $19.95 $19.95 $8.95 $8.95 $8.95 $849.95 $849.95 $849.95 $229.00 $229.00 $229.00 $19.95 $19.95 $19.95 $8.95 $8.95 $8.95 $849.95 $849.95 $849.95 $229.00 $229.00 $229,00 $19.95 $19.95 $19.95 $8.95 $8.95 $8.95 S849,95 $849.95 $849.95 12/10/14 12/5/14 12/20/14 12/10/14 12/14/14 12/19/14 12/24/14 12/17/14 12/3/14 12/24/14 12/14/14 12/13/14 12/4/14 12/9/14 12/27/14 12/23/14 12/13/14 12/3/14 12/12/14 12/4/14 12/25/14 12/5/14 12/10/14 12/5/14 12/20/14 12/10/14 12/14/14 12/19/14 12/24/14 12/17/14 12/3/14 12/24/14 12/14/14 48 45 42 81 7 82 7 7 45 55 57 55 18 17 83 84 85 86 7 8 East East East East 8 6050 6050 8500 8500 8500 2005 2005 2005 3006 3006 3006 6050 6050 6050 8500 87 8 8 23 22 88 8 89 8 90 8 East East East East East 91 92 93 8 8 8 8 94 95 96 97 98 99 100 101 102 103 104 105 106 107 18 22 14 16 17 32 8 East 94 8 8 8 95 96 East East East 8500 8500 28 30 8 PC Store Revenue by Region + Ready 3 110%