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!

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!

- Budget vs. Actual
- Dashboard budget vs actuals (green, yellow or red)
- How to replace budget with actual figures in a PivotTable?
- Protection error when sorting in Autofilter
- Stacked bar charts of plan versus actual
- Budget vs. Actual report
- VB.Net - retrieve actual formula string
- Bar Graph - Compare Budget to Actual
- Excel 2007 and SAP downloads
- YTD Budget Sum if Actual Month has activities
- YTD Budget Sum if Actual Month has activities
- IF Formula for Percentage
- Show Budget VS Actual In Pivot Table
- Formulas : Please help - I'm stuck!
- YTD sum and AVG formula
- Budget Vs Actual expenditure in a single series bar chart?
- Actual vs Budget: multiple measurements, periods
- Dynamic year to date formula
- Dynamic Year-To-Date Formula
- Copying the Hyperlink function result without copying the actual formula
- Save As and save current numbers not the actual formulas and links
- Problem comparing annual budget and monthly actual expenses in pivot table
- Simple budget vs actual spreadsheet
- Using the results of formulae between different spreadsheets.

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.

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

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 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!

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.

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.

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.

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.

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!

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!

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 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.

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.

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,

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

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.

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

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

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

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.

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

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

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.