80.2K

Verified Solution

Link Copied!

Background

Coastalina, a developing country on the coast of South America, has long-standing challenges concerning public health, particularly in urban areas. Compared to its peers, the country ranks poorly on several key public health outcomes, including infant mortality and the incidence of childhood diseases. The Gates Foundation is interested in supporting a new free clinic in the largest city of Coastalina. A condition of the foundation grant is that the government of Coastalina will provide additional funds in support of the volume of service provided by the clinic.

You are a financial analyst for an international nonprofit organization that has been approached by the Coastalina government to manage this health clinic. The board of directors instructs you to run the numbers to determine whether the money coming into the clinic from the Gates Foundation and Coastalina government is sufficient to recoup its operating costs. The clinic is anticipated to open in January 2023.

Clinic Budget Forecasts

1. The Gates Foundation will provide a $145,000 grant for clinic startup costs in 2023.
2. In the first year, the government of Coastalina is proposing to pay $12 for each client visit (including recurring visits).
3. The clinic will hire an executive director for $45,000 annually.
4. Facilities rental for the clinic is $12,000 per year, and utilities are $3,000 per year.
5. The clinic is staffed by doctors and nurses.
   1. A doctors salary is $50,000 per year. The clinic will always need to have two doctors.
   2. The size of the nursing staff is tied to the clinics service levels. It is expected that each nurse is able to see 240 patients per month. In other words, if on any given month the clinic has 240 patients or less, then it only needs one nurse. If the clinic has between 241 and 480 patients, the clinic would need two nurses. And so on.
   3. Nurses are paid $1,500 per month. You can only hire nurses for the full month (no partial salary).
6. The clinic will employ one full-time security guard year-round; the contract rate is $1,500 per month.
7. The nonprofit expects that there will be 800 clinic visits per month in January and February 2023. Monthly visits are anticipated to increase by 7 percent from February to March 2023 and will continue to grow by 7 percent per month until the end of 2023.
8. The nonprofit estimates that each patient visit will require $3 worth of medical supplies.

Forecasts Notes

1. The costs and revenues reported above correspond to different timeframes or are contingent on service levels. Read the instructions and forecasts carefully.
2. Keep in mind there is no such thing as a fractional patient visit. You should use the =ROUNDUP(number, num_digits) function to ensure patient visits are always reported in terms of whole numbers. More on how to use the roundup function here.

Deliverables

You have been asked by the Board of Directors to do several analyses in order to determine whether the proposed agreement will allow the clinic to cover its expenses. You must prepare the budget in an Excel spreadsheet with two tabs that contain the following:

Tab 1: A baseline 2023 budget for the clinic (assume the clinic's fiscal year is the calendar year, therefore it starts in January). The budget must include all relevant information about the clinics monthly visits, revenues, expenses, and the total surplus or deficit at the end of each month (January through December).

The budget must also include the total surplus or deficit for each month, as well as a column reporting the sum of each line item in the budget over the full year (i.e. the sum of all 12 months).

Tab 2: An analysis of how the 2023 budget would change if each nurse could see 280 patients per month rather than the 240 per month as assumed in the baseline budget.

Answer & Explanation Solved by verified expert

See Answer