Question

1 Approved Answer

Posted on Jul 26, 2024

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

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) Companys 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.

image text in transcribed

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%