Colleges and Universities Need to Remove Manual Barriers for Effective Planning

Here is a story that may be familiar for many finance offices at Colleges and Universities.  You are planning for the 2021/2022 academic year. You are planning 6 months in advance, partially because those are the timelines required to go through the various strategic and tactical layers of planning, and partially because the technical planning process has a lot of moving parts and, frankly, it just takes a lot of time.

You grab the set of Excel files you used last year. There is a central workbook with about 20 tabs with consolidated reports and different schedules for enrollment, tuition, room and board, athletic programs, and donations. There also 30 individual files with redundant formats that correspond to your 30 different responsibility centers.

The historical actuals need to be updated with a new year of data. So, you go into Colleague, Banner, Jenzabar or whichever ERP you use and download the trial balance from the prior year. The file with the trial balance is formatted awkwardly so you spend some time cleaning it up: putting it into a nice tabular format, flipping signs, splitting the GL string, and so on. You carefully place this final table into the raw data tab in your master workbook where you will link back to it from other tabs and files.

Then you do the same thing with your payroll system so that you can budget positions and headcount. Except here you need to carefully go into each of the 30 department workbooks and place the right set of employees.

Now you need to update your tabs to work with the new budget year. You are doing incremental budgeting so for each tab, you take your prior year final budget and make it the baseline for the upcoming year. You have a new year of actuals to show as well, so you copy and paste the prior year columns and insert formulas for the new year. Every tab in your workbook needs to be updated.

A wrinkle comes up at this point; the school added some new programs since last year. Now you copy and paste and set up new tabs for these programs. Once you have the formatting and labels just right, you go into your numerous consolidated reports and update the links so that these new tabs get aggregated properly.

Now it is time to elicit feedback from your 30 department heads. You go to e-mail the file and you have a sudden moment of anxiety. There is payroll information in this file, is it going to the right person? You check and double-check and finally click “Send”. 29 more e-mails to send.

After a month and a few reminder e-mails, you start to receive the files back. Some of them have the numbers filled out correctly. Others did not follow the instructions, or the numbers do not make sense, so you send e-mails back and book further meetings.

Once you have all the files returned, you consolidate everything into your Excel models, and you can finally see the big picture. Of course, there are further changes to make. Expenses in a few areas are unrealistic. A few departments added far too many new positions. There are some big structural issues between expenses for a couple programs and the expected enrollment and tuition.  None of these are surprising or unexpected, they just take time to work through. And that time gets multiplied for every part that is manual.

Remove tedious, manual tasks

Removing the manual points of friction is the key to opening new possibilities and improving the planning process. Every manual step is time stolen from finance leaders. Every manual step is lengthening the timelines it takes to plan. Every manual step is making it more complex to incorporate good feedback from around the University or college. Every manual step is introducing the possibility of errors.

Fortunately, modern Corporate Performance Management software reduces friction at every point in the process:

  • You can set up data feeds that automatically load your trial balance, enrollment data, payroll information, curriculum information, or any other sources.
  • You can set up standard input forms that you manage centrally and are reused for each of your programs or departments. Whether you have 20 departments or 200, you set up the format once and the unique combinations are created automatically.
  • You can really take charge of your budgeting timelines and workflow. Rather than e-mailing files, your responsibility center owners log in to see the latest numbers and make inputs. You get live updates as the work is happening along with a dashboard to see who has submitted and who is still outstanding.

When you eliminate these manual sources of friction, you open opportunities to plan more often, plan in greater depth, and include others in your planning process.

Plan more frequently

The annual budget is the standard for most organizations and for good reason.  Universities and colleges have very structured academic terms that line up neatly with the calendar year, so it makes sense to plan against that.  But at the same time, planning once per year is somewhat arbitrary.  The situation can change more frequently than that.

  • With a recent uncertainty around the pandemic, what will enrollment look like between terms?
  • If you implement new academic standards, what will that do to student attrition?
  • How accurate are your expense forecasts and will you need to revise those at some point?

At a minimum, it is a good idea to run a short planning phase before each term to true-up assumptions and ensure the financials stay aligned with the central goals and mission of the university.

These new reforecasts need not be as detailed as the full budget process. If the original plan is strong and still highly relevant, you may not make any changes at all. You may only make some tweaks to enrollment or headcount. You may even keep this central within finance rather than a larger process that incorporates the rest of your responsibility centers.

The key is not to re-do an exhaustive budget process multiple times per year, but rather to be dynamic and ready to go through parts of the planning process whenever the need arises. Your software solution should be capable of being turned on or off whenever you need it, with minimal setup or configuration from you.

Plan with greater detail

Sometimes the summarized data coming from your general ledger is too broad to really help in the planning process. Take your dormitories and room & board. Within your financial statements, these revenue and expenses might be grouped into a handful of line-items. Planning at that same level of detail will hurt accuracy and will make it difficult to measure performance later.

You might plan on-campus housing by first coming up with the logic for revenue. Housing revenue might equal the number of students’ times the average rate for rooms. Then you would split that by the number of housing facilities you have and for the different room types, with pricing different for single occupancy, double occupancy, shared accommodations, and so forth. From there you would layer on meal plans and then the various expenses for repairs & maintenance, security services, and utilities, and other components.  Maybe some of these expenses are tied directly to your occupancy plan.

In effect you can split off a separate housing model that loads historicals from your systems, uses a common set of calculations across your various room types and buildings, and is tied back to the full plan.

What else?

  • How much better will your plan be if you could model individual student cohorts with unique attrition and pricing assumptions for each?
  • Who is the list of donors from last year and how much do we expect them to contribute this year?
  • What are all the outstanding IT contracts for the college, when do they expire, and will they be renewed?

Include more voices in the process

A great financial plan incorporates insights from across the whole college. This may include recruiting & admissions, student services, advancement, alumni relations, human resources, all the academic units, and many others.

With a CPM system in place, it becomes much easier to immerse these different stakeholders in the process. They can see all their historical data and it gets updated monthly. They can submit iterations of the plan and instantly communicate back and forth with you on key topics. They know exactly what is expected on them. They become accountable to their portion of the budget and put more effort and thought into their inputs.

As the cumbersome mechanics of e-mailing Excel files back and forth go away, it becomes much easier to add new partners into the process. Whereas in the past you may have only interacted with the Vice chancellor of student affairs, now you are opening the process to the leaders of the career center, residential life, athletics, counseling, and student activities as well.


Eliminating manual processes from your planning process opens you to many possibilities.  Plan more frequently.  Plan in greater detail.  Tap into the voices throughout your institution. Prophix helps you modernize the tedious and impractical components of your planning process and lets you focus on analysis and being a strategic partner to your university or college. To learn more visit here.

Andrew McDonald

Andrew has spent 15 years consulting for customers in the world of corporate performance management. Andrew has implemented a variety of products hands-on, has architected large enterprise-scale deployments, and has led Professional Services teams. Andrew has worked with dozens of colleges and universities over his career and currently heads up Prophix’s Higher Education practice.

Archives