Expert answer:I need an Excel expert to do a budget for my nonpr
Solved by verified expert:The company name I am using is Jars of Clay. https://www.jocatlanta.org/housing-program A small nonprofit helping homeless women and children. Staff 10 (Director, Assistant Director, HR representative, Kitchen Cook, 2 Social Workers, 2 Counselors, 1 Substance Abuse Counselor and 1 janitor) -Budget 200,000 a year. I only need one page for this budget. The cells on the excel spreadsheet must have equations. The teacher will look for this in each cell. Develop proposed budget (in Excel) for a nonprofit for 2017/2018The budget presentation must include (at least) a proposed budget for this year and the previous year’s actuals (2016 or 2017 depending on FYE) using an internal financial report to which you have access or audited financials. The project will be stronger if your proposed budget document contains both last year’s budget and actuals in addition to your proposal for this year’s budget. The budget must consist of the following five (5) parts/sections1) A summary budget (total org budget including all programs, administrative and development revenues and costs by natural classification) with budget totals compared to last year’s actuals (and budget if available)2) The budgeted details for at least one program (more if org has multiple programs) and administrative and fundraising costs (see pptx for examples of multi-column presentation examples3) Information showing how income was estimated/calculated with details (what specific sources in what amounts, Hint: look well at Budget Book chptr 15 for a template on how to report this info)4) Information showing how salaries and benefits were calculated and allocated (see ppt and excel allocation examples for reference)5) A narrative of no more than 2 pages explaining the budget (highlights), connecting it to the organization’s mission and explaining how general costs were allocated (what methodology was used: see ppt for ways/methods of expense allocation)
template.pdf
week_3_evaluating_budgets__8_.xlsx
allocations__1_.pptx
budget_presentation___adjustments__1_.pptx
Unformatted Attachment Preview
SOS
Support and Revenue
Grants
Contracts
Contributions
Board Support
Other
Investment income
Total Support and Revenue
Expenses
Salaries & Benefits
Contracted Services
Repairs & Maintenance
Depreciation
Professional fees
Insurance
Utilities
Printing
Supplies
Travel
Rent
Other
Net Income
Proposed
Budget
FY 2017
Actual
FY 2016
Budget
2016
31,000
57,000
18,000
5,000
1,000
55,000
167,000
3,445
50,245
9,765
3,208
840
53,204
120,707
30,000
55,000
17,000
5,000
500
50,000
157,500
63,000
59,798
60,000
17,000
15,854
16,000
16,000
9,824
15,000
11,000
10,225
11,000
21,000
18,542
20,000
17,000
22,045
16,000
12,000
10,321
11,000
4,000
3,050
3,500
8,000
7,104
7,500
3,000
1,120
2,500
6,000
5,560
6,000
122
178,000 163,565 168,500
(11,000) (42,858) (11,000)
C:Userscloudconvertserverfiles1173163s8YzpgrNjcfohBSn3GkR20171112025311week_3
_evaluating_budgets__8_.xlsx
YCS
Proposed Budget
2017
Revenue and Support
Tuition, net of aid
Summer Program Income
Extended Day Income
Contributions
Special Events
Investment Income
Grants
Fee Income
Rental Income
Miscellaneous Income
Total revenue and support
Expenses
Salaries & taxes
Benefits
Advertising
Bad debt expense
Class activities
Computer
Depreciation
Insurance
Interest/fees
Printing/Postage
Professional services
Equipment rental/repair/maint.
Supplies
Utilities
Misc
Total Expenses
Net
$
Actual
2016
Budget
2016
$
1,665,465 $
144,000
63,000
69,000
40,000
20,000
13,500
8,500
5,000
2,000
2,030,465 $
1,561,211
$1,764,000
142,000
150,000
52,000
50,000
53,674
70,000
8,000
30,000
28,558
25,000
5,000
8,000
7,500
6,931
7,000
18,793
5,000
1,879,167 $ 2,113,500
$
$
1,267,870 $
256,412
40,000
20,000
47,000
38,259
25,000
40,600
3000
18,924
80,400
100,000
43,000
46,000
4,000
2,030,465 $
$
1,255,869 $
125,653
28,256
136,089
36,363
31,055
64,415
38,027
9,229
17,172
102,382
133,265
49,136
45,525
49,528
2,121,964 $
(242,797) $
1,280,000
192,000
28,000
30,000
40,000
35,000
65,000
38,000
3,000
15,000
92,500
150,000
50,000
45,000
50,000
2,113,500
–
C:Userscloudconvertserverfiles1173163s8YzpgrNjcfohBSn3GkR20171112025311week_3_evaluating_budgets__8_.xlsxYCS
MIO
Proposed
Budget
FY 2017
Actual
FY 2016
Budget
2016
Support and Revenue
Grants
Contracts
Contributions
Board Support
Fee income
Investment income
Total Support and Revenue
370,000
1,100,000
700,000
300,000
1,000,000
1,900,000
5,370,000
306,768
1,000,000
665,564
298,842
800,156
1,870,376
4,941,706
370,000
1,100,000
700,000
300,000
1,050,000
2,300,000
5,820,000
2,480,000
56,000
60,000
1,400,000
476,000
208,000
325,000
30,000
160,000
150,000
25,000
5,370,000
2,405,109
54,728
61,451
1,385,362
462,065
201,927
322,938
26,770
158,808
159,754
29,600
5,268,512
2,500,000
70,000
80,000
1,650,000
650,000
200,000
250,000
35,000
210,000
150,000
25,000
5,820,000
Expenses
Salaries & Benefits
Training & travel
Volunter expenses
Event costs
Contracted Services
Communications
Building expenses
Insurance
Repairs & Maintenance
Depreciation
Miscellaneous
Total Expenses
Net income
–
(326,806)
–
C:Userscloudconvertserverfiles1173163s8YzpgrNjcfohBSn3GkR20171112025311week_3_e
valuating_budgets__8_.xlsxMIO
Allocation:
What goes where
and why
Nonprofit Financial Management
Starting with the big Why
Matching Principle – one of the foundations of
finance (the matching principle) stipulates that
income be matched – both in time and purpose to the expenses generated to earn it. (This
principle is, unfortunately, contradicted by FASB
116 which stipulates that revenue be booked
when either the cash or the promise to give is
received, regardless of when the money is
actually going to be spent.) Keep the matching
principle in mind when considering allocations
Income allocation
Income allocation is usually done for internal
readers of financial statements – boards and
donors. It less frequently required by external
readers. It is not required for an audit or for the
990.
Income allocation involves assigning
unrestricted income to programs and activities
(including G&A and Fundraising).
Income Allocation
The example above shows one way of allocating income. Grants and contracts are usually restricted to
specific programs with a set amount allowed for administrative costs, thus that income has been applied to
the programs stipulated, with the agreed upon administrative % applied to admin costs.
Fee income, special events, donations and investment income are unrestricted in this example. Fee income
is usually assigned to the program producing the fee because readers want to know if the fees are covering
the expenses (matching principle).
Special event income, donations and investment income could all be attributed to administration or divided
among programs, G&A, and Fundraising in any way. The example shows special event income and donations
divided among the programs with a lesser amount for administration so that donors can see their donations
supporting programs. This is not required, but is often done. In this example we also show that investment
income is used to generate capital necessary to cover fundraising expenses. One could argue that special
event or other “contributed income” should be used to cover fundraising costs as those costs are directly
related to the income generated by the fundraising activities.
Income Allocation
The example above shows an alternative way to allocate income. The grants and contracts are
usually restricted to specific programs with a set amount allowed for administrative costs so
there is no change in how that revenue will be used.
Fee income, special events, donations and investment income are unrestricted in this example.
Fee income is usually assigned to the program producing the fee because readers want to
know if the fees are covering the expenses. This is not a requirement, but is a logical method as
it follows the matching principle.
Special event income, donations and investment income are all attributed to administration
and fundraising in this example. This is perfectly acceptable and is frequently done to measure
how much of the restricted revenue is covering the direct costs of programs.
Income Allocation
Here is a third alternative. The restricted grants and contracts have not been
changed but all other income sources are being used to support administration. This
might be done to confirm that the restricted money is covering direct costs.
Expenses
Unlike income allocation, which is optional, the allocation of
indirect expenses is essential for the Statement of Functional
Expenses – a required financial report for nonprofit audits and
for the Form 990.
The Statement of Functional Expenses divides expenses into
three, functional categories: Program, Fundraising and
Administration (G&A). By dividing expenses this way, it is easy
for the reader of the statements to calculate how much the
nonprofit spends in each category. Often this is (erroneously)
used as a quick measure of organizational efficiency.
Program Expenses
• According to the Better Business Bureau Standards for Charitable
Accountability, a charity should “Spend at least 65% of its total expenses
on program activities”
• Of the nonprofits that receive a positive rating from Charity Navigator, 9
out of 10 spend at least 65% of their total expenses on programs and 7
out of 10 spend at least 75% . The website further states, “We believe
that those spending less than a third of their budget on program
expenses are simply not living up to their missions.”
• These are not necessarily the best authorities to dictate how nonprofits
spend their resources. However, if we agree that we are in the business
of solving social problems with the public’s money, we should think long
and hard if we are spending less that half of our resources on direct
programs and services.
• Do not, however fudge your numbers simply to fit into this rubric, rather
state the truth about your numbers and then do some serious selfexamination if something doesn’t feel right, because certainly your
donors will!
How does one allocate expenses?
What methods are used determine whether costs are programmatic,
fundraising or administrative?
Direct cost allocation: this method applies the direct costs of a functional area to that
function. For example, the cost of medicine is a direct cost for the programs (health and
wellness) of a nonprofit hospital, so they are applied to the programmatic function.
Management estimate: using this method, mgmt. determines whether, or how much
of, an expense is related to which of the three categories. This determination must not be
arbitrary, but must be supported by managements experience and judgement.
Time estimation: using this method, management, in consultation with supervisors
and staff, estimate how much time an employee spends working on each of the three functional
areas throughout the year and allocates a % of each employee’s time (and thus pay and benefits)
to each functional area. (this is used primarily to allocate salaries).
Space allocation: for organizations with physical space, that space is measured and
expenses are applied against the % each of the functional areas take up of the whole space (this
is used primarily to apply utility and other facilities costs).
Personnel Costs
Allocation of personnel costs starts with an analysis of where employees spend their time.
Name
Jay
Joan
Sam
Simon
Allyce
Bill
Position
Exec. Director
ASP/ Camp Coordinator
Clerical
Adult Ed/Community Center
Maintenance
Maint. Asst.
Total:
% of Business
Adullt Ed
4%
0%
10%
45%
25%
40%
124%
PROGRAM
Community Center After School
8%
8%
0%
80%
10%
10%
45%
0%
25%
25%
30%
10%
118%
133%
71.7%
Summer Day
10%
15%
15%
0%
15%
55%
G&A
50%
5%
45%
5%
5%
10%
120%
20.0%
Fundraising Totals
20%
100%
0%
100%
10%
100%
5%
100%
5%
100%
10%
100%
50%
600%
8.3%
Maintenance
30% Afterschool clean up
Clerical
Allocation based on time spent
Adult Ed teachers – 3 – Independent Contractors
After school program (ASP) hires hourly staff -up to four depending on enrollment
Summer camp – hires up to four teens and four adults on an hourly basis
Assist Maint – works evenings & weekends , paid hourly
When you examine the time allocation chart, you see that 50% of the ED’s time is
spent on administration, 20% on fundraising and 30% on programs. Frequently,
Exec. Directors will neglect to indicate how much time they spend on programs but
the supervision of program staff is time spent on programs. The same is true for
clerical and accounting staff. Time spent preparing reports on a specific program
can be charged to that program. Likewise, program staff do spend time fundraising
and doing administrative duties, thus their time is not allocated only to programs.
Personnel Costs
Program-Based Budget
Indirect
Total
Program 1
Program 2
Program 3
Program 4
Adult Ed
Comm Ctr
After School
Summer Camp
Fundraising
Admin
Personnel Expenses
Salaries
$
307,260
$
49,622
$
35,788
$
84,500
$
68,050
$
19,100
$ 50,200
Payroll Taxes (7.65%)
23,505
3,796
2,738
6,464
5,206
1,461
3,840
Health Benefits (only over 40%)
43,688
7,530
7,158
10,810
4,330
3,820
10,040
Other Benefits (4%)
12,290
1,985
1,432
3,380
2,722
764
2,008
Total Personnel Expenses
$
386,744
$
62,933
$
47,115
$
105,154
$
80,308
$
25,145
$ 66,088
After staff time is allocated appropriately to the programs, fundraising and
administration, payroll taxes and benefits should also be allocated to those areas.
This needs to be done when the budget is created so the Executive Director and the
Board (or the Finance Committee) can evaluate the ratio of anticipated program
expenses to overall expenses.
Once actual costs are incurred, the finance staff needs to charge the expenses as
per the budget.
Non-Personnel Costs
In considering non-personnel costs, categorizing expenses as direct or indirect becomes significant.
The first task is to decide which expenses are direct costs for each program
In the example above, Program Supplies and Program Activities are direct program costs. Postage is
a shared cost but with a metered postage machine the costs can be directly charged to activity using
the machine (copy costs and printing can also be done this way with current copier technology).
The audit costs and depreciation are charged only to administration, though you could allocate
depreciation cost based on space usage. In fact, I would recommend this as depreciation is simply
the cost of PPE (plant, property and equipment) expensed over time rather than all at once. The
whole point of depreciation is the matching principle, matching the costs of and asset to the time
period in, and purpose for which you use them, so it makes sense to book that portion of
depreciation incurred by programmatic use of fixed assets during any given period.
If one program required a federal audit, then the additional costs for a federal audit could be
charged to that program.
Direct Costs Analysis
See the next slide for an explanation
Direct Cost Analysis
In a direct cost analysis, you move all unrestricted revenue to
G&A and fundraising to see exactly how much dedicated
revenue comes into each program. Then the expenses are
charged out to the programs (in this scenario the allocation is
based on the personnel costs for each program divided by total
personnel costs) and the expenses are subtracted from the
restricted and direct income. This shows you that the
Community Center and the Summer Camp would operate at a
deficit if there was no unrestricted income. If unrestricted
income did not arrive, then the Board might consider closing the
Summer Camp.
Allocations
Then the indirect costs, or costs shared by all the programs,
fundraising and administration need to be spread among the
programs (allocated).
There are many ways to allocate costs:
– Salary Weighted – used for benefits, occupancy costs
– Square footage – used for occupancy costs
– Percentage of budget
– Percentage of salary
– Percentage of client enrollment
There can be several allocation systems within one nonprofit.
Benefits can be allocated by amount of salary or number of
employees per program. Rent can be allocated by square footage
used by each program.
Allocations
• Whatever allocations your nonprofit uses be
sure that they are:
– Logical
– Measurable
– Reasonable to a neutral party
– Monitored on a regular basis to insure relevance
and accuracy
Non-Personnel Costs Allocated
Explanation on the next slide
Non-Personnel Costs Allocated
In order to simplify the chart, the shared costs have been allocated based on the
percentage of personnel costs in each area to the total personnel costs. [A more
precise way to do this would be to base facilities expenses first on square footage of
space used, and then allocate other indirect costs as a percentage of the total business
based on the previous year’s financials. I.E. if programs were 79% of the business and
fundraising was 11% and G&A 10% than indirect, shared costs not specifically identified
by other methods would be allocated respectively
ALLOCATION %
Program 1
Program 2
Program 3
Program 4
Adult Ed
Comm Ctr
After School
Summer Camp
Fundraising
Admin
17%
13%
28%
21%
11%
10%
These percentages are then applied to the shared costs like Equipment Lease, Insurance,
Training. If your organization can relate insurance costs directly to specific programs,
then it would be a direct cost and not allocated. It can also happen as with office
supplies that administration used significantly more than the programs so part of the
total expense is a direct cost to administration and part is allocated.
There are no rules on allocation but it must be logical, reasonable and verifiable.
Review of allocations
Review of budget
After the income is compiled and allocated, the expenses are estimated
and allocated, the budget needs to be reviewed for reasonableness.
Does the budget make sense?
If the budget looks logical, then the programs are examined to see which
programs are self-sufficient (income = expenses); which programs are
cash cows (income exceeds expenses) and which programs are kept for
mission related purposes and do not bring in enough income to cover
their costs.
In the example provided, if the unrestricted income of $5500 was
removed from the Summer Camp, it would operate at a loss. Should the
organization close the summer camp? What if the Special Event didn’t
make $20,000 should the summer camp then be closed?
Remember this is simply a program based budget. It does not take into
account the timing of income or the timing of expenses. Traditionally,
this would be a first cut budget and would go through an iterative
process before arriving at a final budget which would be based on at
least 12 months of income and expenses, budgeted chronologically.
Questions
Allocations are hard to understand if you have not been working in finance. If
you have any questions, please email me.
Budget Presentation &
Adjustments
Nonprofit Financial Management
Budget Presentation
Budgets created by staff are usually presented initially to a
Finance Committee or a volunteer treasurer (depending on
the size and complexity of the nonprofit). It is then
presented to the full Board for its approval.
The version presented to the Finance Committee/Treasurer
is usually more detailed than the final version presented to
the board which is usually a one page summary.
In both situations it is useful to include a narrative that
explains the information presented and to include a
comparison with the previous budget and the prior year’s
actuals.
Budget Narrative
The budget narrative can consist of notes next
to the numbers on the budget spreadsheet or a
separate word document. How the narrative is
presented depends on the readers’ preference.
Two examples of the types of narrative follow:
Example of notes
Example of Narrative
The budgeted income is increased because:
–
–
–
–
–
A new foundation has expressed interest in our mission
The contract will allow for a 7% increase
The fee increase represents more participants
The decrease in contributions is due to the death of major donor
The Special Events committee feels comfortable that the income listed
will be received
The budgeted expenses are increased because:
– More staff will be hired to work on the expected new grant
– Temporary staff will be reduced because the new staff will handle
their duties
– Increases are expected from most vendors
Narrative Rationale
On every board, there are people who find it easy to
understand numbers, and people who prefer words to
explain the numbers. It is easier to provide both numbers
and words explaining the numbers so that all Board
members can follow and participate in the budget
discussion. The Board needs to own and monitor the
budget as part of their fiduciary duties and a well-written,
detailed narrative helps them perform this duty.
Budget approval
The question one needs to ask is,
“Would I approve this budget, if I
was a member of this board?”
If not, why not? What would I
want to see changed? What
makes me uncomfortable? Is
there more information
necessary to help me make a
good decision.
If I would approve it, why do I
think it makes sense?
If you can’t answer these
questions for a budget you
present, you can’t expect the
Board to approve it.
Budget adjustments
The hardest part of developing budgets is handling the
unexpected. The next series of slides will present how one
organization handled the adjustment of their budget when they
learned anticipated income would be $52,000 less than initially
expected.
The options that exist when this happens are:
• Reduce expenses
• Increase income
It is very important to remember that if the choice is to increase
income then that revenue must be certain. If you add
imaginary income and do not reduce expenses, th …
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades