ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
3
Scenario Planning Tool
4
5
6
7
This template is intended to be used for projecting an organization's cash flow across the fiscal year under up to 4 different potential scenarios, identifying in advance any potential cash shortfalls that may need to be addressed. The instructions below explain, step-by-step, how to use the template.
8
What do the tab colors mean and what should I use each tab for?
9
10
COA- Input data on this tab -
This tab is used for inputting your organization name and chart of accounts.
11
12
Budget- Input data on this tab -
This tab is used for inputting your organization's original budget assumptions.
13
14
Personnel- Input data on this tab -
This tab is used for inputting your organization's personnel assumptions for the 4 different scenarios.
15
16
Scenarios- Input data on these tabs -
These tabs are used for making adjustments to the budget assumptions in 3 alternative scenarios.
17
18
Summary- No input on this tab -
This tab shows the side-by-side summary for the organization under the 4 different scenarios.
19
What do the different cell colors mean and what should I use each type for?
20
21
Yellow CellsUsed to input data (general) - cells do not include formulas.
22
23
Grey CellsUsed to input data (actuals) - cells do not include formulas.
24
25
Green CellsDo not input data - these cells may include formulas.
26
27
Blue CellsDo not input data - these cells may include formulas.
28
29
White CellsDo not input data - these cells may include formulas.
30
How do I input data into the template?
31
32
Customizing the template for your organization
33
Enable Macros
34
•If you have not already done so upon opening this template, please click the button at the top of the screen to "Enable Content" when prompted.
35
36
Org Name
37
•Begin by navigating to the COA tab and enter your organization's name in cells B2-E2.
38
Chart of Accounts
39
•Using your current fiscal year approved budget, begin inputting the names and groupings of your chart of accounts.
40
•If your organization uses header accounts, enter those category names in the yellow boxes with the bolded text (starting with cells D9-E9) by overwriting the current text. If your organization does not use header accounts, simply delete the existing text.
41
42
•Use the non-bolded yellow boxes to enter the account names (starting with cell E11). To track the details of specific grants/contracts/other contributions, you can use the account input cells to list the specific funders in the revenue section.
43
44
•You will not be able to customize your salary and fringe accounts here, but those details can be entered later on the Personnel tab.
45
•If there are more spaces than accounts, simply leave blank or delete the suggested text. Do not try to delete any rows.
46
•Some suggested capital/financing accounts are listed below the expense accounts (cells E392-E401). Use or rename any applicable lines from these cells. These accounts might not be listed in your approved budget as they only affect the balance sheet, not the P&L. Any unused account names can be deleted.
47
48
49
•Once you have finished entering your chart of accounts and cleared the text from any unused spaces, press the "Customize My Scenario Planning Tool" button at the bottom of the COA tab. This will hide any unused lines on the subsequent tabs.
50
51
52
Entering your organization budget
53
Fiscal Year Start Date
54
•Next, navigate to the Budget tab and enter the first day of the current fiscal year in cell F2 in MM/DD/YYYY format. The months listed in cells F10-Q10 will update accordingly. If the default date is your fiscal year start date, skip this step.
55
56
Actuals Through End Of
57
•Select the month through which actual results are available from the drop-down box in cell F3. The columns for the months showing actuals will be shaded gray to represent the completed portion of the year.
58
59
Beginning Cash Balance
60
•Enter the organization's cash balance as of the beginning of the fiscal year in cell F4.
61
Prior Year Carryover
62
•Enter the total amount of any prior year receivables carried forward into the current year in cell D191.
63
•Enter the total amount of any prior year payables carried forward into the current year in cell D367.
64
Current Year Budget
65
Revenue
66
•Enter the budget amount for each revenue line according to your approved budget in cells C14-C190.
67
Non-Personnel Expenses
68
•Enter the budget amount for each expense line (excluding salaries and fringe) according to your approved budget in cells C200-C366.
69
Capital & Financing
70
•Enter the amount for any budgeted cash receipts or disbursements from capital expenditures or financing activities in cells C373-C381.
71
Personnel Expenses
72
•Navigate to the Personnel tab to enter the salary details for each budgeted staff member and fringe details for the entire organization.
73
•Enter the title for each budgeted full-time staff member in cells B12-C61. Enter the full-time equivalence and full year salary amount for each person in columns D and E respectively. Repeat for any part-time staff members in cells B65-E114.
74
75
•In the fringe section (starting on row 120), Social Security and Medicare taxes are already detailed. Below those lines, enter the names of any other applicable payroll taxes or benefits applicable to ALL STAFF(cells B122-C127).
76
77
•Social Security and Medicare will calculate automatically. For each subsequent line, enter the full-year budget total in cells F122-F127.
78
•In the "Other Benefits" section (in cells B131-C138), enter the names of any other staff benefits for which ONLY FULL-TIME STAFF are eligible. Enter the full-year budget amounts for each line in cells F131-F138.
79
80
Surplus/(Deficit)
81
•Once you have finished entering your budget, this amount in cell C369 represents the organization's surplus or deficit from operations for the fiscal year.
82
83
84
Entering cash actuals
85
Entering Actuals
86
•Return to the Budget tab and in the grey shaded cells, enter the actual cash receipts and disbursements for each applicable month (excluding salaries and fringe). These actual results will pull through to the subsequent scenario tabs.
87
88
•Navigate back to the Personnel tab to enter salary and fringe actuals in the appropriate month(s).
89
90
Entering cash projections
91
Entering Projections
92
•Return to the Budget tab to enter projected cash receipts/disbursements for the remainder of the fiscal year.
93
Spread Evenly?
94
•Once all budgeted amounts have been entered, for each line item, select "Yes" or "No" from the dropdown menu in column E to indicate whether this item should be distributed evenly across the remaining forecasted months.
95
96
•For any budgeted item expected to be received/disbursed in equal installments throughout the year, select "Yes" from the drop-down menu. This will automatically distribute equal amounts across the projected (yellow-highlighted) month(s), less any actuals to date.
97
98
•If a line item is expected to be received/disbursed unevenly, select "No" and manually enter the amount to be paid or disbursed in the corresponding month(s).
99
100
•Similarly, select "No" and manually enter any receipts/disbursements which may carry forward to the next fiscal year.