Slyses ELE UNIVERSE TO IF and Nested IF (Spoints) 1. Examine the Assignment details worksheet and enter your full name. 2. Click on the Customer Relations worksheet and play around with the data by sorting, filtering etc. Use various filtering options such as text options, 3. Remove all filets and sort data based on Order date. 4. Click on the IF & Nested IF sheet: 1. In E6, enter the column heading 2-Tier Commission. 2. In E7, enter a formula. If the Sale Price is at least 550,000, multiply the Sale Price by 5%, otherwise multiple Sale Price by 2%. Apply the currency format and copy the formula down the remaining rows. 3. In F6, enter the column heading 3-Tier Commission. Resize your column suitably. 4. In F7, enter a formula. If the Sale Price is less than $50,000, multiply the Sale Price by 2%. If the Sale Price is between $50,000, and $100,000, multiply Sale Price by 3%. If Sale Price is more than $100,000, multiply by 5%. Hint: Put the most difficult operation as the last part in your formula. It's easy to calculate values for "if less than $50,000" and "if greater than $100,000". Calculating the in-between values is more difficult so make it the last operation. Apply the currency format and copy the formula down the remaining rows. 5. Examine how the agent commission values differ when you use a 3-Tier Agent Commission versus a 2-Tier Agent Commission NAME RANGE/CELL (5 points) The Name Range worksheet provides a snapshot of the data for mozzarella cheese bought by each customer. Name Ranges can be created in two ways: a) By typing the name e.g. BaseSale in the cell address field. b) By clicking on the Formula Tab>>Define Name and entering the name in the textbox for the name. You can also restrict the scope of the Range Name by selecting the appropriate worksheet in this dialog box. 1. Name the range E16: Base Sale 2. Name the range E 17 DiscountRate 3. Name the range E18: TaxRate 4. Copy the appropriate sales data under Column E (Total Sales) for each customer. 5. In row 21 enter formulas and copy down using the range names in cells E16, E17 and E18 instead of absolute cell references in the formulas. 6. F21 Discount Amount: Discounts are given based on three tiers of purchase given in cells E17, F17, G17. Use an IF formula to determine the discount. 7. G21 Statement Credit: If customers opt to take a statement credit and adjust that towards future purchases, they get 1.5 times the discount amount and the entire annual purchase is taxed at the rate given in E18. 8. H21 Invoice Total: If customers opt to not take a statement credit, wing Font Heading 2 Title Subtitle Subtle minhas Paragraph amount and the entire annual purchase is taxed at the rate given in E18. 8. H21 Invoice Total: If customers opt to not take a statement credit, subtract the discount amount from the total annual purchase and apply the tax rate after applying the discount. 9. 121 Message: If the customer got a discount, they message should read: "Thank you for your business!". If they did not get a bonus, the message should read "Buy more cheese!" 10.Conditional Formatting: In column F, use conditional formatting to make discount amounts display in Green. The zero bonus amounts should stay black. COLUMN CHARTS (points) 1. Navigate to the Column Charts worksheet. 2. The first three columns provide the daily data for a teleshopping call center selling fashion products. 3. Summarize the daily data to weekly data using formulas. *, * AL-A - - - ) 1 Normal 1 No Space Heading Heading 2 Title Subtitle Subtle Em Impha font N Paragraph Styles COLUMN CHARTS (5 points) 1. Navigate to the Column Charts worksheet, 2. The first three columns provide the daily data for a teleshopping call center selling fashion products. 3. Summarize the daily data to weekly data using formulas. 4. Enter the SUM formula in G6 to add the sales volume data for the first week 1/1/2011 to 1/7/2011 from the daily data table. Next, in G7, summarize date for 1/8/2011 to 1/14/2011. Repeat for the remaining weeks and populate all values for 26 weeks. 5. Enter the SUM formula in H6 to add the sales data for the first week from the daily data table. Next, in H7, summarize date for 1/8/2011 to 1/14/2011. Repeat for the remaining weeks and populate all values for 26 weeks. 6. Create a clustered column chart to display the weekly data for Sales volume and Actual Calls. 7. Title the chart WEEKLY SALES PERFORMANCE 8. Add the vertical and horizontal axis, if not already created, 9. Format your chart to display the values for Sales and Calls for each week. PIE CHARTS (5 points) 10. Click on the Pie Charts worksheet 11.Create a 3D-Pie chart of the products purchased by Bert's Bistro using the Product Name and Total Cost columns. Fort Normal 1 No Space Heading 1 Heading 2 VOD ABCD ABD ABD Title Subtitle Subtle Em.mches Paragraph WEEKS. 6. Create a clustered column chart to display the weekly data for Sales volume and Actual Calls. 7. Title the chart WEEKLY SALES PERFORMANCE 8. Add the vertical and horizontal axis, if not already created. 9. Format your chart to display the values for Sales and Calls for each week. PIE CHARTS (5 points) 10.Click on the Pie Charts worksheet 11. Create a 3D-Pie chart of the products purchased by Bert's Bistro using the Product Name and Total Cost columns. 12.Format the legend appropriately if required. 13. Add the Data Labels to show the amount 14. Format the data labels to display both amount and percentage. 15. Title the chart Bert's Bistro Purchases. Position the chart in E18:K36. Submission Instructions: Name your file as your LastName FirstName Excel-3.x/sx and submit on Blackboard. Customer 14 23 Customer Pico AW Smicho Two flits Mam Pesta Place The St Camous Flagstaff House Productio ProductName 22 March 22 More Cheese 92 Noche 22 March 22 March 22Meches M house Mocht 32 March Total Annual Purchase $55 071.00 2041700 $13 5.40 501.00 SOM OD 36930 142 502 00 12531300 $30 00 00 657 572 140 The Dundon Baso Salo Discount Rate Tax Rate $30 000 00 3 000% 3 250 Total Annual Purchase Discount Amount Statement Credit Invoice Tot Message Customer Carmen Flagstal House LINK 'Past Place Pate TN Senoko House The Dandelion The Stadion Two fits 0 c D E @ 11 Weekly Data Sales Calls 2 13 Daily Data 4 5 Date Sales volume. Actual calls 1/1/2011 12 10 7 1/2/2011 14 11 8 1/3/2011 18 10 19 1/4/2011 15 12 10 1/5/2011 14 13 11 1/6/2011 10 13 12 1/7/2011 13 13 13 1/8/2011 15 10 1/9/2011 21 10 15 110/2011 22 11 16 1/11/2011 23 10 17 1/12/2011 23 12 18 1/13/2011 23 13 19 1/14/2011 26 13 20 1/15/2011 25 12 1 1/16/2011 25 10 - 1/17/2011 23 10 23 1/11/2011 22 11 4 1/19/2011 23 10 5 1/20/2011 24 12 26 1/21/2011 22 13 e 1/22/2011 25 13 8 1/23/2011 27 12 1/24/2011 28 10 0 1/25/2011 29 10 1 1/26/2011 29 11 2 1/27/2011 27 14 3 1/28/2011 29 12 1 1/2/2011 28 13 5 1/30/2011 30 13 1/31/2011 31 13 22/1/2011 31 10 Customer Relations IF & Nested IF Week 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Name Range Column Charts Pie Charts Excel 3 Assignment Data File e Select 2 Home Insert Draw Page Layout Formulas Data Review View Help > w A A c D F G N Order Date Product 05 Apr 11 100 2.1 10 20. Act 19 May 11 16 02 Jun 11 89 22.11 31 13 Aug 2 10 Aug 11 24 Alan 11 16 18 Sep 11 31 29 Od 11 39 27 Oct 11 4 29 Dec 11 Product Name Parmesan cheese Romettuce Tomatoes Romaine leuce Peggero Peppers Quantity Unit Price Total Sales Unit Cost Total Cost Profit Customer Customer Sales Rep 8.00 792.00 $ 400 ES 3000$ 365 DO 233 Barts Bio Roberta Crus 54 310 DOIS 14.00 $ 756 00 $ 54.00 23 Borrs Biro Lorame chutz 3 9001 738 00 15 700 57400 $ 164 00 233 Bert's Bistro Lomaine Sot 09 $15.00 70 005 14 001 5 1 372.00 $98.00 233 Bert's Bistro Roberts Cross 63 $53003 33119.00 15 3500 $ 2.206.00 $1,134 00 233 Barts Bestro Robert Cross 3 $ 21.00 6300 5 12 00S 3600S 27.00 213 Burt's Best Robert Cross 22 $12.00 264.00 $ 6.005 132 00 $ 132 00 231 Bert's Hidroline Sicht 17 590000315 7 00 15 469005134 00 233 Bert's Bestro Loraine Schult 58 $15.00 S 340.001s 14.00$ 784 00$ 55.00 233 Bos Bistro Roberta Cro $ 2100 $ 2 037 00S 1200$ 1.166.00 $ 873.00 233 Bart's Bio Roberta Cross 58 S 33 001 914 DO 2500 5 1,62400 $ 210.00 233 Berr's Cross 5 9.00$ 74700 700 $ 58100 $ 100 00 233 Bert's Betro Lone Schi 45 18 00 310005 8.00 $ 300 00S 450.00 Bert's Bestro Roberto Tomatoes Romine fettuce Perpen Pineapple Tomatoes 23 Slyses ELE UNIVERSE TO IF and Nested IF (Spoints) 1. Examine the Assignment details worksheet and enter your full name. 2. Click on the Customer Relations worksheet and play around with the data by sorting, filtering etc. Use various filtering options such as text options, 3. Remove all filets and sort data based on Order date. 4. Click on the IF & Nested IF sheet: 1. In E6, enter the column heading 2-Tier Commission. 2. In E7, enter a formula. If the Sale Price is at least 550,000, multiply the Sale Price by 5%, otherwise multiple Sale Price by 2%. Apply the currency format and copy the formula down the remaining rows. 3. In F6, enter the column heading 3-Tier Commission. Resize your column suitably. 4. In F7, enter a formula. If the Sale Price is less than $50,000, multiply the Sale Price by 2%. If the Sale Price is between $50,000, and $100,000, multiply Sale Price by 3%. If Sale Price is more than $100,000, multiply by 5%. Hint: Put the most difficult operation as the last part in your formula. It's easy to calculate values for "if less than $50,000" and "if greater than $100,000". Calculating the in-between values is more difficult so make it the last operation. Apply the currency format and copy the formula down the remaining rows. 5. Examine how the agent commission values differ when you use a 3-Tier Agent Commission versus a 2-Tier Agent Commission Slyses ELE UNIVERSE TO IF and Nested IF (Spoints) 1. Examine the Assignment details worksheet and enter your full name. 2. Click on the Customer Relations worksheet and play around with the data by sorting, filtering etc. Use various filtering options such as text options, 3. Remove all filets and sort data based on Order date. 4. Click on the IF & Nested IF sheet: 1. In E6, enter the column heading 2-Tier Commission. 2. In E7, enter a formula. If the Sale Price is at least 550,000, multiply the Sale Price by 5%, otherwise multiple Sale Price by 2%. Apply the currency format and copy the formula down the remaining rows. 3. In F6, enter the column heading 3-Tier Commission. Resize your column suitably. 4. In F7, enter a formula. If the Sale Price is less than $50,000, multiply the Sale Price by 2%. If the Sale Price is between $50,000, and $100,000, multiply Sale Price by 3%. If Sale Price is more than $100,000, multiply by 5%. Hint: Put the most difficult operation as the last part in your formula. It's easy to calculate values for "if less than $50,000" and "if greater than $100,000". Calculating the in-between values is more difficult so make it the last operation. Apply the currency format and copy the formula down the remaining rows. 5. Examine how the agent commission values differ when you use a 3-Tier Agent Commission versus a 2-Tier Agent Commission NAME RANGE/CELL (5 points) The Name Range worksheet provides a snapshot of the data for mozzarella cheese bought by each customer. Name Ranges can be created in two ways: a) By typing the name e.g. BaseSale in the cell address field. b) By clicking on the Formula Tab>>Define Name and entering the name in the textbox for the name. You can also restrict the scope of the Range Name by selecting the appropriate worksheet in this dialog box. 1. Name the range E16: Base Sale 2. Name the range E 17 DiscountRate 3. Name the range E18: TaxRate 4. Copy the appropriate sales data under Column E (Total Sales) for each customer. 5. In row 21 enter formulas and copy down using the range names in cells E16, E17 and E18 instead of absolute cell references in the formulas. 6. F21 Discount Amount: Discounts are given based on three tiers of purchase given in cells E17, F17, G17. Use an IF formula to determine the discount. 7. G21 Statement Credit: If customers opt to take a statement credit and adjust that towards future purchases, they get 1.5 times the discount amount and the entire annual purchase is taxed at the rate given in E18. 8. H21 Invoice Total: If customers opt to not take a statement credit, wing Font Heading 2 Title Subtitle Subtle minhas Paragraph amount and the entire annual purchase is taxed at the rate given in E18. 8. H21 Invoice Total: If customers opt to not take a statement credit, subtract the discount amount from the total annual purchase and apply the tax rate after applying the discount. 9. 121 Message: If the customer got a discount, they message should read: "Thank you for your business!". If they did not get a bonus, the message should read "Buy more cheese!" 10.Conditional Formatting: In column F, use conditional formatting to make discount amounts display in Green. The zero bonus amounts should stay black. COLUMN CHARTS (points) 1. Navigate to the Column Charts worksheet. 2. The first three columns provide the daily data for a teleshopping call center selling fashion products. 3. Summarize the daily data to weekly data using formulas. *, * AL-A - - - ) 1 Normal 1 No Space Heading Heading 2 Title Subtitle Subtle Em Impha font N Paragraph Styles COLUMN CHARTS (5 points) 1. Navigate to the Column Charts worksheet, 2. The first three columns provide the daily data for a teleshopping call center selling fashion products. 3. Summarize the daily data to weekly data using formulas. 4. Enter the SUM formula in G6 to add the sales volume data for the first week 1/1/2011 to 1/7/2011 from the daily data table. Next, in G7, summarize date for 1/8/2011 to 1/14/2011. Repeat for the remaining weeks and populate all values for 26 weeks. 5. Enter the SUM formula in H6 to add the sales data for the first week from the daily data table. Next, in H7, summarize date for 1/8/2011 to 1/14/2011. Repeat for the remaining weeks and populate all values for 26 weeks. 6. Create a clustered column chart to display the weekly data for Sales volume and Actual Calls. 7. Title the chart WEEKLY SALES PERFORMANCE 8. Add the vertical and horizontal axis, if not already created, 9. Format your chart to display the values for Sales and Calls for each week. PIE CHARTS (5 points) 10. Click on the Pie Charts worksheet 11.Create a 3D-Pie chart of the products purchased by Bert's Bistro using the Product Name and Total Cost columns. Fort Normal 1 No Space Heading 1 Heading 2 VOD ABCD ABD ABD Title Subtitle Subtle Em.mches Paragraph WEEKS. 6. Create a clustered column chart to display the weekly data for Sales volume and Actual Calls. 7. Title the chart WEEKLY SALES PERFORMANCE 8. Add the vertical and horizontal axis, if not already created. 9. Format your chart to display the values for Sales and Calls for each week. PIE CHARTS (5 points) 10.Click on the Pie Charts worksheet 11. Create a 3D-Pie chart of the products purchased by Bert's Bistro using the Product Name and Total Cost columns. 12.Format the legend appropriately if required. 13. Add the Data Labels to show the amount 14. Format the data labels to display both amount and percentage. 15. Title the chart Bert's Bistro Purchases. Position the chart in E18:K36. Submission Instructions: Name your file as your LastName FirstName Excel-3.x/sx and submit on Blackboard. Customer 14 23 Customer Pico AW Smicho Two flits Mam Pesta Place The St Camous Flagstaff House Productio ProductName 22 March 22 More Cheese 92 Noche 22 March 22 March 22Meches M house Mocht 32 March Total Annual Purchase $55 071.00 2041700 $13 5.40 501.00 SOM OD 36930 142 502 00 12531300 $30 00 00 657 572 140 The Dundon Baso Salo Discount Rate Tax Rate $30 000 00 3 000% 3 250 Total Annual Purchase Discount Amount Statement Credit Invoice Tot Message Customer Carmen Flagstal House LINK 'Past Place Pate TN Senoko House The Dandelion The Stadion Two fits 0 c D E @ 11 Weekly Data Sales Calls 2 13 Daily Data 4 5 Date Sales volume. Actual calls 1/1/2011 12 10 7 1/2/2011 14 11 8 1/3/2011 18 10 19 1/4/2011 15 12 10 1/5/2011 14 13 11 1/6/2011 10 13 12 1/7/2011 13 13 13 1/8/2011 15 10 1/9/2011 21 10 15 110/2011 22 11 16 1/11/2011 23 10 17 1/12/2011 23 12 18 1/13/2011 23 13 19 1/14/2011 26 13 20 1/15/2011 25 12 1 1/16/2011 25 10 - 1/17/2011 23 10 23 1/11/2011 22 11 4 1/19/2011 23 10 5 1/20/2011 24 12 26 1/21/2011 22 13 e 1/22/2011 25 13 8 1/23/2011 27 12 1/24/2011 28 10 0 1/25/2011 29 10 1 1/26/2011 29 11 2 1/27/2011 27 14 3 1/28/2011 29 12 1 1/2/2011 28 13 5 1/30/2011 30 13 1/31/2011 31 13 22/1/2011 31 10 Customer Relations IF & Nested IF Week 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Name Range Column Charts Pie Charts Excel 3 Assignment Data File e Select 2 Home Insert Draw Page Layout Formulas Data Review View Help > w A A c D F G N Order Date Product 05 Apr 11 100 2.1 10 20. Act 19 May 11 16 02 Jun 11 89 22.11 31 13 Aug 2 10 Aug 11 24 Alan 11 16 18 Sep 11 31 29 Od 11 39 27 Oct 11 4 29 Dec 11 Product Name Parmesan cheese Romettuce Tomatoes Romaine leuce Peggero Peppers Quantity Unit Price Total Sales Unit Cost Total Cost Profit Customer Customer Sales Rep 8.00 792.00 $ 400 ES 3000$ 365 DO 233 Barts Bio Roberta Crus 54 310 DOIS 14.00 $ 756 00 $ 54.00 23 Borrs Biro Lorame chutz 3 9001 738 00 15 700 57400 $ 164 00 233 Bert's Bistro Lomaine Sot 09 $15.00 70 005 14 001 5 1 372.00 $98.00 233 Bert's Bistro Roberts Cross 63 $53003 33119.00 15 3500 $ 2.206.00 $1,134 00 233 Barts Bestro Robert Cross 3 $ 21.00 6300 5 12 00S 3600S 27.00 213 Burt's Best Robert Cross 22 $12.00 264.00 $ 6.005 132 00 $ 132 00 231 Bert's Hidroline Sicht 17 590000315 7 00 15 469005134 00 233 Bert's Bestro Loraine Schult 58 $15.00 S 340.001s 14.00$ 784 00$ 55.00 233 Bos Bistro Roberta Cro $ 2100 $ 2 037 00S 1200$ 1.166.00 $ 873.00 233 Bart's Bio Roberta Cross 58 S 33 001 914 DO 2500 5 1,62400 $ 210.00 233 Berr's Cross 5 9.00$ 74700 700 $ 58100 $ 100 00 233 Bert's Betro Lone Schi 45 18 00 310005 8.00 $ 300 00S 450.00 Bert's Bestro Roberto Tomatoes Romine fettuce Perpen Pineapple Tomatoes 23 Slyses ELE UNIVERSE TO IF and Nested IF (Spoints) 1. Examine the Assignment details worksheet and enter your full name. 2. Click on the Customer Relations worksheet and play around with the data by sorting, filtering etc. Use various filtering options such as text options, 3. Remove all filets and sort data based on Order date. 4. Click on the IF & Nested IF sheet: 1. In E6, enter the column heading 2-Tier Commission. 2. In E7, enter a formula. If the Sale Price is at least 550,000, multiply the Sale Price by 5%, otherwise multiple Sale Price by 2%. Apply the currency format and copy the formula down the remaining rows. 3. In F6, enter the column heading 3-Tier Commission. Resize your column suitably. 4. In F7, enter a formula. If the Sale Price is less than $50,000, multiply the Sale Price by 2%. If the Sale Price is between $50,000, and $100,000, multiply Sale Price by 3%. If Sale Price is more than $100,000, multiply by 5%. Hint: Put the most difficult operation as the last part in your formula. It's easy to calculate values for "if less than $50,000" and "if greater than $100,000". Calculating the in-between values is more difficult so make it the last operation. Apply the currency format and copy the formula down the remaining rows. 5. Examine how the agent commission values differ when you use a 3-Tier Agent Commission versus a 2-Tier Agent Commission
