Budget Projections

1. Budget Projections Utilizing Banner Information Grant Brown Bag Information Session December 18, 2012

2. Budget Projection Topic coverage • Uses for budget projections • Responsibility for budget projections • How to create a budget projection file in Excel • How to find & export information from Banner • How to project that data out to the Fiscal Year End(s)

3. Budget Projection Uses Primary Users • Plan spend down of remaining funds • State required budget projections • Mid year & Year End • Budget Modification tool • Aids in finding errors or omissions • Grant Director • Business Office; State of RI • Institutional Advancement, Controller’s Office, Grant Director, Funding Source • Grant Director, Controller’s Office, CCRI, Funding Source Budget Projections are useful! The list of uses and users goes on extensively!

4. Mid-Year Budget Projections Due in Mid-January – here’s why we do them: • CCRI is required to submit a mid-year review of its budget to the Office of Higher Education (OHE) for review and approval.  • Mid-year review is an update on all sources of funds, not just state funds and tuition and fees.  • Opportunity to identify changes occurring since the beginning of the year & identify problems that may exist that require action. Need to know how much you think you’ll spend by the fiscal year end (June 30th)

5. Who “Owns” the Budget Projection? • The ultimate responsibility of creating a budget projection lies with the Grant Director • Although the Controller’s Office & Institutional Advancement are happy to help, the grant director is the only person who can fully create a budget projection • Administrative personnel do not know the intricacies of the grant • E.g. – staff departures, planned operational spending

6. Budget Projection Creation • Create the “Base” Projection file • Open Banner; Open FRIGITD; Input Fund# • Next block (this may take a while to load) • Once data populates go to Help, select Extract Data No Key • Hold Ctrl key! • This exports to Excel

7. Budget Projection ~ Base file • The exported file will need titles & formatting • Add four lines at the top; Insert Grant Name & Fund # • Add titles on columns listed below • Add two new columns: Projected Exps. & Projected Balance

8. Budget Projection ~ Base file • Format columns D thru I with “comma style” • Adjust the column widths of columns B thru I

9. Budget Projection ~ Base file- continued • Create a formula for the Projected Balance • This is the Current Available Balance MINUS Projected Expenses • In the Proj. Bal. (column I), you type in: =G5-H5 • Copy this formula down for all of the lines (drag right corner)

10. Budget Projection ~ Base file- continued Use “AutoSum” in the top right corner of excel Put cursor in cell below the column you want to sum. Click AutoSum Copy for all columns • Enter a SUM formula at the bottom of all columns

11. Obtain Personnel Projection Data • In Banner, determine the last pay period posted to Banner for biweekly & lecturers (monthly) • In FRIGITD or FGIBDST, drill down the Payroll lines & take note of the Payroll # in description • Click inside the Activity column for the payroll line

12. Obtain Personnel Projection Data…continued • With the cursor inside the Activity column for the payroll line, go up to Options • Select Grant Detail Info if in FRIGITD • Select Transaction Detail Info if in FGIBDST The most recent payroll is usually on top. Below is BW24

13. Obtain Personnel Projection DataBiweekly Employees • In Banner, go to NHIDIST & query the last paid payrolls posted in Banner (BW24 in example) • This screen shows payrolls by the date they were paid. Go to HR site for date listing1 • Enter in the pay date of the last pay period posted in Banner, enter Fund #, then Next Block, Execute Query BW Pay Period 24 was paid 11/23/2012

14. Obtain Personnel Projection DataBiweekly Employees • After the data populates, you can either query within the NHIDIST screen, or export to excel. To export, go to Help, then select Extract Data No Key (Hold Ctrl key!) • If exporting, then add titles as shown below • AFTER titles are added, adjust column widths & then delete columns: Z, Q – T, I - L

15. Obtain Personnel Projection DataBiweekly Employees • On the personnel data, sum the total for each account number • Can use the “Subtotal” function in excel for faster results • Highlight the entire data field • On the Excel “Data” tab at the top, click “Subtotal”

16. Obtain Personnel Projection DataBiweekly Employees After clicking the Subtotal icon: Select: Acct; Sum; Check: Dollars; Uncheck: Replace current subtotals

17. Biweekly Personnel Data - After Subtotals Acct totals to use in projection column

18. Obtain Personnel Projection DataMonthly Employees • Go into Banner, NHIDIST screen again, and look up the “monthly” employees • Enter a wide date range to ensure you are capturing all employees • Enter the grant’s Fund #, then click Next Block

19. Obtain Personnel Projection DataMonthly Employees • In the lower section, scroll to the right and enter MN in the field to the right of Payroll Event • Click Execute Query

20. Monthly Personnel Data Shows monthly employees. Use this data to verify employee pay rates, calculate average hrs/mo. worked, & ensure all are included in projection. Can export info to excel – Help dropdown, Extract Data No Key (hold ctrl key)

21. Input Personnel Data in ProjectionBiweekly Projection • Determine # of pay periods between the last payroll posted to Banner & 6/30 (or desired date) • Use HR Pay Schedule to count # of Pay Periods between the last payroll per. posted to Banner & 6/30 = 16 Pay Periods (BW24 to BW14) • In Budget Projection Base file, enter formula for each biweekly account line based upon the Subtotaled Payroll file • For account 611040, the formula would be =705.60*16 • For account 612033, the formula would be =3412.58*16 • Do this for all biweekly account lines Dollars per Pay Period TIMES # Pay Per’s to June 30th

22. Input Personnel Data in ProjectionMonthly Projection • Determine # of REMAINING monthly payroll periods • The # in the MN payroll is the month it is paid. Monthly employees are paid the month AFTER they work, so MN11 is for October • Last monthly payroll was MN11 (October) – so 8 months to get to 6/30 • The monthly employees formula will be somewhat different than for biweekly people • Determine the # hours each monthly employee will work (best to use average estimate), multiply by pay rate, then multiply by remaining months The # in MN payroll is the month it is paid

23. Input Personnel Data in ProjectionMonthly Projection…continued • Monthly employee formula (do for each employee) • In our example, assume Natalie Wood works an average of 70 hrs/mo, her pay rate is $20/hr (8 mo’s left) • Formula will read =70*20*8 for Natalie • Van Morrison & Charlie Brown also work on that line, too, so we’ll have to include them in the formula, too • Assume Van works 60 hrs/mo @ $20/hr, • Charlie 15 hr/mo @ 15.54/hr Overall formula will read =70*20*8+60*20*8+15*15.54*8 Avghrs/moX Hourly Pay Rate X # months to Year End

24. Input Personnel Data in ProjectionMonthly Projection…continued • Most Monthly employees only have FICA as fringe on their pay (FICA Lecturer – 644020) • Create a formula based off the monthly employee salary line(s). FICA is always 7.65%. FICA = 7.65% In this case, the formula will be =H7*7.65% With this method, FICA will update if you change the employee pay projection

25. Incorporate Operating Expenses • This part is for the Grant Director! • Go through the various Operating Expense lines (i.e. Office Exp, Mileage) and put in your estimates of what you think you’ll spend by 6/30 • It can be helpful to jot down planned trips, classes, etc. • Enter the dollars you think you’ll spend in the projected column – do not enter anything in the Indirect line yet!

26. Indirect Cost Expense Help! • This can be tough to calculate, so don’t hesitate to ask for help on this one! • The most accurate way to do this line is via formula (sorry) • If done via formula, it will automatically update as you make changes • Your formula will be the sum of all expenses applicable to indirect multiplied by your indirect rate (or indirect rate + audit) • See next slide for example formula details • If the formula is giving you trouble, then once you feel you have all expenses estimated, then multiply the total projected expenses by your indirect rate

27. Indirect Rate formula Indirect = Cost Base X Indirect Rate If the Indirect Cost line isn’t the last line, then cut & paste to be the last line – you will probably need to fix your totals at the bottom Assuming an 8% rate applied to all expenses, the formula will read: =SUM(H5:H31)*8%

28. Let the Budget Games Begin! • After incorporating all necessary expenditures, take a look at your Projected Balance column • If there are negatives, you’ll need to create a budget modification to cover those negatives • You may notice that you have more money to spend! • Adjust dollars where necessary & ensure you have enough for the full grant term

29. Summary of Projection Steps • Export current budget detail from FRIGITD to excel to create projection base file • Add titles to columns & create Projection columns • Determine last pay period # posted to Banner – calculate # pay periods to year end • Export most recently paid biweekly payroll • Subtotal the expenses by account number • Determine monthly employee’s rates & average hours • Input payroll formulas into projected column • Input projected operating expenses to year end • Input Indirect Rate formula (if applicable) • Adjust lines as necessary

30. Thank you for attending! Questions & Comments

31. References • 1HR Pay schedules can be found at: http://www.ccri.edu/hr/calendars/ • Banner Screens: • FRIGITD – Grant Inception to Date (shows multi-year) • FGIBDST – Organization Budget Status (shows current CCRI fiscal yr only) • NHIDIST – Labor Distribution Query (shows labor for specified pay ranges) • Excel Resources: • Ask IT – they hold great training sessions • “Lynda” has online video modules - go to: https://webfor.ccri.edu/lynda/index.cfm • URI Prov. Feinstein 1-day course (Prof. Development)