Question

Please provide a step by step solution

Instructions:

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. Create a worksheet to include the following information from the visits to the doctor.
   1. Input the Date of Visit using the date function.
   2. 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

3. Your friend has a co-payment of $25.00 and the insurance will pay 80% of the remaining cost. In Column D create a formula that displays how much the insurance company will pay. In Column E create a formula that displays the amount your friend will have to pay.
4. 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 create a formula that calculates the insurance response date.
5. 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.
6. In cell A14, type the word Average. In cell C14, input a formula that calculates the Average Cost of Visit.
7. Format the spreadsheet to make it easy to read and visually attractive. Add a footer that includes your name.
8. Name the worksheet Doctor Visits.

PART B

1. Copy the worksheet in the same workbook. Rename the second worksheet Bank Loan.
2. Sort the worksheet alphabetically by Doctor and then by Cost of Visit.
3. 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 create 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

Answer 1

Solution of Part A - Since there is no way to input a worksheet with formula, I am pasting it as screen shot.

Date of visit	Doctor	Cost of visit	Insurance co to pay	Friend to pay	Response date of insurance
6/6/2019	Goodman	$       113.00	$                         90.00	$            23.00	7/9/2019
6/8/2019	Goodman	$       145.00	$                       116.00	$            29.00	7/11/2019
6/10/2019	Loman	$       120.00	$                         96.00	$            24.00	7/13/2019
6/13/2019	Goodman	$       110.00	$                         88.00	$            22.00	7/16/2019
6/15/2019	Loman	$       140.00	$                       112.00	$            28.00	7/18/2019
6/17/2019	Loman	$       175.00	$                       140.00	$            35.00	7/20/2019
6/21/2019	Loman	$       135.00	$                       108.00	$            27.00	7/24/2019
6/23/2019	Walker	$       215.00	$                       172.00	$            43.00	7/26/2019
6/25/2019	Walker	$       235.00	$                       188.00	$            47.00	7/28/2019
6/27/2019	Loman	$       120.00	$                         96.00	$            24.00	7/30/2019
6/28/2019	Goodman	$       105.00	$                         84.00	$            21.00	7/31/2019
Total		$   1,613.00	$                   1,290.00	$          323.00
Average		$       146.64	$                       117.27	$            29.36

Formula used in the above solution :

Date of visit	Doctor	Cost of visit	Insurance co to pay	Friend to pay	Response date of insurance
=DATE(2019,6,6)	Goodman	113	=ROUND(C2*80%,0)	=C2-D2	=((A2)+3)+30
=A2+2	Goodman	145	=ROUND(C3*80%,0)	=C3-D3	=((A3)+3)+30
=A3+2	Loman	120	=ROUND(C4*80%,0)	=C4-D4	=((A4)+3)+30
=A4+3	Goodman	110	=ROUND(C5*80%,0)	=C5-D5	=((A5)+3)+30
=A5+2	Loman	140	=ROUND(C6*80%,0)	=C6-D6	=((A6)+3)+30
=A6+2	Loman	175	=ROUND(C7*80%,0)	=C7-D7	=((A7)+3)+30
=A7+4	Loman	135	=ROUND(C8*80%,0)	=C8-D8	=((A8)+3)+30
=A8+2	Walker	215	=ROUND(C9*80%,0)	=C9-D9	=((A9)+3)+30
=A9+2	Walker	235	=ROUND(C10*80%,0)	=C10-D10	=((A10)+3)+30
=A10+2	Loman	120	=ROUND(C11*80%,0)	=C11-D11	=((A11)+3)+30
=A11+1	Goodman	105	=ROUND(C12*80%,0)	=C12-D12	=((A12)+3)+30
Total		=SUM(C2:C12)	=SUM(D2:D12)	=SUM(E2:E12)
Average		=AVERAGE(C2:C12)	=AVERAGE(D2:D12)	=AVERAGE(E2:E12)

Solution of Part B

Date of visit	Doctor	Cost of visit	Insurance co to pay	Friend to pay	Response date of insurance
6/6/2019	Goodman	$       113.00	$                         90.00	$            23.00	7/9/2019
6/8/2019	Goodman	$       145.00	$                       116.00	$            29.00	7/11/2019
6/13/2019	Goodman	$       110.00	$                         88.00	$            22.00	7/16/2019
6/28/2019	Goodman	$       105.00	$                         84.00	$            21.00	7/31/2019
6/10/2019	Loman	$       120.00	$                         96.00	$            24.00	7/13/2019
6/15/2019	Loman	$       140.00	$                       112.00	$            28.00	7/18/2019
6/17/2019	Loman	$       175.00	$                       140.00	$            35.00	7/20/2019
6/21/2019	Loman	$       135.00	$                       108.00	$            27.00	7/24/2019
6/27/2019	Loman	$       120.00	$                         96.00	$            24.00	7/30/2019
6/23/2019	Walker	$       215.00	$                       172.00	$            43.00	7/26/2019
6/25/2019	Walker	$       235.00	$                       188.00	$            47.00	7/28/2019
Total		$   1,613.00	$                   1,290.00	$          323.00
Average		$       146.64	$                       117.27	$            29.36
Bank Loan		$   1,013.00

Formula used in solution of part B :

Date of visit	Doctor	Cost of visit	Insurance co to pay	Friend to pay	Response date of insurance
43622	Goodman	113	=ROUND(C2*80%,0)	=C2-D2	=((A2)+3)+30
43624	Goodman	145	=ROUND(C3*80%,0)	=C3-D3	=((A3)+3)+30
43629	Goodman	110	=ROUND(C4*80%,0)	=C4-D4	=((A4)+3)+30
43644	Goodman	105	=ROUND(C5*80%,0)	=C5-D5	=((A5)+3)+30
43626	Loman	120	=ROUND(C6*80%,0)	=C6-D6	=((A6)+3)+30
43631	Loman	140	=ROUND(C7*80%,0)	=C7-D7	=((A7)+3)+30
43633	Loman	175	=ROUND(C8*80%,0)	=C8-D8	=((A8)+3)+30
43637	Loman	135	=ROUND(C9*80%,0)	=C9-D9	=((A9)+3)+30
43643	Loman	120	=ROUND(C10*80%,0)	=C10-D10	=((A10)+3)+30
43639	Walker	215	=ROUND(C11*80%,0)	=C11-D11	=((A11)+3)+30
43641	Walker	235	=ROUND(C12*80%,0)	=C12-D12	=((A12)+3)+30
Total		=SUM(C2:C12)	=SUM(D2:D12)	=SUM(E2:E12)
Average		=AVERAGE(C2:C12)	=AVERAGE(D2:D12)	=AVERAGE(E2:E12)
Bank Loan		=C13-600