CREATE AN EXCEL SPREADSHEET TO CALCULATE THE ANSWER TO THIS QUESTION. SUBMIT YOU
ID: 2538036 • Letter: C
Question
CREATE AN EXCEL SPREADSHEET TO CALCULATE THE ANSWER TO THIS QUESTION. SUBMIT YOUR ANSWER IN A SPREADSHEET IN QUESTION #2. DO NOT ENTER AN ANSWER HERE! You are considering opening a new urgent care facility and have the following projected data: Revenues (11,000 visits) $1,175,000 Salaries, wages, & benefits $350,000 Supplies 120,000 Rent 6,000 Depreciation 12,000 Repairs & maintenance 5,000 Assume that 75% of salaries/wages benefits and 100% of supplies are variable and that all other costs are fixed. IN AN EXCEL SPREADSHEET YOU SUBMIT FOR QUESTION 2, SHOW THE FOLLOWING CALCULATIONS a Construct the clinic's pro forma P&L; b. Calculate the number of visits needed to break even What number of visits is required to generate an after-tax profit of $250,000, given a 35% tax rate?Explanation / Answer
Req a. Performa Profit and Loss account Service revenue 1175000 Less: Variable ccost Salaries (350000*75%) 262500 Supplies 120000 Contribution 792500 Less: fixed cost Salaries (350000*25%) 87500 Rent 6000 Depreciation 12000 Repairs and maintenance 5000 Net Operating income 682000 Req B: Revenue for 11,000 visit: $ 1175,000 Contribution for 11000 visit: $792,500 Revenue per visit (1175,000/11000) = 106.82 Contribution per visit: (792500/11000) = $72.05 per visit Fixed cost: $110,500 Break even in terms of number of visit: Fixed cost / Contribution per visit ($110,500 /72.05) = 1534 visit Req C: Desired profits after tax: $ 250,000 Tax rate: 35% Desired profits before tax: 250000/60% = $416,667 Desired contribution= fixed cost+ desired profits (110,500+416,667) = $527,167 Number of visit for dsired profits: Desired contribution/ Contribution per visit ($527,167/72.05) = 7317 visits.