Excel assignment | Information Systems homework help

Get Custom Essay on Excel assignment | Information Systems homework help

Let Our Team of Pofessional Writers Take Care of Your Paper.


  

1. You are an office assistant for the Danbury Drop-In Clinic, a walk-in healthcare clinic in Danbury, Connecticut. Daniel Boyette, the owner of the clinic, wants to expand by adding other clinic locations in Danbury. Whether he leases new space or merges with another clinic, he needs a loan to cover the cost of the clinics. He has asked for your help updating the workbook he created to analyze the loan information and forecast sales.

Switch to the Loan Scenarios worksheet, and then calculate the monthly payment for the Add 1 Location scenario as follows:

a. In cell D10, enter a formula using the PMT function to calculate the monthly payment for a loan.

b. Use the inputs listed under the Add 1 Location loan scenario in cells D4, D6, and D8. (Hint: The result will be displayed as a negative number to reflect the negative cash flow of a loan payment.)

2. Calculate the monthly interest rate for the Add 2 Locations scenario as follows: 

a. In cell E6, enter a formula using the RATE function to calculate the monthly interest rate for a loan.

b. Use the inputs listed under the Add 2 Locations loan scenario in cells E8, E10, and E4. (Hint: Assume the present value of the loan is the loan amount shown in cell E4.)

3. Calculate the loan amount for the Add 3 Locations scenario as follows:

a. In cell F4, enter a formula using the PV function to calculate the loan amount.

b. Use the inputs listed under the Add 3 Locations loan scenario in cells F6, F8, and F10.

4. Calculate the number of months Daniel needs to pay back a loan for an existing clinic as follows: 

a. In cell G8, enter a formula using the NPER function to calculate how many months it would take to pay back a $450,000 loan.

b. Use the inputs listed under the Merger loan scenario in cells G6, G10, and G4.

5. Switch to the Amortization worksheet. Calculate the cumulative interest for a loan for one new clinic location as follows:

a. In cell C16, enter a formula using the CUMIPMT function to calculate the cumulative interest paid on the loan after the first year (payment 1 in cell C14 through payment 12 in cell C15) when the payments are made at the end of the period. Use 0 as the type argument in your formula.

b. Use absolute references for the rate, nper, and pv arguments.

c. Use relative references for the start and end arguments. 

d. Copy the formula from cell C16 to the range D16:G16 to calculate the interest paid in Years 2–5.

6. Calculate the cumulative principal for a loan for one new clinic location as follows:

a. In cell C17, enter a formula using the CUMPRINC function to calculate the cumulative principal paid in the first year (payment 1 in cell C14 through payment 12 in cell C15) when the payments are made at the end of the period. Use 0 as the type argument in your formula.

b. Use absolute references for the rate, nper, and pv arguments.

c. Use relative references for the start and end arguments. 

d. Copy the formula from cell C17 to the range D17:G17 to calculate the principal paid in Years 2–5.

7. In cell H17, use the Error Checking command to identify the error in the cell, and then correct the error. (Hint: The formula in the cell should calculate the total values in C17:G17 using the SUM function.)

8. Calculate the principal amounts in the loan amortization schedule as follows:

a. In cell E22, enter a formula using the PPMT function to determine the amount of the first loan payment devoted to principal. 

b. Use absolute references only for the rate, nper, and pv arguments.

c. Use cell A22 as the current period. (Hint: The period is based on a monthly payment schedule.)

d. Copy the formula from cell E22 to the range E23:E81 to calculate the principal paid in Periods 2–60.

9. Calculate the interest amounts in the loan amortization schedule as follows: 

a. In cell F22, enter a formula using the IPMT function to determine the amount of the first loan payment devoted to interest. 

b. Use absolute references only for the rate, nper, and pv arguments.

c. Use cell A22 as the current period. (Hint: The period is based on a monthly payment schedule.) 

d. Copy the formula from cell F22 to range F23:F81 to calculate the principal paid in Periods 2–60.

10. Switch to the Depreciation worksheet. Calculate the annual straight-line depreciation for one new clinic location (the most likely scenario) as follows:

a. In cell C10, enter a formula using the SLN function to calculate the straight-line depreciation for the clinic during its first year of operation.

b. Use the values in cells D4, D5, and D6 for the arguments. 

c. Use absolute references for the cost, salvage, and life arguments in the SLN formula. 

d. Copy the formula from cell C10 to the range D10:I10 to calculate the yearly straight-line depreciation in Years 2–7.

11. Calculate the annual declining balance depreciation for one new clinic location as follows:

a. In cell C17, enter a formula using the DB function to calculate the declining balance depreciation for the new clinic during its first year of operation.

b. Use the values in cells D4, D5, and D6 for the arguments.

c. Use the value in cell C16 as the current period. 

d. Use absolute references only for the cost, salvage, and life arguments in the DB formula. 

e. Copy the formula from cell C17 to the range D17:I17 to calculate the yearly declining balance depreciation in Years 2–7.

12. Correct the errors on the Depreciation worksheet as follows:

a. Determine the error in cell D18 by using the Trace Precedent and Trace Dependent arrows. Correct the error so that the formula in cell D18 calculates the cumulative depreciation of the clinic by adding the Cumulative Depreciation value in Year 1 to the Yearly Depreciation value in Year 2.

b. Copy the corrected formula in cell D18 to the range E18:I18, and then remove any arrows from the worksheet.

13. Switch to the Income Statement worksheet. For the Add 1 Location scenario, project the income from Insurance for 2020-2022 (cells D4:F4) using a Growth Trend interpolation. (Hint: Select the range C4:G4 before filling this series with values.)

14. Project the income from Other reimbursements for 2020-2022 (cells D6:F6) using a Linear Trend interpolation. (Hint: Select the range C6:G6 before filling this series with values.)

15. Project expenses as follows:

a. Project the expenses for Payroll for 2020-2023 (cells D12:G12) using a Growth trend extrapolation.

b. Use a step value of 1.05. (Hint: When extrapolating values, the Trend check box in the Series Dialog Box should not be checked.)

c. Do not set a stop value for the series. (Hint: Select the range C12:G12 before filling this series with values.) 

16. Switch to the Investment worksheet. Calculate the present value of an investment in one new clinic location as follows:

a. In cell C14, enter a formula that uses the NPV function to calculate the Present Value of the Add 1 Location investment.

b. Use the value in cell C13 as the desired rate of return.

c. Use the range C6:C11 as the returns paid to investors. (Hint: If a Formula Omits Adjacent Cell error warning appears, ignore it.)

17. In cell C15, enter a formula without using a function that calculates the Net Present Value by adding the Present Value of the Add 1 Location investment (calculated in cell C14) to the value of the initial investment (in cell C5).

18. Calculate the internal rate of return as follows:

a. In cell C16, enter a formula that uses the IRR function to calculate the internal rate of return of the Add 1 Location investment.

b. Use the range C5:C11 as the returns paid to investors.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Loan Scenarios Worksheet



Source link

Calculate the price for this paper
Pages (550 words)
Approximate price: -

Try it now!

Calculate the price for this paper

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Best Quality Essays has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.

Essays

Essay Writing Services

At Best Quality Essays, we prioritize on all aspects that bring about a good grade such as impeccable grammar, proper structure, zero-plagiarism and conformance to guidelines. Our experienced team of writers will help you completed your essays and other assignments.

Admissions

Admission and Business Papers

Be assured that you’ll definitely get accepted to the Master’s level program at any university once you enter all the details in the order form. We won’t leave you here; we will also help you secure a good position in your aspired workplace by creating an outstanding resume or portfolio once you place an order.

Editing

Editing and Proofreading

Our skilled editing and writing team will help you restructure you paper, paraphrase, correct grammar and replace plagiarized sections on your paper just on time. The service is geared toward eliminating any mistakes and rather enhancing better quality.

Coursework

Technical papers

We have writers in almost all fields including the most technical fields. You don’t have to worry about the complexity of your paper. Simply enter as much details as possible in the place order section.