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, and description, etc. See the Excel file for details.

PC Store:

1) Calculate the average sales price in cell F2 and how many sales transaction were conducted in East sales region in Cell F3. Use the original price as the base price.

2) Calculate the total revenue for each item in column labeled "Total Revenue" using the Unit Price and Units Sold for each item.

3) Store wants to give holiday season discount to its customers (using discount provided in cell I5). Use this discount to calculate the new "Discounted Unit Price". Remember, discounted price should be lower than the original price.

4) Add Conditional Formatting to highlight the total revenues that are between $12,000 and $30,000. Have dark red FILL with WHITE letters & Bold.

5) Using date functions, in Column "Expected Shipping Date", calculate the date of shipping item which is always set by store as 21 days from the date of order. So, for example, for Sales ID of 1, since order date is 12/3/2014, the correct expected shipping date should be 12/24/2014.

6) Using text functions, in column "Item Type", extract the second word from the Item Description. For example, for sales ID of 1, the item description is "24inch Monitor", so the correct answer for Item Type would be "Monitor". Use the original price as the base price.

7) Using text function, combine Item No and Item Description in column "Combine Item No and Description". For example, for sales ID of 1, it should be "2005 is a 24inch Monitor".

8) Company's VP of Marketing feels they can sell at a higher price based on location and item description. She uses the following rule to calculate New Unit Price using nested-IFs for each item in column N. Use the original price as the base price. (If the item is a 24inch Monitor or 17inch Laptop, new unit price would be 20% more than original unit price. For all other items, If they are in South region, new unit price would be 10% more than original prices, For all other regions, new unit price would remain the same as original unit price. Hint: Draw the decision tree diagram first to get your logic right.)

Since this database is about sales in the month of December, the company wants to give eligible orders a Christmas season discount.

BQ1) In the Column "Day of Order", calculate the day of the month when the order was placed. For examples, for Sales ID of 1, day of order would be 3.

BQ2) If the day of the order is between 20 and 28 (both inclusive), then it should say "Eligible" otherwise it should say "No X-mas discount".

AutoSave . OFF A ?. CAFE ... Pineda_Abril del Cielo_E1_red Comments Page Layout Formulas Data Tell me Share Home Insert Draw Review View O Calibri (Body) A B Wrap Text v Custom I V Delete Format Sort & Find & Conditional Format Cell Insert Analyze Filter Select Data Paste BIUV A Merge & Center v v % " Formatting as Table Styles F2 X V fx M N O B C D G H Store and Regional Database RED Average Unit Price= Q1 How many sales transactions were conducted in East sales region= 01 Discount = 8.50% 03 05 26 Q7 28 12 & Q4 Bonus Q1 Bonus Q2 Units Total Discounted Expected Combine Item No and Sales Item Type New Unit Price Month of Eligible? Unit Price Date of Order Order Sales ID Store No. Item No. Item Description Region Sold Revenue Unit Price Shipping Date description South 2005 24inch Monitor $229.00 12/3/14 28 South 2005 24inch Monitor $229.00 12/1/14 30 South 2005 24inch Monitor $229.00 12/21/14 9 South 3006 Wireless Keyboard $19.95 12/3/14 30 Wireless Keyboard $19.95 2/1/14 35 South 300 South 3006 Wireless Keyboard $19.95 12/9/14 39 28 South 6050 PC Mouse $8.95 12/1/14 South 6050 PC Mouse $8.95 12/24/14 3 South 6050 PC Mouse $8.95 12/2/14 38 South 8500 17inch Laptop $849.95 12/5/14 25 South 8500 17inch Laptop 849.95 12/5/14 27 17inch Laptop $849.95 12/5/14 33 South 8500 South 2005 24inch Monitor $229.00 2/10/14 South 2005 24inch Monitor $229.00 12/20/14 South 2005 24inch Monitor $229.00 12/13/14 10 South 3006 Wireless Keyboard $19.95 12/24/14 South 3006 Wireless Keyboard $19.95 12/3/14 South 3006 Wireless Keyboard $19.95 12/5/14 South 6050 PC Mouse $8.95 12/5/14 South 6050 PC Mouse $8.95 12/10/14 JO NNNNNNNNNN South 6050 PC Mouse $8.95 12/5/14 Co South 8500 17inch Laptop $849.95 12/20/14 18 South 8500 17inch Laptop $849.95 12/10/14 18 South 8500 17inch Laptop $849.95 12/14/14 20 $229.00 12/19/14 38 South 2005 24inch Monitor 26 South 2005 24inch Monitor $229.00 12/24/14 30 27 South 2005 24inch Monitor $229.00 2/17/14 3 28 30 South 3006 Wireless Keyboard $19.95 12/3/14 South 3006 Wireless Keyboard $19.95 12/24/14 PC Store Revenue by Region + 110 ReadyPineda_Abril del Cielo_E1_red AutoSave . OFF A ?. CAE ... Share Comments Home Insert Draw Page Layout Formulas Data Review View Tell me O Custom Calibri (Body) B Wrap Text v I V Find & Cell Insert Delete Format Sort & Analyze Filter BIU A v % " Conditional Format Paste Merge & Center v Select Data Formatting as Table Styles F2 X V fx M N O B D G H South 3006 Wireless Keyboard $19.95 12/3/14 30 South Wireless Keyboard $19.95 12/24/14 32 3006 South 3006 Wireless Keyboard $19.95 12/14/14 33 25 South 6050 PC Mous $8.95 12/13/14 South 6050 PC Mouse $8.95 12/4/14 5 6050 PC Mouse $8.95 12/9/14 26 South South $849.95 12/7/14 28 8500 17inch Laptop South 3500 17inch Laptop $849.95 12/23/14 27 South 8500 17inch Laptop $849.95 12/13/14 29 North 2005 24inch Monitor $229.00 12/3/14 18 North 2005 24inch Monitor $229.00 2/12/14 20 North 2005 24inch Monitor $229.00 12/4/14 3006 12 North Wireless Keyboard $19.95 12/27/14 North 3006 Wireless Keyboard $19.95 2/7/14 24 North 3006 Wireless Keyboard $19.95 12/8/14 36 North 6050 PC Mouse $8.95 12/15/14 29 $8.95 12/30/14 11 North 6050 PC Mouse North 6050 PC Mouse $8.95 12/15/14 38 21 North 8500 17inch Laptop $849.95 12/31/14 BEBEE BEE BAGGED REBBEWEEEEEEEE North 8500 17inch Laptop $849.95 12/26/14 24 North 8500 17inch Laptop $849.95 12/7/14 30 2005 24inch Monitor $229.00 12/7/14 27 North 24inch Monitor $229.00 12/14/14 25 North 2005 North 2005 24inch Monitor 229.00 12/7/14 23 North 3006 Wireless Keyboard $19.95 12/23/14 BO 3006 Wireless Keyboard $19.95 12/14/14 82 North 5 North 3006 Wireless Keyboard $19.95 12/16/14 75 North 6050 PC Mouse $8.95 12/21/14 65 6050 PC Mouse $8.95 12/28/14 24 North North 6050 PC Mouse $8.95 12/20/14 55 North 8500 17inch Laptop $849.95 12/6/14 55 North 8500 17inch Laptop $849.95 12/31/14 57 47 North 8500 17inch Laptop $849.95 12/17/14 24 East 2005 24inch Monitor $229.00 12/16/14 East 2005 24inch Monitor $229.00 12/5/14 85 East 2005 24inch Monitor $229.00 12/5/14 56 3006 Wireless Keyboard $19.95 52 East 12/10/14 PC Store Revenue by Region + + 110% ReadyPineda_Abril del Cielo_E1_red AutoSave . OFF A ?. CAE ... Share Comments Insert Review View Tell me Home Draw Page Layout Formulas Data O Calibri (Body) B Wrap Text v Custom I V Conditional Format Cell Insert Delete Format Sort & Find & Analyze Filter Select Data Paste BIUV A Merge & Center v $ ~ % " Formatting as Table Styles F2 X V fx M N O H A B D G 6 East 3006 Wireless Keyboard $19.95 12/10/14 52 64 12/5/14 58 65 Eas 3006 Wireless Keyboard $19.95 3006 Wireless Keyboard $19.95 12/20/14 69 66 East 67 East 6050 PC Mouse $8.95 12/10/14 35 39 68 6 East 6050 PC Mouse $8.95 12/14/14 44 6 East 6050 PC Mouse $8.95 12/19/14 $849.95 12/24/14 78 6 East 3500 17inch Laptop East 8500 17inch Laptop $849.95 12/17/14 88 East 8500 17inch Laptop $849.95 12/3/14 99 7 East 2005 24inch Monitor $229.00 12/24/14 34 East 2005 24inch Monitor $229.00 12/14/14 36 7 35 East 2005 24inch Monitor $229.00 12/13/14 7 East 3006 Wireless Keyboard $19.95 12/4/14 49 East 3006 Wireless Keyboard $19.95 12/9/14 47 7 East 3006 Wireless Keyboard $19.95 2/27/14 48 7 East 6050 PC Mouse $8.95 12/23/14 45 East 6050 PC Mouse $8.95 12/13/14 42 East 5050 PC Mouse $8.95 12/3/14 45 East 8500 17inch Laptop $849.95 12/12/14 55 East 8500 17inch Laptop $849.95 12/4/14 57 East 8500 17inch Laptop $849.95 2/25/14 55 24inch Monitor $229.00 12/5/14 18 East 2005 2005 24inch Monitor $229.00 12/10/14 17 East East 2005 24inch Monitor 229.00 12/5/14 23 8 East 3006 Wireless Keyboard $19.95 12/20/14 22 3006 Wireless Keyboard $19.95 12/10/14 18 8 East East 3006 Wireless Keyboard $19.95 12/14/14 22 East 5050 PC Mouse $8.95 2/19/14 14 East 5050 PC Mouse $8.95 12/24/14 16 East 6050 PC Mouse $8.95 12/17/14 17 East 8500 17inch Laptop $849.95 12/3/14 32 East 8500 17inch Laptop $849.95 12/24/14 28 8 $849.95 30 96 East 8500 17inch Laptop 12/14/14 PC Store Revenue by Region + + 110% Ready