50.1K

Verified Solution

Link Copied!

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 doctors 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 & Explanation Solved by verified expert

See Answer