please provide a step by step solution excel

PART A

1. A friend has made several visits to the doctor in the past month because of a serious illness and has asked you to calculate what he will owe after the insurance company has paid its part of the bill. The insurance company usually takes about a month to process a claim and your friend is hoping you can help figure the amount right away so he can plan ahead.
2. Construct a worksheet to include the following information from the visits to the doctor.
3. Input the Date of Visit using the date function.

Format the Cost of Visit to Currency

A B C

1 Date of Visit Doctor Cost of Visit

2 6/6/2019 Goodman $113.00

3 6/8/2019 Goodman $145.00

4 6/10/2019 Loman $120.00

5 6/13/2019 Goodman $110.00

6 6/15/2019 Loman $140.00

7 6/17/2019 Loman $175.00

8 6/21/2019 Loman $135.00

9 6/22/2019 Walker $215.00

10 6/24/2019 Walker $235.00

11 6/27/2019 Loman $120.00

12 6/28/2019 Goodman $105.00

1.Your friend has a copayment of $25.00 and the insurance will pay 80% of the remaining cost. In Column D construct a formula that displays how much the insurance company will pay. In Column E construct a formula that displays the amount your friend will have to pay.

2.The insurance company is supposed to respond within 30 days of billing. The doctor's office has said that they will bill 3 days after the visit. In Column F construct a formula that calculates the insurance response date.

3.In cell A13, type the word Total. In cell C13 insert a formula that calculates the total cost of the visits. Copy this formula to cells D13 and E13.

4.In cell A14, type the word Average. In cell C14, input a formula that calculates the Average Cost of Visit.

5.Format the spreadsheet to make it easy to read and visually attractive. Add a footer that includes your name.

6.Name the worksheet Doctor Visits.

PART B

1.Copy the worksheet in the same workbook.Rename the second worksheet Bank Loan.

1. Sort the worksheet alphabetically by Doctor and then by Cost of Visit.
2. Since your friend already has some money saved, calculate whether or not he will need a loan if the total he has to pay is more than $600. In cell A15 type Bank Loan. In cell E15 construct an IF statement that will display the words No Loan if your friend does not need a loan and Yes Loan if your friend needs a loan.