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 2022.
Clinic Budget Forecasts
The Gates Foundation will provide a $138,000 grant for clinic startup costs in 2022.
In the first year, the government of Coastalina is proposing to pay $10 for each client visit (including recurring visits).
The clinic will hire an executive director for $40,000 annually.
Facilities rental for the clinic is $12,000 per year, and utilities are $3,000 per year.
The clinic is staffed by doctors and nurses.
A doctor’s salary is $50,000 per year. The clinic will always need to have two doctors.
The size of the nursing staff is tied to the clinic’s 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.
Nurses are paid $1,500 per month. You can only hire nurses for the full month (no partial salary).
The clinic will employ one full-time security guard year-round; the contract rate is $1,500 per month.
The nonprofit expects that there will be 800 clinic visits per month in January and February 2022. Monthly visits are anticipated to increase by 7 percent from
February to March 2022 and will continue to grow by 7 percent per month until the end of 2022.
The nonprofit estimates that each patient visit will require $3 worth of medical supplies.
Forecasts Notes
The costs and revenues reported above correspond to different timeframes or are contingent on service levels. Read the instructions and forecasts carefully.
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 (Links to an external site.).
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 2022 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 clinic’s monthly visits, revenues, expenses, and the total surplus or deficit at the end of each month (January through
December). Costs should be split between fixed, variable, and step.
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 2022 budget would change if each nurse could see 280 patients per month rather than the 240 per month as assumed in the
baseline budget.
Grading Notes
1. The two tabs of your spreadsheet are each valued as follows:
Baseline 2022 budget (Tab 1) – 70 points
14 patients per day scenario (Tab 2) – 30 points
For each major error, you will be penalized 10 points. Major errors are those that either have a significant impact on the clinic’s bottom line, or that denote
very bad judgment. Minor errors or typos will be assessed as -5 points each.
2. Any calculation in your budget should be completed using Excel formulas rather than input manually. For example, the annual column should reflect the
sum of the 12 months for each line item using =SUM function. You are more likely to make errors through manual input rather than using Excel’s functions. A
minor error is also more likely to be counted as a major error if your instructor is unable to trace your calculations within your spreadsheet.