Budget Tracking & Reconciliation with PIVOT TABLES

Budget Tracking & Reconciliation with PIVOT TABLES

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 Its 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 werent 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. Its 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 Pryor Seminars You Tube: https://www.pryor.com/

MyOnlineTrainingHub.com Myexcelonline.com SkillPath http://www.skillpath.com onlc Training Centers https://www.onlc.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 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\

Recently Viewed Presentations

  • Chapter 2: Early River Valley Civilizations, 3500 BC - 450 BC.

    Chapter 2: Early River Valley Civilizations, 3500 BC - 450 BC.

    Chapter 2: Early River Valley Civilizations, 3500 BC - 450 BC. ... Important Class Notes on Ch 2 Sec 1:I. Sumerian Science and Technology. First Civilization to use the wheel, the sail, Bronze, and the plow. ... 3500 BC -...
  • Banner 9 Admin Pages RECEIVING

    Banner 9 Admin Pages RECEIVING

    REGULAR VS STANDING ORDERS. 3-way Match vs Workflow. Banner 9 Admin Pages - Index Field. Some departments or campuses were able to use Index (which housed Fund/Organization/Program)
  • The Internet Real-Time Laboratory (IRT) http://www.cs.columbia.edu/IRT Prof. Henning

    The Internet Real-Time Laboratory (IRT) http://www.cs.columbia.edu/IRT Prof. Henning

    Rescue service for web servers experiencing 15 minutes of fame Keren Bergman Andrew Campbell Ed Coffman Predrag Jelenkovic Angelos Keromytis Aurel Lazar Nick Maxemchuk Vishal Misra Jason Nieh Dan Rubenstein Henning Schulzrinne Xiaodong Wang Yechiam Yemini RTSP sipum Cisco 7960...
  • PowerPoint-Präsentation

    PowerPoint-Präsentation

    Warehouse and storage solutions for all kinds of wind energy components. Worldwide capabilities in more than 50 countries. A dedicated expert team for wind energy warehouse solutions. Services provided: Warehouse services for small and big components (Nacelle, Generator, Blades,..) Warehouse-cranes...
  • Monitoring CO Poisoning with the Rad-57TM Includes a

    Monitoring CO Poisoning with the Rad-57TM Includes a

    This program is not meant as a substitute for a program or course of study in carbon monoxide recognition and treatment, or emergency medicine. This is for review only. Please refer to your physician or local Medical Director for approved...
  • Evolutionary Neural Networks 1 Backgrounds Why NN+EC? Evolving

    Evolutionary Neural Networks 1 Backgrounds Why NN+EC? Evolving

    Microarray technology Enables the simultaneous analysis of thousands of sequences of DNA for genetic and genomic research and for diagnostics Two Major Techniques Hybridization method cDNA microarray/ Oligonucleotide microarray Sequencing method SAGE * * * * * * * *...
  • Smart Louisville Transportation Innovation in the 21st Century

    Smart Louisville Transportation Innovation in the 21st Century

    The foundational guideposts for the 2040 Comprehensive Plan. Plays. Infrastructure/Strategy. Incorporate AVs into our strategic outlook. Partnerships. Be included in key conversations & partnerships. Parking. Prepare for future with less parking demand. Transit.
  • CAUTI Indications

    CAUTI Indications

    As a substitute for nursing care of the patient or resident with incontinence As a means of obtaining urine for culture or other diagnostic tests when the patient can voluntarily void For prolonged postoperative duration without appropriate indications (e.g., structural...