Budget Tracking and Reconciliation Systems Overview
Detailing a comprehensive budget tracking and reconciliation system developed by Lani Woods for managing a $5 million operating fund, including the purpose, components, Excel workbook usage, and reconciliation process.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
ARC 2017: BUDGET TRACKING AND RECONCILIATION SYSTEMS BY LANI WOODS, AA/S PHONE: (805) 756-6320 EMAIL: LLWOODS@CALPOLY.EDU COLLEGE OF ENGINEERING, ELECTRICAL ENGINEERING DEPARTMENT, CAL POLY JUNE 1, 2017
FORMAT OF PRESENTATION Provide information about my budget Explain system of Excel workbooks I use Demonstrate Excel workbooks I use Ending summary Questions
INFORMATION ABOUT MY BUDGET: MY BUDGET FAST FACTS Budget Type: Operating Fund, $5 million, Composition: 96% salary & benefits; 4% OE Track budget for 22 Tenure/Tenure Track Faculty; 18 full-time and part-time lecturers; 4 staff members; 6 teaching associates, a fluid number of ISA s and SA s I track the budget for all department spending categories (OE); a variety of Foundation (aka University Campus Programs); several Lottery accounts and Corporation accounts.
INFORMATION ABOUT MY BUDGET: PURPOSE TRACKING: Initial budget projection Report budget status to department chair throughout the year Provide comparison from prior projection Explain changes to budget being able to answer WHY? Provide year-end projection Explain impact of future change scenarios to budget RECONCILIATION: Reconcile actuals
EXPLAIN EXCEL WORKBOOK I DEVELOPED My budget tracking system involves workbook with linked pages for: Master Payroll Operating Expense (OE) Lottery Budget (income) College Based and Student Success Fee Demonstrate how I prepare workbook for reconciliation
RECONCILIATION To begin the process for reconciliation I use 4 primary reports. Revenue & Expense Summary The Budget Transactions Labor Cost Distribution Revenue & Expense Transactions. The Revenue & Expense Summary Provides us with the bottom line that we eventually want to make sure matches to all the Budget Summary reports we submit to the hire ups. The budget Transactions shows us all the budget entries for the month. The Labor Cost Distribution shows us the breakdown of base salary and benefits for each employee. I compare the totals on this report to the totals on the Revenue and Expense Transaction report to assure that salaries and benefits are reported under the correct category. I ll show you why in a view minutes. Revenue & Expense Transactions: This report is used for reconciliation.
DEMONSTRATE: BUDGET WORKBOOK I manage all department budget commitments, including additional funding from various sources Projects salary and benefit of all employees by classification Categories are identified by account # s. Supplies and Services are broken down into categories identified by the department: Lab Equipment, Lab Expense Consumables, Lab upgrades, Alarm, Office Supplies, Supplies & Services Summary tab for me which is more complex and contains reconciliation Summary tab for the Dean which is more simple Summary and budget tab rolls into Summary for the Dean. DEMO Budget Workbook
DEMONSTRATE UPDATING BUDGET WORKBOOK Budget Workbook Once I have completed reconciling for the month I match the actuals for the month to the projects in my budget workbook. As you can see the tabs on the budget workbook align with the tabs on the reconciliation workbook There is a line on each worksheet for each employee according to their classification. Each line includes their gross amount, their base salary and the benefit costs. Each worksheet is linked to a summary page I make sure the totals for the month match the totals on the Revenue & Expense Summary report to make sure that I have not changed some numbers for the previously reconciled months. The budget tab includes all budget entries for the month The summary and budget tabs are linked to the Dean s Report Tab. I review this report to make sure the totals match the budget and summary page. The Dean s Report provides me with quick information regarding our projected balance for the year. I also have columns that include our original projections (column B) and the actuals for the previous year (column D)
ENDING SUMMARY I have provided information to you about my budget and the linked excel workbook I developed to help me manage my budget projections My Excel workbooks utilize formulas and links to provide updated budget projections to the college Dean With 96% of my budget in salary & benefit costs, changes to course offering have a direct effect on budget and this is captured in the workbooks I developed. It imperative that we project our personnel costs early in the FY in order to assure that we are provided adequate funding to cover all the courses we offer throughout the AY. Cal Poly is on the quarter system so adjustments to courses offered in spring can often change budget projections depending on who we hire to teach additional courses not originally anticipated in the budget. I have provided a brief demonstration of the Excel workbook I developed to help reconcile and to manage my budget projections.
THANK YOU & QUESTIONS Thank you for your time; I hope this demonstration has provided you with information and ideas that will make your budgeting easier! Any questions?
