compute profit margin using excel and explain the process

/in /by

Question 1

For 2017, QVC AMC reported patient services revenue of $210M, premium revenue of $42M, and bad debt of $1M. Additionally, QVC AMC paid salaries of $195M, paid for supplies of $33M, paid $6M for insurance, paid $9M in interest, and booked $8M in depreciation. Given this information, create an Excel workbook with three tabs in which you answer/solve the following (be sure to label tabs accordingly, e.g., your first tab could be “Part 1 Q1”):

1.What is QVC AMC’s total margin?

2.How much profit(loss) does QVC AMC generate for each dollar of revenues and income?

3.Would you characterize QVC AMC as profitable? What suggestions do you offer QVC AMC?

Question 2

QVC Ambulatory Surgery Center (ASC) constructed a new state-of-the-art surgery center to replace its old ASC. The for-profit ASC was completed on January 1, 2017; and QVC began booking depreciation immediately. The cost of the new facility was $6M with $5M attributed to construction and $1M in land costs. The facility is expected to have a 15-year useful life and the salvage value of the building is expected to be $500,000. In the Excel workbook you created for Part 1, create three more tabs in which you answer/solve the following (be sure to label tabs accordingly):

1.Calculate the annual depreciation expense using the straight-line method.

2.Assuming a 30% tax rate, how much did QVC AMC save in income taxes for the year ending December 31, 2017 as a result of the depreciation shield.

3.Does the depreciation shield result in cash or noncash savings for QVC AMC? Explain.

Question 3

The board of directors of QVC Academic Medical Center (AMC) decided to invest $425,000. QVC AMC will receive the following payments during Years 1-4: $100,000, $125,000, $150,000, and $175,000. In the Excel workbook you created for Part 1, create three more tabs in which you answer/solve the following (be sure to label tabs accordingly):

1.What is the expected rate of return if the opportunity cost rate is 8%?

2.What is the return on this investment measured in percentage terms?

3.Would this be considered a good investment? Why or why not?

Length: One Excel Workbook containing all eight tabs and 1-2 page summary for each part (1-2 paragraphs for each question). Total 3-6 pages for summary parts.

 
"Order a similar paper and get 100% plagiarism free, professional written paper now!"