14.Charlie wants to calculate his gross monthly sales commissions. The rule is that he receives a commission of 2.5% of his total monthly sales if he sold more than $400,000 worth of vehicles. Otherwise, he only gets 1.5% of his total monthly sales amount.

In cell B31, create a formula using theIFfunction to check whether the value of cellB9isgreater than400000.

If this condition is true, the function should multiply cellB9by2.5%to determine Charlie's commission. (Hint: For the if true value, useB9*0.025.)

If this condition is false, the function should multiply the value in cellB9by1.5%to determine Charlie's commission. (Hint: For the if false value, useB9*0.015.)

Copy the formula in B31 to the range C31:G31.

15.After deductions, Charlie's net monthly commission is only 85% of his gross monthly commission.

In cell B32, create a formula that multiplies the value of cellB31(Charlie's gross monthly commission) by the value of cellB33(Charlie's net commission percentage). Use anabsolutereference to cell B33 (because the net commission percentage won't change) and arelativereference to cell B31.

Copy the formula in cell B32 to the range C32:G32.

16.In cell H31, create aLine Sparklinebased on the data in the rangeB31:G31. Copy the Sparkline you created in cell H31 to cell H32 without copying the formatting.

Update the Line Sparklines as described below:

Change the Sparkline type toColumn.

Change the Sparkline style toSparkline Style Dark #4(4th column, 5th row of the Sparkline Styles palette [Mac Hint: 7^thcolumn, 4^throw]). (Hint: Depending on your version of Office, the Sparkline style may appear as Black, Sparkline Style Dark #4.)

17.In the range A31:A32,increase the indent of the cell contentsonce.

18.Charlie's goal is to make a net commission of $60,000 for the second half of 2018 (July through December), using the estimate that his gross commission rate will be an average of 2% (cell B37) across the second half of the year.

Select cell B39, and then useGoal Seekto determine what value of cellB36(Estimated 6 Month Sales Total)is necessary to set the value of cellB39to60000.Leave the result of the Goal Seek analysis as the new value of cell B36.

19.In cell B40, insert a formula using theNOWfunction to enter the system current date into the cell, and then apply theShort Datenumber format (e.g., 3/15/2019).