Tracking Multiple Budgets in Excel

25 03 2010

One of my favorite jobs in theatre is as a production manager. I enjoy scheduling, solving problems and yes tracking budgets. One way to track multiple budgets is to use and excel spread sheet. Here are some tips on how to make a budget sheet but also how to make that information available in an easy to read format.

First let’s start with tracking a single budget line and ask information do we want to track as we enter expenditures. I like to make a single budget line for each department and for each production or maintenance line.

Lighting Budget R&J

Date Purchase # Purchased From Item Purchased Cost Budget Spent Budget left
Starting Budget $300.00
4-Mar 1 BMI Supply Gel $78.50 $78.50 $221.50
10-Apr 2 Limelight gobos $50.00 $128.50 $171.50
11-Apr 3 APG instrument rental $100.00 $228.50 $71.50
15-May 4 home depot practices $60.00 $288.50 $11.50
$288.50 $11.50
$288.50 $11.50
$288.50 $11.50
Totals $288.50 $11.50

To start set up the columns you want to track leaving the last two columns for what was spent and what was left. Excel makes the math easy and quick. To get Excel to do any math start the cell with =. So if in the example above it we want to add the cost of the item to what we have spent so far type in equals then click the cost cell then the cell above to get the new total spent. To figure the budget left column, take the cell above and subtract the cost of the item. This will give you a running total of this line. You don’t need to enter the formula for every cell. You can copy the first cell then highlight the rest in that column and paste. Excel will recognize the pattern and shift the cells appropriately. The other method create the formula is to highlight the cell or cells you want to copy then put your cursor on the bottom right corner and wait for it to turn into a +, then drag

1 Date Purchase # Purchased From Item Purchased Cost Budget Spent Budget left
2 Starting Budget $300.00
3 =E3+F2 =G2-E3
4 =E4+F3 =G3-E4
40 Totals

*Tip– Highlight ever other row it make it easier to read the rows.

Once you get the budget line for the first show or budget line you can then copy and paste it on the sheet for multiple budgets or you can crest a worksheet for each one. To see a different worksheet go to the bottom of the page in Excel and there will be one to three tabs labeled Sheet 1, Sheet 2 and Sheet3. If you click the taps you move to a new work sheet.  You can right click the tab in order to rename it or create a new sheet. Once you create a new sheet just copy and paste the cells from the previous budget line.

Once you have each individual budget I like to make a cover sheet that gives me easy access to all the budget numbers.

Budget Breakdown
Spent Left Budget
Lighting and Sound
Odd Couple $0.00 $100.00 $100.00
Christmas Carol $298.25 $1.75 $300.00
Dance Ensemble $0.00 $250.00 $250.00
Rent $2,333.51 -$2,287.24 $46.27
Hamlet $0.00 $400.00 $400.00
Supplies/Rental Equipment $1,202.83 $2,447.17 $3,650.00
$3,834.59 $911.68 $4,746.27
Projections $0.00 $1,000.00 $1,000.00

*tip-Highlight all the cells that deal with money and right click and choose format. Change the format to currency.

In order to get the numbers from the previous sheets, select the cell where you want the information, put in the = then click the tab of the sheet where the info is stored. Once on that sheet double click the cell you want to reference and you will return to the cover sheet. Once you do this anytime you add a new purchase it will be updated on the cover page. Once you have all the individual numbers you will need to total them in order to get a sense of the overall budget. To total a column or row you need to enter the formula =sum(first cell:last cell) or you can hit the S symbol on the toolbar and it will add this formula for you.

Spent Left Budget
4 Lighting and Sound
5 Odd Couple =Lighting!E34 =Lighting!F34 =C5+B5
6 Christmas Carol =Lighting!L34 =Lighting!M34 =C6+B6
7 Dance Ensemble =Lighting!S34 =Lighting!T34 =C7+B7
8 Rent =Lighting!Z34 =Lighting!AA34 =C8+B8
9 Hamlet =Lighting!AG34 =Lighting!AH34 =C9+B9
10 Supplies/Rental Equipment =Lighting!AN34 =Lighting!AO34 =C10+B10
=SUM(B5:B10) =SUM(C5:C1) =SUM(D5:D10)

Once you get the file set up its always good to go back and make it look pretty, bold the sections you need to and then pint a report to see what it looks like. Another way to get new ideas is to ask how others use excel and copy and paste the code they use. Here is a sheet I use with fake numbers for the year.




One response

25 03 2010
Jim Link

My boss tracks all of our budgets for each show and the supply budget on Excel. It’s really fast and convenient.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: