"Program Costs and Revenue Generation Worksheet Instructions"
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 program. 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. 1 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 automatically. 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 worksheet. 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: http://www.extension.iastate.edu/extensionfinance/revenue.htm 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. 2 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. 3