Proposal Budget Instructions version 11.00 - 09/29/2010 1 Proposal Budget Spreadsheet Instructions ProBudgt.xls (ver. 11.00 - 09/29/2010) This form has been modeled after and adapted from the form available from the Texas Engineering Experiment Station. The proposal budget spreadsheet has been designed to allow the entry of budget information into one worksheet and then automatically transfer the entered budget information into the respective agency forms on subsequent worksheets, making all necessary calculations along the way and allowing for instantaneous updates to all budget forms. We will begin with a brief discussion of terms used within MS Excel when discussing Spreadsheets. Microsoft has designed their spreadsheet format using the basic item called the workbook. Within a workbook, you have individual worksheets. The worksheets are accessed using the filetabs along the bottom of the workbook screen. In our particular case the workbook is called “ProBudgt.xls”. The worksheets are seen along the bottom of the screen, labeled Data Entry, File Copy, NSF 1030, etc. The first step for a new proposal is to save the Proposal Budget Spreadsheet under a different filename. We suggest using your RO number. The RO# is the internal tracking number assigned by the Division of Research. This will protect the integrity of the information in the master document. The naming convention for these files should be RO#bud.xls (i.e. 11075bud.xls). The Data Entry worksheet is where the pertinent information is entered and calculations are made. At the top of the page the RO number is entered for reference and the date is automatically determined. The project duration is entered and the applicable Indirect Cost Rate (%) is entered. The default indirect cost rate is our standard on-campus rate currently 56% of MTDC. A field is also available for the entry of an Annual Salary Escalation rate (%). The default is 3%. This field will increase the Investigator salaries annually by this percentage based on the entry of the first year figure. To the right of these cells are five cells where the names of all involved Investigators are entered. To the right of those cells are five sets of radio buttons – one for each Investigator. These buttons are used to determine the type of appointment for each Investigator. The selection here determines whether their salary amount will be calculated on a 9-month basis or a 12-month basis and whether they will be paid in calendar months or academic/summer months. Next is a set of buttons for the selection of the Indirect Cost Calculation Method. Indirect Cost Base Calculation - Five different calculation methods have been set up for the calculation of the Indirect Cost Base Amount. These calculations are based on the different items that sponsor agencies include or exclude from their calculation of Indirect Costs. The actual calculations are performed below the data entry portion of the worksheet. In cells A263 – G267, the base amounts for the different calculationProposal Budget Instructions version 11.00 - 09/29/2010 2 methods are determined. From these cells the proper calculation for the method selected with the radio buttons is copied into cells A280 – F280 (IDC Base Amount). The same process is used for the actual IDC calculations in cells A256 – G260 with the proper total being copied into cells A175 - F175. This method of calculation allows any changes made to the calculation method, IDC rate, and/or amounts to be incorporated “on the fly.” MTDC SWFB NSF, MTDC TDC FEE, All Other TDC minus Total Salaries + TDC minus TDC Fee Amt. Total Equipment Total Fringes Total Equipment Tuition Tuition Electron Microscope Electron Microscope Fabrication Fabrication Sub-Contract w/o OH Sub-Contract w/o OH Computer Services Computer Services Total Participant Costs Table 2. Indirect Cost Base calculations Below that, in row 12, a title can be entered for the project and then the worksheet splits into columns: Col. A - Headings; Cols. B-F - Years 1-5; and Col. G - Totals. Cells with black text and a yellow background are designed for the entry of information by the user. Cells with red text contain formulas and are not meant for user input unless necessary to that particular proposal. This section begins with the information for Principal Investigators. The names of the PI’s are automatically copied into the cell just to the right of their Investigator status (PI, Co-PI#1, etc.). These would be B14, B21, B28, B35, and B42. Most of the items in this section are self-explanatory. The amounts are filled in year-by-year for each applicable item. Keep in mind that any cells with red text are designed to be calculated by the formula within the cell. Figures are not meant to be entered by the user. Of course there are always exceptions, so make sure that you save the spreadsheet under your RO# before making any changes! A few notable exceptions to the self-explanatory rule may be: Graduate Student(s) - Stipend(s) - This entry is calculated by multiplying the number of graduate students (row 54) by the annual stipend amounts shown in (row 240). Subcontracts - The subcontracts entry is split to allow for differentiation between amounts to carry overhead and overhead free amounts. As a part of our negotiated indirect cost rate agreement, only the first $25,000 of each subcontract amount is charged overhead.Proposal Budget Instructions version 11.00 - 09/29/2010 3 Tuition - The tuition entry is calculated by multiplying the number of graduate students (row 54) by the Graduate Student Tuition amounts shown in A241 - F241. The total tuition amount is calculated based on the policy of 10 credit hours per year for each student, times the applicable annual rates. The amount of cost sharing is calculated by adding the amount of tuition cost shared (if applicable - row 169) and any adjustment amounts entered in Rows 271 - 275. The description of entries for these rows is as follows: • Calculated Amount on Tuition - This row will simply copy the figures from Row 163 to allow you to see the total cost-sharing in one area. • Additional Amount (already in TDC) - This row will allow certain amounts already contained within the calculations above to be included as cost sharing. This would most likely be for agencies that have special requirements, such as cost sharing of