Objectives:

Build a budget in Excel Correct budget format Projections

Construction of revenues and expenses based on unit data Constrained growth of parameters

Excel programing of flexible budget

Be able to conduct scenario analysis of data Use budget for policy recommendations

Material

AssignmentBudget Building and Scenario Analysis-2.docx AssignmentBudget Building and Scenario Analysis.xlsx

Case

As the budget director of the citys Department of Natural Resources (DNR), your task is to prepare the DNCs budget for fiscal year t and, in a one-page memo, present it to the Mayor and address concerns about revenue certainty. See Activities at end of case for more details.

Last year, the DNR had $1,158,000 in revenue. The fiscal year runs January to December; with the first quarter from January to March; the second from April to June; the third from July to September; and the fourth from October to December.

The department receives revenues from a variety of sources. Historically the DNR receives $700,000 from the citys general fund. The remainder of the funds come from revenue-generating activities and grants. The DNR charges fees to citizens who want the city to prune their trees. The DNR charges $300 to prune small trees; $600 to prune medium trees; and $900 to prune large trees.

In addition, the Land Conservation Fund provides a 1:2 match for any money the DNR spends on conservation programing (that is, for every $1 the DNR spends, the Fund contributes $0.50). The DNRs conservation activities include water systems and tree planting. The DNR spends $60,000 annually on water systems. It also invests in tree planting, where the reimbursement rate from the Land Conservation Fund is $200 for the purchasing and planting of each tree (includes cost of trees and labor). The Land Conservation Fund pays the DNRs conservation activities in the same month as when the activities occur.

Table 1 shows the historic data for tree pruning services and planting. The DNR expects future activity to follow the growth trajectories.

Table 1: Historic Data for Tree Pruning and Planting

	Actual	Actual	Actual	Actual	Actual	Budget	Projected
	FY t-6	FY t-5	FY t-4	FY t-3	FY t-2	FY t-1	FY t
# trees pruned, small	60	69	79	91	105	121
# trees pruned,	160	189	223	263	310	366

medium
# trees pruned, large	78	87	98	110	123	137
# trees planted	80	86	93	101	110	118

Water system activities occur evenly throughout the year.

40% of pruning activities occur in Quarter 2; 30% occur in Quarter 3; and 20% occur in Quarter 4.

All tree planting occurs in quarter 2 (April June).

The DNR must pay its employees, which includes a Director, an Associate Director, two Managers, and fifteen staff. The four administrators receive a salary: Director ($114,000); Associate Director ($86,000); and each Manager ($62,000). The staff receive hourly wages at $18/hour. Each staff member works an average of 36 hours per week. Each staff employee receives a lump-sum bonus of $200 in months when the total number of tree-activities exceeds 60.

As well, the DNR pays benefits to each employee. These benefits are consistent with city policy and include payroll taxes (the Federal Medicare/Employer-Employee tax used to fund the Medicare insurance program and the Federal Old Age Survivors and Disability Insurance Employee Expense, more commonly known as Social Security), health insurance, life insurance, and retirement contributions. The Fed MED/EE rate is 2.9%, and the employer pays half of that. Employers and employees share, in even portions, contributions to the Fed OASD/EE tax. The rate paid by each is 6.2% (for a total of 12.4%).[1] Each month, the DNR contributes $600 to each employee to cover medical and dental insurance; $7 towards a basic life insurance policy; and 5% of salary or wages to a retirement fund. The DNR has no ability to adjust federal payroll taxes, and other benefits are difficult to change in the short term.

The DNRs operating expenses include $8,500 in monthly equipment and operating costs and $14.50 per tree planted. Additionally, the DNR spends funds on its water system.

Notes for Students:

Assume 4 weeks per month.

Assume that a bonus constitutes compensation upon which employment taxes are levied.

Assume that the DNR forecasts trees using average growth.

As you begin this assignment, it may be useful to conceptualize it on paper. Before you build the budget, think through the construction (with paper and pencil) of what the line items of the budget should be.

The DNR cannot prune or plant a portion of a tree. If a monthly value is not a whole number, round up the value to the next whole value using Excels ROUNDUP or CEILING function.

For programming the staff bonus, use Excels IF THEN command.

Be sure to link the Excel cells so that you can update your budget for a parameter change by simply changing the parameter on the first tab.

Hint: after you identify the parameters, and before you build the budget, forecast the # of each of the tree activities per year, using one of the forecasting skills. Then, allocate those tree activities per month. These data are fundamental to the monthly budget. That is, populate the yellow highlighted cells on the parameter page of the spreadsheet before you build the budget on the budget page.

Activities

Part A (60 points)

Address the analytical tasks in a spreadsheet. When you are ready, and before the due date, take the quiz and submit your spreadsheet.

The Mayor asks you to prepare the DNRs operating budget for the next fiscal year, year t, and analyze it to address the policy questions. A complete budget includes revenues, expenses, and balance for each month and for the annual total. You are to create a flexible operating budget in Excel, using linked cells, for the 12 months and annual totals of FY t.

The Mayor also wants to test the sensitivity of the budget to changes in some of the parameters. Using your spreadsheet to generate budget data, evaluate the effect of the following scenarios on the budget:

Cuts to general fund revenues, Fee increases, and

Personnel changes.