Free Microsoft Excel 2013 Quick Reference

Budget versus actual formula

Have a spreadsheet with column headers for each month and row headers of budget items (such as electricity expense) as well as a column for the monthly budget figures for each expense item. Each month I enter the individual expense item figures and it automatically sums the row of figures and puts it in the cell to the right in the column labelled "Actual Year-to-Date".
My problem is what formula do I put in the cells with the column marked "Budget Year-to-Date" because each month it would be different. Right now I use a formula that multiplies the cell that contains the monthly budgeted amount for the item times the number of months that have gone by, so each month I need to remember to change the number of months and copy the formula down the column. For example instead of B3*5 for May, in June, I will need to change the formula to B3*6. There must be a better way to do this!


Post your answer or comment

comments powered by Disqus
We have quite a few spreadsheets that have Budget schedules which are created
at the end of each year and then as the year goes by we fill in the Actual
dates. Now the Budget sheets look exactly like the Actual sheets only the
budget sheet is built using calculations and a proforma where as the Actual
sheets are simply hard coded dates. Now we have a report that has formulas
looking at both to tell us if something is behind schedule according to the
budget sheets compared to the actual. However, if you remove one column or
row from either Budget or Actual you break quite a few formulas. Is there a
better way to build the report so that it does not break? We quite frequently
have to cut out or add new columns or rows during the year.

Hi,

I'm trying to make a dashboard that is to illustrate how the actuals are compared to the budget.

Based on a set interval the actual post will be colored either:green (x% better than budget)yellow (y% better or worse than the budget)red (z% worse than the budget)
My problem is how to handle figures that are negative.

Scenarios:Actual is positive and Budget is postiveActual is negative and Budget is positiveActual is postive and Budget is negativeActual is negative and Budget is negativeActual is zero and Budget is positiveActual is zero and Budget is negativeActual is positive and Budget is zeroActual is negative and Budget is zeroActual is zero and Budget is zero
In addition to this the polarity can be changed. Eg. if the post about costs then lower than the budget is postive, and if the post is income then higher than the budget is postive.

The current formula is now in three steps:
1. Calculate the difference between budget and actuals in %
2. Check the polarity (e.g. a cost or an income) and then turn the result of #1
3. Based on results of #2 - check whether to color the cell green, yellow or red based on the intervals set

I hope you guys have a better and easier solution to handle such an issue. Thanks

I have a PivotTable with actuals and budget figures for each item. PivotTables make it easy to switch between those two, or to see the differences.

But is it possible to get a view where everything until this month is actual figures, but everything in the future uses budgeted figures on the same line or graph? If I choose both budget and actual, I get sums or other calculations. Graphing the budget and actual together is ok, but it would be nice to have a single continuous line with projections into the future, instead of two separate lines.

Thanks!

I've got a spreadsheet with 1500+ rows that I am going to need to share to about 10 other users. It's a standard budget versus actual kind of spreadsheet. Before I share it, I want the budgeted columns (plus a few formula columns) protected so they don't muck it up by mistake (or on purpose!). Each of the 10 users will only be interested in their own rows, so I needed to have the autofilter so that they could choose to see only their own data.

I can get that to work just fine by choosing the Allow AutoFilter on the Protection menu. The problem I have now is that I can't use the "Sort Ascending" or "Sort Descending" option in the autofilter. And yes, I did check the Sort option in the Protection menu.

I get the standard: "The cell or chart you are trying to change is protected and therefore read-only....."

What am I doing wrong? It won't sort on protected or unprotected columns. For that matter, I can't do a plain "Data, Sort..." either. The sorting isn't a requirement for the spreadsheet to work, but I think it will make it much easier to use if they can sort by date. Any ideas? Or will Excel just not allow you to sort within autofilter when it's protected?

Thanks in advance for your help!

Is there an FAQ that tells me how I can create month-by-month stacked bar
charts of plan versus actual? I need to plot a set of products (plan versus
actual), and each month I would have an additional pair of stacked bars.

Thank you.

Hi,

I'm a project manager for a facility that re-builds large industrial machines. I have developed a system for budgeting our work that seems to work great, but getting budget vs. actual reports has been a stumbling block for us. Our company uses Oracle E-business suite for our resource management, but it's not customized for our purposes and I can't format my own Oracle reports due to beaurocratic reasons. So, I'm left to fend for myself out here and try to come up with my own system for reporting (which I'm happy to do, but I'm constantly working with one foot in unfamiliar territory here, and time constraints as well).

The way my system currently works is that I have developed a budget scheme that works with the way that day is entered into Oracle. So my output data from the actual labor and materials commited to work parrallels my input budget data.

I'm right on the cusp of the solution, but I'm not sure where to turn for the next step: the Budget Vs. Actual report.

The budget data comes from an excel file currently, but I have formatted it as an Access table as well. The actual data is exported from Oracle Projects. I have several options for exporting the raw data (the option I'm leaning towards is a SQL server the company has set up that can query the Oracle Projects tables).

I've set both of these data sources up as tables and have set up pivot table views in access to help see the organization of the relevant data.

The heirarchy of the actuals data is:

WipName (this is the work order name)
OperationSequenceNumber (this is the task code)
ExpenditureType
Group1 (labor transactions)
Sum of Quantity (hrs)Group2 (material transactions)
Sum of CostAmount ($'s)The budget data is as follows:

Unit#
WipName
OperationSequenceNumber(& description)
Budget Amount for Labor Transactions (hrs)Budget Amount for Material Transaction ($'s)
What I'd like to do is mash these together into a report that shows:

Unit#
WipName
OperationSequenceNumber
Mat'l Budget - Material Act'l - Variance - Lab. Budget - Lab. Act. - Var.This seems like it should be really easy to do, but I'm not sure where to start. I'd like to get it working in Access, and then migrate to SQL reporting services so I could put this report on my SharPoint site. Or perhaps I just need to upload the access DB to Sharepoint and use SQL reporting services to define and run the report.

Does anyone know how this report would be made, or know of any resources that I could go to for perhaps setting the report definition up in SQL reporting services?

Any help is greatly appreciated!!! I've attached my simple Access database for reference.

Hi,

I am using VB.Net (Interop) to reference an Excel Workbook, and want to
retrieve the actual formula string rathar than the result of the formula.

So if the formula in Excel was =SUM(B5:B8) then the string I would want to
return would be "=SUM(B5:B8)"

I have tried almost every property but still can only seem to get the value.

Any help would be much appreciated.

Thanks in advance,

Grant.

I have a worksheet on the last page of my workbook that keeps track of all the hours worked in different categories, and it has a place for the budget hours that should have been worked. I would like to keep a bar graph on the last page, comparing budget to actual. Most importantly, I would only like those categories that actually have entries made on them to show up on the chart, in order to cut down on clutter and make it easier to read. Any suggestions on how to have the entire data set selected, but to just get the ones with numbers to show up on the graph? Any suggestions on how to make an actual vs. budget bar graph look really professional and easy to read?

My ERP system is SAP. Every month, we download budget versus actual information for analysis. The data feeds to a summary spreadsheet. To locate new general ledger account numbers that come on during month, we have a list of standard account numbers in column A in Excel 2007. We copy and paste the data out of SAP. We then check to see if new account numbers have come into play by scanning down Column A and making sure the account number in that column matches the account number in the column just downloaded.

A very manual effort to say the least.

434245 434245 Sale Cap-Vehicles
434505 434505 PY Expend Recovery
* 434 - Miscellaneous & Other
441010 441010 Signatory Landings 69,357,014.00-
441015 441015 Carrier Incentive Program 334,394.00-
441020 441020 Aviation Fuel Revenue 217,250.00-
441030 441030 Aircraft Parking Revenue 1,509,750.00-
* 441 - Landing Area 71,418,408.00-

If the account numbers don't line up, we have to figure out what was added.

Is there a simpler way to do this? I've attached an example.

My worksheet has 50 rows showing sales for each book. It has 39 columns - 3
for each month - budget, actual and diff - and 3 more columns for YTD. They
are like this:
Jan-Budget Jan-Actual Jan-Diff Feb-Budget Feb-Actual Feb-Diff
etc.

I have budgets for each month for each book on the worksheet already. I
update it monthly to enter actual sales for the month. I want the YTD-Budget
column to only sum up months that have actual activities so I can do a fair
comparison with YTD-Actual.

I tried to create a formula that sums up the budget columns for months where
Total Actual for the month is 0. But I didnt' get it right.

I'd appreciate if you can help me with that. Thanks in advance!

My worksheet has 50 rows showing sales for each book. It has 39 columns - 3
for each month - budget, actual and diff - and 3 more columns for YTD. They
are like this:
Jan-Budget Jan-Actual Jan-Diff Feb-Budget Feb-Actual Feb-Diff
etc.

I have budgets for each month for each book on the worksheet already. I
update it monthly to enter actual sales for the month. I want the YTD-Budget
column to only sum up months that have actual activities so I can do a fair
comparison with YTD-Actual.

I tried to create a formula that sums up the budget columns for months where
Total Actual for the month is <> 0. But I didnt' get it right.

I'd appreciate if you can help me with that. Thanks in advance!

Can anyone help save this working man's wallet?

I have two sets of monthly expense figures, budgeted (A1) and actual (B1). In C1 I have =IF(A1=0,0,B1/A1) to calculate how much in % the actual is over or under the budget without having a #DIV/0! if the budgeted is zero.

But if both the budgeted and actual are zero, it returns 0% instead of the correct 100% (on budget). What is the more complete C1 formula?

And, is there a graphical "wizard" feature in Excel that lets you add many IF-AND logical arguments so it builds the formula for you?

I have weekly budget report (for the entire year) that I put in a Pivot Table with approx 20 different row labels. The actual revenue numbers come only once a month (several different reports make up the actual revenue numbers).

I need to put the actual numbers on the same row as the budget numbers and I can't figure it out.

For example, for the first three months of the year, I have the pivot table showing everything perfectly. In February, I get January's actual revenue numbers that now "replace" the budget numbers for January. If this was a regular table, I could easily overwrite these numbers, but with the pivot table, I can't get these two fields to line up.

For now, I have the Actual Revenue numbers on the Rows above the Budget numbers for their respective months/quarters. Thanks.

Hi all,

A newbie here. I'm putting together a spreadsheet to track various budgets. I am stumped. What I want to do is...

I have two columns of data - projected budget and actual budget. (ie I am tracking actual funds committed vs actually invoiced). I have these in two columns. Under the 'invoiced?' column I have the cells with the default "N", which I will change to "Y" when an invoice is received and paid. I have used SUMIF to sum the 'Invoiced?' column total as a "N" is turned into a "Y". Well heres the question.

I then want a third column next to the 'invoiced?' column that will enter the actual date that the "N" is turned into a "Y". I have been playing around with the IF formula and trying to manipulate the TODAY formula to no result. So again I'm trying to have three columns for my budgets. The first is 'Projected', the second is 'actual', and I want a third that will show todays date when the cell is changed from the default "N" to a "Y"

Thanks in advance and i HOPE this makes sense.

Hello,

I have a VLookup solution to a YTD SUM/AVG "monthly" problem, but I think there might be an easier way.

I have an input sheet and a cell where I enter 1 for January, 2 for Feb, and so on, and this cell changes monthly.

Also there is a report sheet with Column "A" filled in with Jan -Dec and columns of data to the right. Because this sheet also contains budget and actual figures all 12 months are populated with data all the time. However, at the bottom of the data columns I have YTD formulas. One formula is YTD Sum and another for YTD AVG.

To avoid manually changing the Sum and AVG formulas each month I created a Vlookup table off to the right that calculates 12 months of Sum and AVG results. I then use the input page cell (for the month) to pick up the correct YTD Sum and AVG. This all works, but is there an easier formula to do the same thing?

Example
Monthly YTD avg =VLOOKUP(INPUT!$C$24,AVG_YTD,2)
Sum YTD =VLOOKUP(INPUT!$C$24,AVG_YTD,16)

The named range is AVG_YTD but contains data on all Sums and Averages for all months of the year.

Thanks,

Hi all.

I would like to chart actual vs budgeted expenditure in a bar chart in a single bar. By this I mean that if 70% of budget expenditure has been incurred, the bar should only be 70% filled.

So far my efforts have failed. Can anyone suggest a solution?

FD

So I was looking on another site which showed how to construct a Period table and join at a higher level than the date so one can make a calculation for month budget vs actual. I already have a fiscal table that is essentially a date column, with 52 fiscal weeks (numbered text), months, qtr with a year calculated in a column. Thing is, no one just does it for a month and I need to do it weekly for week and Qtr eventually recap the year.

However, the fiscal table I have is joined to sales data by date, not period. Of course, I don't want daily budgets to actual, I want to run weekly as a portion of quarterly budgets. I also would think I could create a calculated field to be able to use the date to supply the other fields. I guess what I'm trying to do is not add manually anything to the original data, which is in another spreadsheet on a shared drive. Trying to keep that very clean.

And further, I have to provide multiple measurements. Employees are linked to 6 reports, each has a budget. Right now I can get sales figures no problem all in the same pivot. But now I'm not sure if I could pull off having the budget vs actual right next to the sales data. That would be very nice and compact. Ideas on how to do this? Am I on the right track? What can I put in the budget table to make the quarter and week work in relation to the fiscal table and the sales data, which only has dates.

I previously asked for help with this:

In Excel2000, I would like to create a dynamic year-to-date formula. I
have the following data starting in row 3 and going from column A to
column X:

Jan Actuals Jan Budget Feb Actuals Feb Budget
100 200 300 400

I would like to use cell A1 as an input cell for the month ( ex. Feb =
2). I would like the formula to lookup the month and then tell me how
much Feb YTD Actuals are ( in this example = 400 ). All I want to
change each month is the input cell in A1 and have the formula
automatically give me the results.

Someone responded with this formula:

=SUMPRODUCT(--(MOD(COLUMN(OFFSET($A$2,,,,CHOOSE($A$10,1,3,5,7,9, 11,13,15,17,19,21,23))),2)=1),OFFSET($A$2,,,,CHOOS E($A$10,1,3,5,7,9,11,13,15,17,19,21,23)))

Can someone plaes explain this formula. It works perfectly but I don't
understand it.

Thanks

In Excel2000, I would like to create a dynamic year-to-date formula. I
have the following data starting in row 3 and going from column A to
column X:

Jan Actuals Jan Budget Feb Actuals Feb Budget
100 200 300 400

I would like to use cell A1 as an input cell for the month ( ex. Feb =
2). I would like the formula to lookup the month and then tell me how
much Feb YTD Actuals are ( in this example = 400 ). All I want to
change each month is the input cell in A1 and have the formula
automatically give me the results.

Thanks

Hi, What I'm hoping to do is to copy the =Hyperlink(A1,B1) result onto
a new cell with the actual linked text, but without the formula. So
if A1 has "http://google.com" and B1 has "link", then rather than
using the above formula (which will read the same on the cell), the
destination cell will read "link" hyperlinked to http://google.com
without the =hyperlink formula. The reason I want to do this copying
is to reuse the cells A1 and B1 for other entries. Can anyone help?
Thanks.
~michael

I have a budget file that gets a lot of its numbers from another source data
file and then it contains a lot of formulas. I want to save the budget file
using "save as" to save the budget in its current state. Then I want to make
some changes to the source data file and update my budget file again and then
"save as" again with the new data. My problem however is that all the files
that I do a "save as" keep changing everytime i change the source data. i
want to save the current numbers when I do a "save as" NOT the links to the
source data. How do I do this if someone knows I would appreciate it.
--
Frank M.

Hi!

I have one question. I would simplify the problem as follows:

- I want to compare actuals vs budget
- BUT Actuals are on monthly basis and my budget is annual
- I don't want to divide my budget or to work on a monthly budget
- I want to see in a pivot table the actuals detailed by month and the totals compared to my annual budget.

I join a file for example. The solution I tried was to insert a line in the 'database' dedicated to the budget. Is this the way you do it usually? I mean my problem is much more complicated and my excel file much much bigger, but I want to make sure I am on a good beginning before complicating things.

Thanks in advance for your opinions

I have a sheet with my budget categories. I have a budget total. I have another sheet that is my check register.

The register sheet has a categories drop down from which I can select a category from the budget sheet. On the budget sheet, I want a cell that calculates the actual spent based on the sum of all the rows for registry entries that match that category.

How woudl I do that? (Normally I would just use Access. But I don't have Access on one of my machines.)

Thanks

S

I am setting up several spread sheets. One is to Transport Costs, one is for
Food Costs, etc. One of the spread sheets will be a budget spread sheet for
the whole month. What I need to do, is have a way of drawing the totals of
the formulae used in the individual spreadsheets into the Budget spread sheet.

Eg.

Spread sheet 1 Spread sheet 2

A B A
B

Transport Food

1 Road Tax £180 1 Bread
£0.75
2 Insurance £800 2 Eggs
£1.20
3 Fuel £300 3 Orange Juice
£1.40
4 4 Butter
£0.87
5 Total =sum(b1:b3) 5 Milk
£0.80
6
7 Total
=sum(b1:b5)

Spread sheet 3

Budget Spread sheet

A B

1 Transport Costs Drawn from spread sheet 1, cell b5
2 Food Costs Drawn from spread sheet 2,
cell b7
3
4 Total Costs =sum(b1:b2)

My efforts up to now, have only managed to copy the actual formulae between
spread sheets, not the results of the formulae. I had no problems achieving
the results in Supercalc 5, but can't seem to do it in Excel 2003.

Help.


No luck finding an answer? You could always try Google.