The orders tab

The Prices Tab

How would you approach the following functions using these excel tables?

Columns Column 6 Column7 Columns Red Light Pink 8 13 8 White 21 15 16 5 2 4 8 16 12 5 2 3 1 2 4 23 19 8 3 7 10 7 6 14 15 14 9 10 6 8 18 19 8 18 15 8 3 7 2 10 1 Columni Column2 Column3 Column 4 The Whole Bouquet - A Floral Wholesale Supplier January Orders (by Dozen) Order# Order Date Delivery Date Customer 190101 1/2/19 1/11/19 Garcia 190102 1/2/19 2/11/19 Jones 190103 1/2/19 2/12/19 Jones 190104 1/2/19 2/11/19 Rivera 190106 1/2/19 1/11/19 Garcia 190107 1/2/19 1/9/19 Jones 190108 1/2/19 1/11/19 Jones 190109 1/2/19 2/11/19 Rivera 190110 1/2/19 1/9/19 Garcia 190111 1/2/19 2/12/19 Jones 190112 1/2/19 2/11/19 Martin 190113 1/2/19 2/12/19 Martin 190114 1/2/19 1/9/19 Kim 190115 1/2/19 | 2/11/19 Kim 190116 1/2/19 2/12/19 Kim 190117 1/2/19 2/12/19 Kim 190118 1/2/19 2/11/19 Kim 190119 1/2/19 2/12/19 Kim 190120 1/2/19 1/9/19 Smith 190121 1/2/19 2/12/19 Martin 190122 1/2/19 1/9/19 Kim 190123 1/2/19 1/11/19 Rivera 190124 1/2/19 1/9/19 Kim 190125 1/2/19 1/11/19 Kim 190126 1/3/19 2/11/19 Jones 190127 1/3/19 2/12/19 Garcia 190128 1/3/19 2/11/19 Garcia 190129 1/3/19 1/10/19 Garcia 190130 1/3/19 2/12/19 Kim 190131 1/3/19 1/10/19 Smith 190132 1/3/19 2/12/19 Rivera 190133 1/3/19 1/11/19 Smith 190134 1/3/19 2/11/19 Kim 190135 1/3/19 2/11/19 Kim 190136 1/3/19 2/12/19 Kim 190137 1/18/19 Rivera 190138 1/4/19 2/12/19 Garcia 8 2 10 9 9 Floral Type Roses Tulips Carnations Roses Tulips Roses Roses Miniature Roses Carnations Carnations Miniature Roses Carnations Roses Roses Roses Roses Roses Roses Roses Carnations Roses Miniature Roses Roses Roses Roses Tulips Roses Carnations Roses Tulips Miniature Roses Carnations Roses Roses Roses Miniature Roses Roses 4 4 8 2 17 4 3 5 7 2 3 4 1 18 3 3 3 5 9 1 6 6 4 1 6 4 4 4 2 6 13 11 1 2 22 2 17 8 1 8 11 4 5 2 watow on 8 21 2 1 2 1/4/19 3 11 2 6 1 Dark Pink Yellow 9 13 Lilac 4 6 Special 4 5 Total 70 75 62 26 11 3 4 11 4 4 2 2 1 2 1 3 12 4 1 5 2 1 5 Include Baby's Breath? Discounts 11 Yes 12 Yes 8 Yes 5 Yes 2 Yes 3 Yes 16 No 4 Yes 9 Yes 11 No 6 Yes 11 No 18 88 2 28 12 3 4 59 4 72 14 9 16 5 2 6 2 43 5 82 85 5 Yes 5 2 17 5 Yes 1 16 5 2 7 4 10 1 1 3 Yes 2 8 31 11 44 2 Yes 2 2 5 7 13 No Yes 4 37 75 16 15 Yes 3 2 1 1 1 1 1 1 2 3 Order Source 15% Phone 0% Main Website 0% Phone 0 0% Promotional Website 0% Main Website 0 0% Main Website 0% Phone 15% Main Website 0% Promotional Website 12% Phone 15% Phone 0% Phone 12% Phone 15% E-mail 0% Promotional Website 12% Phone 12% Main Website 15% Main Website 5% Phone 15% Phone 15% Phone 15% Phone 5% E-mail 12% Promotional Website 12% E-mail 5% Main Website 0% Main Website 12% E-mail 15% Main Website 15% E-mail 0% Main Website 15% Main Website 15% Main Website 15% Main Website 12% Promotional Website 12% Main Website 0% Promotional Website 10% E-mail 12% Main Website 12% Main Website Yes Yes 1 1 1 3 21 3 5 1 2 2 Yes 2 0 0 Yes 34 5 31 7 2 5 1 5 3 10 1 1 1 No Yes 4 19 1 3 7 2 2 1 5 Yes Yes 0 2 52 10 91 4 16 2 14 1 1 5 3 17 3 13 7 3 Yes Yes Yes Yes 13 79 4 3 10 3 13 1 1 Yes Yes 50 18 77 6 4 5 11 1 0 0 1 1 1 15 2 3 3 8 1 8 0 2 7 1 5 4 5 0 No Yes Yes Yes Yes Yes Yes 49 6 6 37 2 2 2 1 5 6 28 36 Wholesale Price List (per dozen) Roses 23 Miniature Roses 14 Tulips 18 Lilies 19 Carnations 11 Baby's Breath = Add $5 per dozen to total order Special Color = Add 20% to the prices 4. Add a series of columns to your Orders data table that will result in displaying the total price for each order. At a minimum, you will need the following four columns, however you may find it helpful to add additional columns to further break down the calculations. a. Add a column that will use a xLookp function to display the price based on the type of flower listed on the "Prices" tab. b. Add a column that will calculate the price of baby's breath. c. Add a column that will add the surcharge for special colors. d. Add a column that will calculate the total order price, adjusting for any discounts. 5. What is the percentage growth from January 2018 to January 2019. (Note: There are a couple of ways that you can approach this task.) 6. Use a "Countlf" function to determine how many orders included baby's breath. Calculate the total sales revenue for baby's breath. 7. Create at least five pivot tables (you may add others): a. Calculate the total number (by dozens) of each color, of each type of flower. Add a calculated field to determine how many (total stems) of red roses were ordered in the month. b. Calculate the total sales (by revenue) by each order source. c. Calculate the average % discount by customer. d. Use a grouping function to calculate total sales (by revenue) by week. e. Create a pivot table that summarizes orders by order date and delivery date. Columns Column 6 Column7 Columns Red Light Pink 8 13 8 White 21 15 16 5 2 4 8 16 12 5 2 3 1 2 4 23 19 8 3 7 10 7 6 14 15 14 9 10 6 8 18 19 8 18 15 8 3 7 2 10 1 Columni Column2 Column3 Column 4 The Whole Bouquet - A Floral Wholesale Supplier January Orders (by Dozen) Order# Order Date Delivery Date Customer 190101 1/2/19 1/11/19 Garcia 190102 1/2/19 2/11/19 Jones 190103 1/2/19 2/12/19 Jones 190104 1/2/19 2/11/19 Rivera 190106 1/2/19 1/11/19 Garcia 190107 1/2/19 1/9/19 Jones 190108 1/2/19 1/11/19 Jones 190109 1/2/19 2/11/19 Rivera 190110 1/2/19 1/9/19 Garcia 190111 1/2/19 2/12/19 Jones 190112 1/2/19 2/11/19 Martin 190113 1/2/19 2/12/19 Martin 190114 1/2/19 1/9/19 Kim 190115 1/2/19 | 2/11/19 Kim 190116 1/2/19 2/12/19 Kim 190117 1/2/19 2/12/19 Kim 190118 1/2/19 2/11/19 Kim 190119 1/2/19 2/12/19 Kim 190120 1/2/19 1/9/19 Smith 190121 1/2/19 2/12/19 Martin 190122 1/2/19 1/9/19 Kim 190123 1/2/19 1/11/19 Rivera 190124 1/2/19 1/9/19 Kim 190125 1/2/19 1/11/19 Kim 190126 1/3/19 2/11/19 Jones 190127 1/3/19 2/12/19 Garcia 190128 1/3/19 2/11/19 Garcia 190129 1/3/19 1/10/19 Garcia 190130 1/3/19 2/12/19 Kim 190131 1/3/19 1/10/19 Smith 190132 1/3/19 2/12/19 Rivera 190133 1/3/19 1/11/19 Smith 190134 1/3/19 2/11/19 Kim 190135 1/3/19 2/11/19 Kim 190136 1/3/19 2/12/19 Kim 190137 1/18/19 Rivera 190138 1/4/19 2/12/19 Garcia 8 2 10 9 9 Floral Type Roses Tulips Carnations Roses Tulips Roses Roses Miniature Roses Carnations Carnations Miniature Roses Carnations Roses Roses Roses Roses Roses Roses Roses Carnations Roses Miniature Roses Roses Roses Roses Tulips Roses Carnations Roses Tulips Miniature Roses Carnations Roses Roses Roses Miniature Roses Roses 4 4 8 2 17 4 3 5 7 2 3 4 1 18 3 3 3 5 9 1 6 6 4 1 6 4 4 4 2 6 13 11 1 2 22 2 17 8 1 8 11 4 5 2 watow on 8 21 2 1 2 1/4/19 3 11 2 6 1 Dark Pink Yellow 9 13 Lilac 4 6 Special 4 5 Total 70 75 62 26 11 3 4 11 4 4 2 2 1 2 1 3 12 4 1 5 2 1 5 Include Baby's Breath? Discounts 11 Yes 12 Yes 8 Yes 5 Yes 2 Yes 3 Yes 16 No 4 Yes 9 Yes 11 No 6 Yes 11 No 18 88 2 28 12 3 4 59 4 72 14 9 16 5 2 6 2 43 5 82 85 5 Yes 5 2 17 5 Yes 1 16 5 2 7 4 10 1 1 3 Yes 2 8 31 11 44 2 Yes 2 2 5 7 13 No Yes 4 37 75 16 15 Yes 3 2 1 1 1 1 1 1 2 3 Order Source 15% Phone 0% Main Website 0% Phone 0 0% Promotional Website 0% Main Website 0 0% Main Website 0% Phone 15% Main Website 0% Promotional Website 12% Phone 15% Phone 0% Phone 12% Phone 15% E-mail 0% Promotional Website 12% Phone 12% Main Website 15% Main Website 5% Phone 15% Phone 15% Phone 15% Phone 5% E-mail 12% Promotional Website 12% E-mail 5% Main Website 0% Main Website 12% E-mail 15% Main Website 15% E-mail 0% Main Website 15% Main Website 15% Main Website 15% Main Website 12% Promotional Website 12% Main Website 0% Promotional Website 10% E-mail 12% Main Website 12% Main Website Yes Yes 1 1 1 3 21 3 5 1 2 2 Yes 2 0 0 Yes 34 5 31 7 2 5 1 5 3 10 1 1 1 No Yes 4 19 1 3 7 2 2 1 5 Yes Yes 0 2 52 10 91 4 16 2 14 1 1 5 3 17 3 13 7 3 Yes Yes Yes Yes 13 79 4 3 10 3 13 1 1 Yes Yes 50 18 77 6 4 5 11 1 0 0 1 1 1 15 2 3 3 8 1 8 0 2 7 1 5 4 5 0 No Yes Yes Yes Yes Yes Yes 49 6 6 37 2 2 2 1 5 6 28 36 Wholesale Price List (per dozen) Roses 23 Miniature Roses 14 Tulips 18 Lilies 19 Carnations 11 Baby's Breath = Add $5 per dozen to total order Special Color = Add 20% to the prices 4. Add a series of columns to your Orders data table that will result in displaying the total price for each order. At a minimum, you will need the following four columns, however you may find it helpful to add additional columns to further break down the calculations. a. Add a column that will use a xLookp function to display the price based on the type of flower listed on the "Prices" tab. b. Add a column that will calculate the price of baby's breath. c. Add a column that will add the surcharge for special colors. d. Add a column that will calculate the total order price, adjusting for any discounts. 5. What is the percentage growth from January 2018 to January 2019. (Note: There are a couple of ways that you can approach this task.) 6. Use a "Countlf" function to determine how many orders included baby's breath. Calculate the total sales revenue for baby's breath. 7. Create at least five pivot tables (you may add others): a. Calculate the total number (by dozens) of each color, of each type of flower. Add a calculated field to determine how many (total stems) of red roses were ordered in the month. b. Calculate the total sales (by revenue) by each order source. c. Calculate the average % discount by customer. d. Use a grouping function to calculate total sales (by revenue) by week. e. Create a pivot table that summarizes orders by order date and delivery date