Program Costs and Revenue Generation Worksheet Instructions by erg11737


									  Program Costs and Revenue Generation Worksheet Instructions

General Instructions

This worksheet has been designed in Excel to be filled out on the computer. The worksheet is
protected. Only the blue background cells can have information inserted. The TAB key will
move the cursor to those cells. The yellow cells are locked because they contain formulas.

The worksheet is designed to have three parts:
   1. The Budget part for planning purposes.
   2. The Actual expense and records part for analysis purposes.
   3. The Revenue Generation section for deciding how much stays in the county and how
      much goes to ISU. However, the final decision remains with the staff members doing the

You should plan to TAB through the whole worksheet as you plan the program budget and fill in
all the cells that pertain to budgeting.

The worksheet may be self-explanatory as you use it. However, these instructions are meant to
clarify the intent for each section or cell.

Where to begin

The goal of the worksheet is to help you set your budget, your charge per participant and the
division of your actual income. As you start at the top of the worksheet, you will consider several
things relevant to the event: the program or service, the persons planning, and the partners.

The worksheet is setup to pay County out-of-pocket program costs first. If there is income to
cover both County and ISU personnel costs, the worksheet pays the actual total. If not, the actual
amount available is prorated by percentage. There is also a place in Table D at the bottom of the
page to enter amounts that the planners want to underwrite.

You will also want to gather information on the minimum and maximum participants possible so
you can decide the expected number of participants for budgeting purposes.

Now use TAB or click on a blue background cell to enter Table A.

Calculated Program/Service Cost (Table A)

For most programs and services the entries in Table A will be out-of-pocket costs only. These
are expenses that would only be incurred if the program or service were offered. They include
expenses such as refreshments, meals, room rental, supplies, postage, advertising, costs of non-
Extension speakers, etc.

Personnel costs and travel costs from Table B are automatically entered in Table A. The only
additional personnel entry in Table A would be "other personnel and travel costs" that aren't
captured in Table B. Subtotals for out-of-pocket and personnel costs are calculated

The NOTES column may be used to record how you arrived at the Budget amounts. Then enter
the amounts in the Budget column. Leave the Actual column for after the event is finished.

The yellow background cells of Table A fills automatically as you fill out the rest of the

On Line 33, fill in the Number of Participants you expect. The worksheet will figure the Cost per
Participant for you as you continue to determine costs in Tables A and B. Remember that Table
D allows you to underwrite the program if you wish.

Personnel and Travel Cost Calculations (Table B)

Table B has two sections. Enter the number of hours for all individuals involved in developing,
coordinating, and delivering a program or service. Travel expenses also need to be entered.

The Support Personnel section would generally be County personnel. Enter their name(s), the
estimated number of hours they will spend supporting this program, their cost per hour and any
travel expenses. Here you may use your own figures for actual costs or the statewide average
costs from FY06 below.

The Direct Personnel section would generally be ISU personnel. These three lines have been
locked because of the formulas attached to them. If you want to enter names instead of the titles
listed, you will have to unprotect the page. Do not move the CEED from the third line. The other
two lines can be interchanged or changed to add another FS. Be sure to re-protect the page so the
TAB will function. Enter their name(s), the estimated number of hours they will spend directly in
this program, their cost per hour, and any travel expenses. Here you should use the statewide
average costs from FY06 below.

The statewide Average Hourly Salary/Benefits Costs are:

Travel should be estimated and entered for all personnel. All totals will transfer to Table A.

Conclusion of Budget Section

You should now have in Table A the total expected costs for the event. You can use these figures
to discuss the amount to charge participants for the program and how you plan to pay for the
program. Again, Table D allows you to underwrite the program if you wish. The worksheet
prorates the actual income.

Revenue Generation Calculations (Table D)

After the event and when bills are known, fill in the Actual columns in Tables A and B. Fill in
the income information in Table C. The spreadsheet automatically fills in the INCOME VS.
ACTUAL COSTS box. The spreadsheet calculates two different ways depending on whether you
are over or under your program costs.

If you are over costs, the formula pays all costs (Line 30 “Total Calculated Program/Service
Costs” less underwritten if desired) then leaves the remainder with the County. If you are under
Line 30 costs, the formula pays all out-of-pocket costs (Line 20 “Subtotal Out-of-Pocket Costs”
less underwritten if desired), then prorates the remainder to ISU less underwritten if desired and
the County less underwritten if desired.

These figures are reflected in Table D “Revenue Generation Summary”.

Conclusion of Actual Section

Finding a formula that will work in all situations is probably impossible. These two formulas
take a simple route to give you numbers that you can work with to decide what amount you will
split and send to ISU. The planners can just use the amounts shown in Table D or decide to
adjust the figures by some other principle.

The worksheet is not password protected. To change any formula or add space just click on
“Tools”, chose Protection, click on Protect sheet or Unprotect sheet. To allow the TAB function
to work, be sure to protect the sheet after you make edits.

Also many cells are locked. These can be changed by right-clicking in the cell, then clicking on
Format Cell, then clicking on Protection tab and checking or unchecking the “Locked” box.
Unlocking a cell permits it to be typed in when the sheet is Protected.

Definitions used in Formulas

Out-of-pocket        All program cash expenses paid by the County. Does not include County or
                     ISU personnel expenses or County or ISU travel expenses. Less County
                     underwritten if entered in Table D.
Prorated             ISU prorated share includes ISU personnel cost plus ISU travel. Less ISU
Personnel costs      underwritten if entered in Table D.
                     County prorated share includes County personnel cost plus County travel.


To top