please solve this asap! i have to submit this assignment tomorrow morning
Open Sheet 1 1. Use an IF statement in D4 to determine if a sales quota has been met and if it has, inserts a bonus of 15% of actual sales. If the quota has not been met, the formula will insert O. Use the fill handle to copy the formula into the range of D5:09 2. Use an IF statement in E4 to determine if a sales quota has been met and if it has, inserts a bonus of 20% of actual sales. If the quota has not been met, show nothing in the cell. Use the fill handle to copy the formula into the range E4:09. 3. Use an IF statement in C14 to determine whether the amount in cell B14 is greater than $599. If the number is greater than $599, then the produce will have a 5% discount applied to the price. If the number is not greater than $599, the formula will insert O. Use the fill handle to copy the formula into the range of C15:C26. Create a formula in cell D14 to determine what the price will be for each product # after the 5% discount is applied. Use the fill handle to copy the formula into the appropriate cells. 4. Determine the quiz averages by inserting a function in cell E31 that calculates the average for the test scores in the range B31:031. Use the fill handle to copy the function over the appropriate cells. 5. Use an IF statement in F31 that inserts the word PASS if the quiz average is greater than 79 and inserts the word FAIL if the quiz average is not greater than 79. Copy the formula in cell F31 down into the range F32:F35. Open Sheet 2 In column D 1. Determine what a 5% bonus would be if the salesperson surpassed their quota. The total should include the actual sales and bonus. If the salesperson does not receive a bonus they will receive their actual sales amount. Use an absolute cell reference. Change to comma formatting and zero decimal places. In columne 1. Determine an additional 15% sales bonus to only those employees that surpassed a total of $130,000 from column D, otherwise show a blank. Only show the value of the bonus, do not add it back in. Use an absolute cell reference. Change to comma formatting, zero decimals. In column F 1. Create an IF statement in column F that will create a comment "Great Job", to those employees that surpassed their quota and a comment "Work Harder" to those that did not 4 E E F G H H 1 1 2 3 4 Capstan Marine Products Sales Department Salesperson Quota Actual Sales Bonus Bonus Allejandro $ 95,500.00 $ 1,03,295.00 Crispin 1,37,000.00 1,29,890.00 Frankel 1,24,000.00 1,33,255.00 Hiesmann 85,500.00 94,350.00 Jarvis 1,59,000.00 1,67,410.00 Littleman 1,10,500.00 1,09,980.00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Discount Price $ Product # C-2340 C-3215 C-4390 E-2306 E-3420 G-2312 G 4393 J-1203 J-3288 1-4594 M-2355 M-3129 M-4392 Sales Department Price Discount 1,250.00 695.00 475.00 225.00 520.00 2,150.00 2,450.00 755.00 455.00 1,050.00 890.00 645.00 475.00 20 21 22 23 24 25 26 27 Average Grade 28 29 30 31 32 33 34 35 36 37 38 39 Name Angelo Cunningham Elliot Kennedy Lipscomb Quiz 1 78 90 82 100 64 New Employee Orientation Quiz 2 Quiz 3 69 88 95 86 88 94 98 96 76 62 40 41 fx A 8 B E F G H Bonu s 15% Comments to Employee 1 2 3 Bonus alespersor Quota Actual Sale 5% Sam 85,500 1,05,295 Jerry 1,40,000 1,29,990 Jillian 1,25,000 2,33,255 Kelsey 95,500 93,350 Hayden 1,60,000 1,77,410 Cora 1,19,500 1,10,980 A 4 5 6 LO 7 8 9 59 15% 10 11 12 13 14 15 16 17 18 19 20 Open Sheet 1 1. Use an IF statement in D4 to determine if a sales quota has been met and if it has, inserts a bonus of 15% of actual sales. If the quota has not been met, the formula will insert O. Use the fill handle to copy the formula into the range of D5:09 2. Use an IF statement in E4 to determine if a sales quota has been met and if it has, inserts a bonus of 20% of actual sales. If the quota has not been met, show nothing in the cell. Use the fill handle to copy the formula into the range E4:09. 3. Use an IF statement in C14 to determine whether the amount in cell B14 is greater than $599. If the number is greater than $599, then the produce will have a 5% discount applied to the price. If the number is not greater than $599, the formula will insert O. Use the fill handle to copy the formula into the range of C15:C26. Create a formula in cell D14 to determine what the price will be for each product # after the 5% discount is applied. Use the fill handle to copy the formula into the appropriate cells. 4. Determine the quiz averages by inserting a function in cell E31 that calculates the average for the test scores in the range B31:031. Use the fill handle to copy the function over the appropriate cells. 5. Use an IF statement in F31 that inserts the word PASS if the quiz average is greater than 79 and inserts the word FAIL if the quiz average is not greater than 79. Copy the formula in cell F31 down into the range F32:F35. Open Sheet 2 In column D 1. Determine what a 5% bonus would be if the salesperson surpassed their quota. The total should include the actual sales and bonus. If the salesperson does not receive a bonus they will receive their actual sales amount. Use an absolute cell reference. Change to comma formatting and zero decimal places. In columne 1. Determine an additional 15% sales bonus to only those employees that surpassed a total of $130,000 from column D, otherwise show a blank. Only show the value of the bonus, do not add it back in. Use an absolute cell reference. Change to comma formatting, zero decimals. In column F 1. Create an IF statement in column F that will create a comment "Great Job", to those employees that surpassed their quota and a comment "Work Harder" to those that did not 4 E E F G H H 1 1 2 3 4 Capstan Marine Products Sales Department Salesperson Quota Actual Sales Bonus Bonus Allejandro $ 95,500.00 $ 1,03,295.00 Crispin 1,37,000.00 1,29,890.00 Frankel 1,24,000.00 1,33,255.00 Hiesmann 85,500.00 94,350.00 Jarvis 1,59,000.00 1,67,410.00 Littleman 1,10,500.00 1,09,980.00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Discount Price $ Product # C-2340 C-3215 C-4390 E-2306 E-3420 G-2312 G 4393 J-1203 J-3288 1-4594 M-2355 M-3129 M-4392 Sales Department Price Discount 1,250.00 695.00 475.00 225.00 520.00 2,150.00 2,450.00 755.00 455.00 1,050.00 890.00 645.00 475.00 20 21 22 23 24 25 26 27 Average Grade 28 29 30 31 32 33 34 35 36 37 38 39 Name Angelo Cunningham Elliot Kennedy Lipscomb Quiz 1 78 90 82 100 64 New Employee Orientation Quiz 2 Quiz 3 69 88 95 86 88 94 98 96 76 62 40 41 fx A 8 B E F G H Bonu s 15% Comments to Employee 1 2 3 Bonus alespersor Quota Actual Sale 5% Sam 85,500 1,05,295 Jerry 1,40,000 1,29,990 Jillian 1,25,000 2,33,255 Kelsey 95,500 93,350 Hayden 1,60,000 1,77,410 Cora 1,19,500 1,10,980 A 4 5 6 LO 7 8 9 59 15% 10 11 12 13 14 15 16 17 18 19 20
