just show how to solve it step by step. It is an excel project. What's the formula i can use to solve question 14,15,16,19 & 20

1. Charlie is a salesman at Pick Up Motors car dealership. He is performing a detailed analysis of his sales for the first half of 2018, including representing his sales and commissions with graphs.

Switch to the 2018Jan-Jun Sales worksheet, and then remove the panes from the worksheet. (Hint: Deselect the Split option from the View tab.)

2. Freeze rows 1 and 2. (Hint: Select cell A3 before freezing panes.)

3. Change the width of column A to 31.00. (Hint: Do not use AutoFit.)

4. Select the range B4:D4 and, using the Fill Handle, fill the range E4:G4 with consecutive months.

5. In cell H5, create a Column Sparklinebased on the data in the range B5:G5. Copy the Sparkline you created in cell H5 to the range H6:H9.

6. Using the Format Painter, copy the formatting from the range G5:G9 to the range H5:H9.

7. Copy the contents of the range B4:G4 to the range B12:G12.

8. Apply the Percentagenumber format with zero decimal places to the range B13:G13 and the nonadjacent cell B33.

9. Select the merged range A15:A27, and then rotate the cell contents to 90 degrees (Hint: The text should read from bottom to top).

10. In the stacked column chart (with the title Monthly Sales per Vehicle Type), add Monthas the primary horizontal axis title and Sales Amountas the primary vertical axis title.

11. In the stacked column chart (with the title Monthly Sales per Vehicle Type), change the number format of the vertical axis to Accountingstyle with zerodecimal places.

12. In the 3-D pie chart (titled Monthly Sales Contribution to Six-Month Sales Total) change the chart style to Style 1 (1^stcolumn, 1^strow of the Chart Styles palette).

13. Add data labels to the 3-D pie chart using the Outside Endstyle.

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 the IFfunction to check whether the value of cell B9 is greater than400000.

If this condition is true, the function should multiply cell B9by 2.5%to determine Charlies commission. (Hint: For the if true value, use B9*0.025.)

If this condition is false, the function should multiply the value in cell B9by 1.5%to determine Charlies commission. (Hint: For the if false value, use B9*0.015.)

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

15. After deductions, Charlies net monthly commission is only 85% of his gross monthly commission.

In cell B32, create a formula that multiplies the value of cell B31 (Charlies gross monthly commission) by the value of cell B33 (Charlies net commission percentage). Use an absolutereference to cell B33 (because the net commission percentage wont change) and a relativereference to cell B31.

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

16. In cell H31, create a Line Sparklinebased on the data in the range B31: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 to Column.

Change the Sparkline style to Sparkline 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 contents once.

18. Charlies 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 use Goal Seek to determine what value of cell B36 (Estimated 6 Month Sales Total)is necessary to set the value of cell B39 to 60000.Leave the result of the Goal Seek analysis as the new value of cell B36.

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

20. Use the Recommended Charts tool to create a Clustered Columnchart based on the range A30:G32 with the following options:

Move the chart to its own chart sheet, and then enterCommissions 2018as the name of the new chart sheet. If necessary, reposition the worksheet so it is located before the 2018 Jan-Jun Sales worksheet.

Use Sales Commissions - January to June 2018as the chart title.

In the clustered column chart add Monthas the primary horizontal axis title and Commissionas the primary vertical axis title.

Your workbook should look like the Final Figures on the following pages. (The value in cell B36 generated by the Goal Seek analysis has been intentionally blurred in Final Figure 1.) Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

PICK UP MOTORS

Sales Report - January to June 2018

Sales

Vehicle Type

January

February

March

Trend

Total

Car

$142,000.00

$147,600.00

$153,280.00

$155,790.00

$78,960.00

$186,985.00

$864,615.00

Suv

$164,500.00

$135,050.00

$142,340.00

$98,756.00

$72,300.00

$155,420.00

$768,366.00

Truck

$97,685.00

$98,600.00

$99,402.00

$101,540.00

$102,786.00

$105,200.00

$605,213.00

Other

$32,547.00

$25,600.00

$27,890.00

$26,450.00

$9,620.00

$31,248.00

$153,355.00

Total

$436,732.00

$406,850.00

$422,912.00

$382,536.00

$263,666.00

$478,853.00

$2,391,549.00

Monthly Sales Contribution to Six-Month Sales Total - January to June 2018

Sales

Percentage

0.18

0.17

0.18

0.16

0.11

0.20

Sales Charts January to June 2018

Commission - January to June 2018

Monthly Commission

January

February

March

April

May

June

Trend

Total

Gross Amount

$-

Net Amount

$-

Net Commission %

0.85

Commission Estimate - July to Dec 2018

Estimated 6 Months Sales Total

Estimated Average Commission Rate

2.0%

Estimated Gross Commission

$-

Estimated Net Commission

$-

Report Date: