Budget Tracking and Reconciliation Demonstration
Breakout Session 3:
Thursday, May 31, 2018 @8:30-10:00 am
Budget Tracking & Reconciliation – Department Level
•
Welcome and Introductions
Bridgette Keene
•
Activity Explanation
Theresa Aitchison
•
Budget Tracking & Reconciliation
Maria Rasimas
•
Shadow Budget System Demo
Kim Kamrath
•
CHABSS Case Study
Bridgette Keene
•
Q&A Session
CSUSM Panel
•
Breakout Session Survey
Participants
•
Complete Breakout Session Activity
Participants
Breakout Session 4:
Thursday, May 31, 2018 @10:30-12:00 pm
Budget Tracking & Reconciliation – College Level
•
Welcome and Introductions
Bridgette Keene
•
Activity Explanation
Theresa Aitchison
•
Budget Tracking & Reconciliation
Maria Rasimas
•
Shadow Budget System Demo
Amy Armstrong
•
Q&A Session
CSUSM Panel
•
Breakout Session Survey
Participants
•
Complete Breakout Session Activity
Participants
Budget Tracking & Reconciliation
with PIVOT TABLES
By CSU San Marcos
How did we start using this Shadow
BUDGET
?
•
We previously used Quicken which had limited analysis
functionality and was more cumbersome with high volume of
salaries
•
Started with a basic Excel spreadsheet with pivot tables to
analyze the data - we added complexity over time as skills and
needs developed
•
We also adopted the power of pivots with other data sets such as
enrollment schedules, csv data downloads, HR actuals, etc.
Budget basics
•
What is a shadow budget?
•
A financial management tool to track expenses and revenue, much like a
checkbook ledger
•
Why use a shadow budget?
•
To reconcile revenue and expenses against “the bank” in our case,
DATA
WAREHOUSE
, preferably monthly
•
Management of faculty and department funds, salaries, etc.
•
Ease of financial forecasting or projecting through fiscal year end
Why is it Important to
RECONCILE
?
•
Know what current financial status is in ‘real’ time
•
Protects the resources of the University by being good stewards
•
It’s an audit requirement
•
Examples of why we need to reconcile:
•
Errors such as transposing numbers and charging incorrect departments
•
Errors in budget allocations
•
Chargebacks or transfers that we weren’t informed of
Why is it important to forecast?
•
Forecasting is a
PROJECTION
of the total revenues and expenses
through the end of the fiscal year.
•
We know if the funds will end the
FISCAL YEAR
“on target” and in good
financial health
•
We can tell exactly how much funding we have left to spend at any time,
department funds, faculty funds, etc.
•
Easy mid-year reporting (if required)
Using Excel with Pivots as your budget
shadow to reconcile and forecast your budget
Advantages:
•
Customizable – add columns as needed for additional analysis
•
Accessible – no licensing fees, most people have access to Excel
•
Shareable – just email
•
Increases Excel skills overall – more than just budget
•
Reduces the need for multiple data entry spreadsheets
•
Efficiency – quick and easy analysis in multiple ways
Excel basics to get to know:
EXCEL tools used in the shadow budget samples:
•
Format as Table
•
REFRESH
•
Quick Access Toolbar
•
Validation Table
•
Conditional Formatting
•
Pivot Table and Pivot Table tools
•
Slicer
•
Filter
Format as a Table
•
Using the table format in your Excel spreadsheet eliminates the
need to insert lines. Filter to your fund or department and just
add on the bottom
•
Totals at the top include only what is filtered.
Favorite Excel Quick Access Toolbar
For convenience add the following to your toolbar (below the
ribbon for easy access):
•
Save
•
Refresh All
•
Filter
•
Clear Filter
•
•
Insert Pivot Table
VALIDATION TABLES
•
What are they?
•
A set of data that restricts the values allowed in a cell
which reduces data entry errors
•
Creates drop down boxes to choose
from rather than manual data entry
Conditional Formatting
•
We use conditional formatting in the template to color code
funds and scenarios for easy visual of your funds. It’s a great
tool to know!
Pivot Tables
•
Pivot tables are a preset query of the data (filter on steroids)
•
All analysis of the data is done with pivot tables from one
spreadsheet
•
Make as many as you need, examples:
By Account to reconcile
By Category to review with supervisor
By Employee to track salaries
•
Pivot Tables Tools include Design and Analyze
•
Design is cosmetic for visual clarity
•
Analyze includes changing data source, adding slicers (filters on steroids)
SLICER
and Filter
•
Slicers
•
Filters
Use in data entry ledger or pivot tables to filter and analyze data
Excel and
Pivot Table
Training available
Seminars/Webinars
Pryor Seminars
https://www.pryor.com/
SkillPath
http://www.skillpath.com
onlc Training Centers
https://www.onlc.com
Free Online
You Tube:
MyOnlineTrainingHub.com
Myexcelonline.com
CSU Campus Online
Learning/Skillport
On Campus training
Use knowledgeable staff on
campus for workshops
Hire Professional Excel Trainers
for your group
Request Budget Office Support
And on to demo the shadow budgets…
Presentation by CSU San Marcos
Amy Armstrong, Theresa Atchison, Kim Kamrath, Bridgette Keene, Maria Rasimas
May 31, 2018
Shadow budget samples are available at the below website along with the presentations. These are merely
samples, each shadow budget can be customized as needed to your own unique needs.
WEBSITE:
https://www.csusm.edu/par/budgettrackingandreconciliation.html\
Major difference between Department and College Shadow budget samples provided:
Department level manages a high volume of faculty funds
College level manages a high volume of salaries
Time for Q&A will be available after the demo
Explore the implementation of a shadow budget system for effective expense tracking and revenue management. Learn about the transition from traditional financial tools to pivot table analysis for enhanced data processing. Discover the importance of reconciling financial data in real time to ensure stewardship of resources and compliance with audit requirements.
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
Breakout Session 3: Thursday, May 31, 2018 @8:30-10:00 am Budget Tracking & Reconciliation Department Level Welcome and Introductions Activity Explanation Budget Tracking & Reconciliation Shadow Budget System Demo CHABSS Case Study Q&A Session Breakout Session Survey Complete Breakout Session Activity Bridgette Keene Theresa Aitchison Maria Rasimas Kim Kamrath Bridgette Keene CSUSM Panel Participants Participants
Breakout Session 4: Thursday, May 31, 2018 @10:30-12:00 pm Budget Tracking & Reconciliation College Level Welcome and Introductions Activity Explanation Budget Tracking & Reconciliation Shadow Budget System Demo Q&A Session Breakout Session Survey Complete Breakout Session Activity Bridgette Keene Theresa Aitchison Maria Rasimas Amy Armstrong CSUSM Panel Participants Participants
Budget Tracking & Reconciliation with PIVOT TABLES By CSU San Marcos
How did we start using this Shadow BUDGET? We previously used Quicken which had limited analysis functionality and was more cumbersome with high volume of salaries Started with a basic Excel spreadsheet with pivot tables to analyze the data - we added complexity over time as skills and needs developed We also adopted the power of pivots with other data sets such as enrollment schedules, csv data downloads, HR actuals, etc.
Budget basics What is a shadow budget? A financial management tool to track expenses and revenue, much like a checkbook ledger Why use a shadow budget? To reconcile revenue and expenses against the bank in our case, DATA WAREHOUSE, preferably monthly Management of faculty and department funds, salaries, etc. Ease of financial forecasting or projecting through fiscal year end
Why is it Important to RECONCILE? Know what current financial status is in real time Protects the resources of the University by being good stewards It s an audit requirement Examples of why we need to reconcile: Errors such as transposing numbers and charging incorrect departments Errors in budget allocations Chargebacks or transfers that we weren t informed of
Why is it important to forecast? Forecasting is a PROJECTION of the total revenues and expenses through the end of the fiscal year. We know if the funds will end the FISCAL YEAR on target and in good financial health We can tell exactly how much funding we have left to spend at any time, department funds, faculty funds, etc. Easy mid-year reporting (if required)
Using Excel with Pivots as your budget shadow to reconcile and forecast your budget Advantages: Customizable add columns as needed for additional analysis Accessible no licensing fees, most people have access to Excel Shareable just email Increases Excel skills overall more than just budget Reduces the need for multiple data entry spreadsheets Efficiency quick and easy analysis in multiple ways
Excel basics to get to know: EXCEL tools used in the shadow budget samples: Format as Table REFRESH Quick Access Toolbar Validation Table Conditional Formatting Pivot Table and Pivot Table tools Slicer Filter
Format as a Table Using the table format in your Excel spreadsheet eliminates the need to insert lines. Filter to your fund or department and just add on the bottom Totals at the top include only what is filtered.
Favorite Excel Quick Access Toolbar For convenience add the following to your toolbar (below the ribbon for easy access): Save Refresh All Filter Clear Filter Email Insert Pivot Table
VALIDATION TABLES What are they? A set of data that restricts the values allowed in a cell which reduces data entry errors Creates drop down boxes to choose from rather than manual data entry
Conditional Formatting We use conditional formatting in the template to color code funds and scenarios for easy visual of your funds. It s a great tool to know!
Pivot Tables Pivot tables are a preset query of the data (filter on steroids) All analysis of the data is done with pivot tables from one spreadsheet Make as many as you need, examples: By Account to reconcile By Category to review with supervisor By Employee to track salaries Pivot Tables Tools include Design and Analyze Design is cosmetic for visual clarity Analyze includes changing data source, adding slicers (filters on steroids)
SLICER and Filter Use in data entry ledger or pivot tables to filter and analyze data Slicers Filters
Excel and Pivot Table Training available Seminars/Webinars Free Online On Campus training Pryor Seminars You Tube: Use knowledgeable staff on campus for workshops https://www.pryor.com/ MyOnlineTrainingHub.com Myexcelonline.com Hire Professional Excel Trainers for your group SkillPath http://www.skillpath.com CSU Campus Online Learning/Skillport Request Budget Office Support onlc Training Centers https://www.onlc.com
And on to demo the shadow budgets Major difference between Department and College Shadow budget samples provided: Department level manages a high volume of faculty funds College level manages a high volume of salaries Time for Q&A will be available after the demo Presentation by CSU San Marcos Amy Armstrong, Theresa Atchison, Kim Kamrath, Bridgette Keene, Maria Rasimas May 31, 2018 Shadow budget samples are available at the below website along with the presentations. These are merely samples, each shadow budget can be customized as needed to your own unique needs. WEBSITE: https://www.csusm.edu/par/budgettrackingandreconciliation.html\
