From: Module 22 - Operating Plan and Variance Analysis

1. Develop an allocation and an allotment plan. To complete this exercise, use the file provided by your instructor entitled Budget Tools 2e Module 22 Operating Plan. On the tab labeled Exercise in the spreadsheet is the proposed budget for a health care finance program. This budget spends a lot of money on health care claims in object code 2241 and pays a lot of money for claims processing in object code 2501. The legislature has provided $30 million for new health care claims benefits but no money for the estimated $25,000 in processing costs for new claims; these costs are to be met through administrative savings. The legislature has also demanded other administrative efficiencies, but little guidance has been given for achieving them. Based on past payroll patterns, you may want to anticipate that 48% of the expenses will be incurred in the first half of the year and 52% will be incurred in the second half. Benefit ratios are provided in the spreadsheet. There are no special purchases. The building lease for the departmental office is $125,000 per month. Net legislative adjustments are $28.75 million. As a new analyst, you are not sure what some of the expenditures are for, so your initial proposal will likely be revised, but you have been tasked with making an initial proposal. Need to use the provided spreadsheet.

1. Lakeside has closed its books for the first half of the fiscal year. Calculate the variance and variance percentage of the data presented in Table 22.6.

3. The legislative body of Brookville has completely approved your proposed budget of $400,000 for the fiscal year starting July 1, with no reductions. Prepare your annual budget by object code and month using the following guidance:

(a) Salaries are $23,000 per month, and benefits total 15% of salaries each month.

(b) General supplies are purchased at the beginning of each quarter and total $400 per quarter.

(c) There are two service contracts. One is for your website's host and development and is paid in two parts: $15,000 in December and $25,000 in June. The second contract is for data analysis by an outside vendor. Payment on this contract is due every January, for a total of $2,000.

(d) The director of your department attends a national conference every October. Their training (which includes travel) budget is $3,000 for the conference.

(e) This year, your department will be outfitted with new technology equipment, including PCs, laptops, a phone system, and a new server. The cost is expected to be $36,000, and the transition should happen in February.

4. Brookville has just closed the fiscal month of November. You have not incurred any expenses thus far that were outside of your original annual plan. Your department has the following year-to-date actual expenditures (YTD actual):

Regular pay: $115,000

Benefits: $17,250

General supplies: $780

Service contract: $15,000

Training: $1,500

Using the budget prepared in assignment 2, what is the variance by object code and for the overall department? Prepare a brief variance analysis summary for the department director regarding the department's budget-to-actual position after the end of November. Based on your plan, is there anything of concern that your director needs to know about? For example, is the variance positive or negative, and is this favorable or unfavorable?

TABLE 22.6 Lakeside Year-to-Date Budget and Actual

YTD YTD Budget Actual FEES 5,000 3,785 FEE REVENUE 5,000 3,785 All Revenue 5,000 3,785 REGULAR PAY 132,710 122,360 BENEFITS 44,442 41,816 TOTAL PERSONNEL 177,152 164,176 GENERAL SUPPLIES 1,095 SUPPLIES 1,095 SERVICE CONTRACT 2,644 1,798 TRAINING 1,543 300 TRAVEL 2,219 1,209 POSTAGE 120 336 SERVICES 6,526 3,643 EQUIPMENT 5,000 CAPITAL 5,000 All Expenditures 189,773 167,819