Budgeting and financial analysis

Page 1 of 3

Assignment

A federal government in a developing country has decided to open a number of job training

centers. The centers will provide intensive training in basic jobs skills for unemployed citizens in

the larger cities across the country. Given many existing claims on the government’s budget, its

Ministry of Education is seeking to make this program as affordable as possible. They have

decided to begin this effort with a demonstration project that will help to refine their financial

plan. Future program expansion will follow variations on this original model:

1. The center will be open 20 days every month, for 8 hours per day. The current plan calls

for a January 1, 2022 opening with 120 trainees.

2. Space has been located in the start-up city and the building owner has agreed to rent it to

the program for US $450 per month. The Ministry must renovate the building and get it

equipped; the estimated cost is $12,000.

3. The Ministry has hired an administrator for the program who will be paid US $3,500 per

month. An assistant to the administrator will be also hired at US $2,500 per month.

4. Each training staff member will earn $14.50 per hour; the program will pay an additional

7 percent of staff salaries for health benefits. Assume that the health benefits will be paid

only to the training staff – administrator and assistant will not receive health benefits.

5. Each trainee will take one year to complete the training program. The center would like

to have a student-to staff ratio of no more than 6 to 1 in order to provide an intensive

learning environment. Projections indicate that the number of trainees will grow by 10

percent per month through December of 2022.

6. Finally, training staff will have the option of accepting housing in an apartment building near the training center. The program will contribute $75 toward the monthly rent cost for

the staff members choosing this option. The program will not pay any housing costs for

staff choosing to live elsewhere. Housing will not be available for administrators and

assume that half of the training staff members will avail this option.

7. Each trainee (student) will be provided with lunch during the day at the cost of $2.00 per trainee per day, and the cost of the supplies used by each trainee (student) averages

$2.50 per day.

8. The Ministry has negotiated a grant from a donor agency that will subsidize the program

at a rate of $375 per month per trainee. The only stipulation is that the center must

maintain a student-to-staff ratio of no more than 8 trainees for each staff member.

9. The center will also charge a nominal fee of $2.50 per day to those students who can

afford it. Estimates indicate that 50 percent of the trainees will be able to pay the fee.

10. The President of the government has authorized a start-up grant of $160,000 for the first

year of operation. However, he cannot guarantee that any funding from the government

treasury will be available in subsequent years.

11. This is just a one-year budget across 12 months, so you do not have to consider the time

value of money in calculations. Also, assume zero inflation.

12. Amortize the one-time revenues and expenses equally across 12 months (for example,

start-up grant and renovation costs).

Page 2 of 3

Deliverable One

1. You are a budget analyst in this country’s Ministry of Finance. Please prepare a budget

for the training program in an Excel spreadsheet based on the model developed by the

Minister of Education that is presented above. The budget should use parameters and

formulas to produce a well-designed and flexible spreadsheet.

2. You can assume a calendar year, January to December. Determine the revenues, expenditures and any surplus or deficit for each month, given the program parameters

recommended by the Ministry.

3. Deliverable one should be a complete spreadsheet analysis for the calendar year 2022 (12 months). You don’t have to create a budget for 2023 and thereafter.

Deliverable Two

This is a demonstration project, and the Minister is aware their original plan may be overly

ambitious. Part of your job is to find ways to make this program work. Here are some

possible changes that could be considered (you may suggest other proposals):

• What happens if you change the trainee-staff ratio to the maximum allowed by the grant?

• What happens if you cap enrollment growth to 5 percent per month for the entire year?

• Is it feasible to open the center for less time or reduce per day work hours?

Remember that the goal is a sustainable program (beyond the first year) – most likely without a

start-up grant in future years. Present your plan for this project. As a last option, you can ask for

an increase in the Ministry’s per trainee subsidy from the current amount of $375. Deliverable

two should be a complete revision of deliverable one that applies your recommendations. Be

innovative in your recommendations and show the numbers behind your proposal. You

must also provide a written explanation in at least 2-3 paragraphs in the form of a cover letter

that makes a case for your recommendations. You can place your cover letter in a text box on the

first tab of the spreadsheet. You will submit your explanations, analysis, and assumptions in

textboxes and several tabs within one excel file.

Some Spreadsheet Features to Consider for this Assignment

There are many ways to make your spreadsheet more useful for decision-makers and to make life

easier for the analyst. Consider the following features when you develop your spreadsheet.

Use defined parameters, rather than building a number transitory factors into complex formulas. Parameters are budgetary assumptions that might be subject to change: salaries,

rent, working hours, renovation costs, student-teacher ratios, etc.

You can place key assumptions on a separate sheet or at the top or bottom of each tab. You might imagine yourself reviewing the program budget with the Minister and trying

out various budget scenarios by changing parameters.

Page 3 of 3

Remember to round up in any calculations involving people. Fractions of a dollar make

sense, not people! You can use Excel’s ROUND/ROUNDUP functions to round your

numbers.

Use the dollar sign ($) before all the monetary values in your spreadsheet using the

accounting notation in excel.

Do not calculate the monthly growth in parameters manually, use Excel’s Click and Drag

features to copy formulas.

Below is a very basic template of a budget sheet that might help in conceptualizing the

layout. However, you do not need to follow this template if you have better ideas. Be

creative and use the skills you have learnt from Dennis Taylor’s online excel training.

On the flip side, do not introduce (or overuse) techniques that may not be required just

because you have learned them.

A very basic template of a budget sheet

Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 Total

Expense 1 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 12.00$

Expense 2 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 12.00$

Expense 3 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 12.00$

-$

-$

Total Expenses 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 36.00$

Revenue 1 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$

Revenue 2 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$

Revenue 3 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$ 1.00$

Total Revenue 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$ 3.00$

Net Surplus/Deficit

Parameter 1 (No. of Trainees) 1 1 1 1 1 1 1 1 1 1 1 1

Parameter 2 (No. of Staff) 1 1 1 1 1 1 1 1 1 1 1 1