+ Reply to Thread
Results 1 to 10 of 10

Budget Tracking Charts

  1. #1
    Registered User
    Join Date
    08-21-2014
    Location
    Utah
    MS-Off Ver
    2010
    Posts
    5

    Budget Tracking Charts

    Hello everyone. I'm trying to accomplish something that I feel should be fairly simple, but I have very little experience with charts in Excel. I've attached an Excel file that is a basic layout of what my actual workbook looks like. I can't get to the actual one because it's at work, and we can't post to forums there >.<. In any case, it's the same concept. I've also attached two pictures that show the most basic concept of how I would like the charts to display the data. Okay, here's what I'm trying to do:

    We track each and every transaction throughout the year and categorize it based on the type of expenditure (travel, office supplies, etc). Each category has it's own sheet within the workbook. The transactions have a date in the far left, displayed in DD MMM YY format. In a separate sheet, I want to be able to add together all charges based on what month they occurred in. Once all of that is plugged in, I want it to reflect on two charts. The first will show each month on the X-axis and the $$ on the Y-axis. Also on this chart will be a line representing the $$ budgeted for that month for easy comparison. The second chart is a little simpler. It will just have a line representing the total quarterly budget and a bar that represents the total expenditures for that quarter.

    Thank you in advance for any help offered.
    Budget.xlsx

    Qtrly_Chart.png

    Qtrly_Summary.png

  2. #2
    Registered User
    Join Date
    02-18-2013
    Location
    Florida, USA
    MS-Off Ver
    Excel 2013+
    Posts
    18

    Re: Budget Tracking Charts

    I have put in some info to get you going. Hopefully you can improve up on it by experimentation.
    Suggested improvements.
    (1) Put all your transaction data in a table. This will make all formula association dynamic.
    (2) Add a Calendar tab (this is hard to convince people to do) with all your calendar information and formulas(ie each day of the year on separate row) and use it as a look table.
    (3) So the helper columns in the transaction data will be look up formulas instead of calendar formulas.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-21-2014
    Location
    Utah
    MS-Off Ver
    2010
    Posts
    5

    Re: Budget Tracking Charts

    mgd0903,

    Thank you so much for that information. I guess I was trying to make it a little too complex when all I need to do is add columns that serve the purpose of simplifying the formulas. Can you explain a little more how placing all data in a table is beneficial? By dynamic formula association, do you mean that new entries into the table will inherit existing formulas?

  4. #4
    Registered User
    Join Date
    02-18-2013
    Location
    Florida, USA
    MS-Off Ver
    Excel 2013+
    Posts
    18

    Re: Budget Tracking Charts

    The other way round, existing formulas that refer to your transaction data will inherit new entries. (When you convert your transaction data to a table)

  5. #5
    Registered User
    Join Date
    08-21-2014
    Location
    Utah
    MS-Off Ver
    2010
    Posts
    5

    Re: Budget Tracking Charts

    Okay, I almost have the perfect product but I ran across an issue. In the cell that adds the transactions according to the month (Column K in the "Charts" sheet that mgd0903 provided), I need it to update when I filter the source sheet ("Supplies" in this case). Here's what I currently have in there:

    =SUMPRODUCT(--(Supplies!$B:$B=J2),SUBTOTAL(109,OFFSET(Supplies!$I:$I, ROW(Supplies!$I:$I)-MIN(ROW(Supplies!$I:$I)),0,1)))

    The formula does what I want it to (matches the month and adds the total $ amount, and updates with filters), but it slows down my workbook dramatically, even if I try to make an entry that doesn't affect the range. Is there a better solution?

  6. #6
    Registered User
    Join Date
    02-18-2013
    Location
    Florida, USA
    MS-Off Ver
    Excel 2013+
    Posts
    18

    Re: Budget Tracking Charts

    (1) Covert your transaction data to a table. This will make all formula association dynamic. - Follow this advice. It is based on experience. Then let me know.
    So all your references will be table references. The formula should still work, but much faster.

  7. #7
    Registered User
    Join Date
    08-21-2014
    Location
    Utah
    MS-Off Ver
    2010
    Posts
    5

    Re: Budget Tracking Charts

    I actually had already taken your advice...the data is in a table. Still unbearably slow.

  8. #8
    Registered User
    Join Date
    08-21-2014
    Location
    Utah
    MS-Off Ver
    2010
    Posts
    5

    Re: Budget Tracking Charts

    Okay... I just realized that I wasn't referencing the tables, but the entire column. Changed the references to Table[Column].

  9. #9
    Registered User
    Join Date
    02-18-2013
    Location
    Florida, USA
    MS-Off Ver
    Excel 2013+
    Posts
    18

    Re: Budget Tracking Charts

    Correct. The formula should still work. Just try it.

  10. #10
    Registered User
    Join Date
    09-10-2013
    Location
    indonesian
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Budget Tracking Charts

    Budget.JPG

    Budget2.JPG
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 10-30-2014, 04:00 PM
  2. [SOLVED] Budget Meter without using charts.
    By hanzechin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-26-2013, 03:23 PM
  3. Replies: 4
    Last Post: 08-28-2011, 09:24 PM
  4. Tracking expenditures by budget lines
    By Ravaan69 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-22-2008, 03:53 PM
  5. Budget Tracking Spreadsheet
    By khibullimore in forum Excel General
    Replies: 2
    Last Post: 12-03-2007, 10:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1