B E F H K 1 A D E j L M 1 The goal of the business problem is create 4 columns with formulas. The four formula requirements are listed below: 2 The Formula for Price of Product must be created in cell E16 and then copied down the column. 3 The Formula for Discount Rate must be created in cell F16 and then copied down the column. 4 The Formula for Net Revenue must be created in cell G16 and then copied down the column. 5 The Formula for Size of Sale must be created in cell H16 and then copied down the column. 6 The Price of Product formula requires that you lookup the Product Price from the Product Lookup Table 7 The Discount Rate formula requires that you lookup the Discount from the Discount Lookup Table 8 The Net Revenue formula requires that you multiply: Units . Price of Product *(1-Discount Rate). 9 The Size of Sale formula requires that you categorize the Net Revenue as 'Large' or 'Small'. If the Net Revenue is greater than 1000, then the Net Revenue is 'Large', otherwise it is 'Small' 10 The categories for 'Large' and 'Small' may change. 11 The check figures for the last record in the table are seen here => 8/28/2019 Quad 131 43.95 0.525 2734.79 Large 12 13 14 Units Discount Rate Net Revenue Size of Sale Product Lookup Table: Price Price of Product 31 50 60 90 13 Product Carlota Flattop Quad Sunshine Range (m) 24 meters 45 meters 22 meters 30 meters Birch (mm) 8 mm birch 12 mm birch 8 mm birch 5 mm birch 29.99 22.95 43.99 95 19.99 60 Discount Lookup Table: Units 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Date Product 8/3/2019 Carlota 8/16/2019 Flattop 8/4/2019 Flattop 8/6/2019 Carlota 8/6/2019 Sunshine 8/1/2019 Flattop 8/20/2019 Sunshine 8/18/2019 Carlota 8/7/2019 Flattop 8/21/2019 Flattop 8/22/2019 Flattop 8/13/2019 Flattop 8/13/2019 Flattop 8/20/2019 Carlota 8/14/2019 Carlota 8/20/2019 Sunshine 8/5/2019 Carlota 8/23/2019 Carlota 8/18/2019 Carlota 8/6/2019 Flattop 8/1/2019 Flattop 8/21/2019 Carlota 8/28/2019 Quad 76 3 10 77 29 114 64 104 42 72 82 Discount Rate 0 0 5 0.05 12 0.15 24 0.375 96 0.525 Large or Small Assumption Table: 39 Hurdle Large Large Small Small 81 1000 12 37 38 98 131 B E F H K 1 A D E j L M 1 The goal of the business problem is create 4 columns with formulas. The four formula requirements are listed below: 2 The Formula for Price of Product must be created in cell E16 and then copied down the column. 3 The Formula for Discount Rate must be created in cell F16 and then copied down the column. 4 The Formula for Net Revenue must be created in cell G16 and then copied down the column. 5 The Formula for Size of Sale must be created in cell H16 and then copied down the column. 6 The Price of Product formula requires that you lookup the Product Price from the Product Lookup Table 7 The Discount Rate formula requires that you lookup the Discount from the Discount Lookup Table 8 The Net Revenue formula requires that you multiply: Units . Price of Product *(1-Discount Rate). 9 The Size of Sale formula requires that you categorize the Net Revenue as 'Large' or 'Small'. If the Net Revenue is greater than 1000, then the Net Revenue is 'Large', otherwise it is 'Small' 10 The categories for 'Large' and 'Small' may change. 11 The check figures for the last record in the table are seen here => 8/28/2019 Quad 131 43.95 0.525 2734.79 Large 12 13 14 Units Discount Rate Net Revenue Size of Sale Product Lookup Table: Price Price of Product 31 50 60 90 13 Product Carlota Flattop Quad Sunshine Range (m) 24 meters 45 meters 22 meters 30 meters Birch (mm) 8 mm birch 12 mm birch 8 mm birch 5 mm birch 29.99 22.95 43.99 95 19.99 60 Discount Lookup Table: Units 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Date Product 8/3/2019 Carlota 8/16/2019 Flattop 8/4/2019 Flattop 8/6/2019 Carlota 8/6/2019 Sunshine 8/1/2019 Flattop 8/20/2019 Sunshine 8/18/2019 Carlota 8/7/2019 Flattop 8/21/2019 Flattop 8/22/2019 Flattop 8/13/2019 Flattop 8/13/2019 Flattop 8/20/2019 Carlota 8/14/2019 Carlota 8/20/2019 Sunshine 8/5/2019 Carlota 8/23/2019 Carlota 8/18/2019 Carlota 8/6/2019 Flattop 8/1/2019 Flattop 8/21/2019 Carlota 8/28/2019 Quad 76 3 10 77 29 114 64 104 42 72 82 Discount Rate 0 0 5 0.05 12 0.15 24 0.375 96 0.525 Large or Small Assumption Table: 39 Hurdle Large Large Small Small 81 1000 12 37 38 98 131