I am trying to use excel to forecast budget expenditure for the fiscal year, using actuals from months past + unknown (future months).
I have this formula in cell G2: =IF((worksheet A!G70>0),F2,IF((B2
>D2),(B2-C2)*F6+F2,IF((B2
In the attached spreadsheet i have a budget amount, billed to date, %complete, %remaining and forecast figure. What i am trying to do is estimate the forecast spend vs the budget or billed to date and percent remaining. I am struggling with how to do this based on in some cases the budget is already overspent but the %complete is less than 100%. What i really want to do is create a forecast based on the billed to date or budget depending on which is greater and work out estimated spend based on whether the task is complete or there is still a % remaining.
View 2 Replies View RelatedI am trying to create a spreadsheet that will keep track of budget spent and budget remaining.
I need the values to take what is spent from expenses claim forms that are filled out,
Each budget category has a code and within that code there are sub heddings
I have project's start date and end date and total cost. I want to spread the total cost across month on top using the total number of days[calculated using the end and start date] that project will run and the number of days that project is in that month. I have highlighted the area in yellow where I want formulas to work.
View 3 Replies View RelatedI have is a cell with a begining balance and a cell witha current balance.
I also have a column in which each expenditure will be placed ina cell in that column in sequential order.
What i want to do is take the value in the first cell of the expenditure column and subtract it from the begining balance and the place the result in the current balance.
But after the first cell i will need to subtract each value from its previous value to continue counting down so to speak.
I am at a loss on how to move the last value which will also be the lowest value in the column to the cueent balance cell.
If I have a database of 1000 records that are logged and counted as to which of these records are successful and unsuccessful sales,
can excel be programmed to give an accurate forecast of what will be the number/ percentage of successful sales records over the next 100, 50, 30 and 10 new record entry ranges, using the database history?, then which of the new records within each selected range are most likely to be successful sales using the database history?
If so, what components or data references are needed in a worksheet to meet these requirements to allow for creating an accurate forecasting application within excel?
I'm trying to figure out how to do seasonal forecasting based on volumes. I tried to use FORECAST but the numbers are not accurate.
I have actual data to work with to come up with my forecasting on a monthly basis. Our yearly calender is from Nov 08 to Oct 09.
Here's a sample of the data....
I have a row of totals in a spreadsheet and I want to calculate a forecasted total based on the previous month's totals.
For example I have two months and I want to know how to calculate the forecasted Jun 09 total: ...
I am trying to come up with the appropriate formula that can be used for possible forecasting purposes. Countif proves to be futile due to more than one criteria that needs to be met. Sumproduct seems to be more feasible but I am unable to get the correct formula or maybe I am wrong.
Here's the objective...I need to be able to plot the total number of people working on a project on a daily basis - # of people working on a Monday, Tuesday and so forth by means of referring to start and end date column..I am open to revisions so as to streamline everything.
I do forecasting for my company. We have a forecast every quarter. Any good templates (simple or complex) for presenting different quarters (or time periods) and the actual results for management?
View 5 Replies View Relatedi was looking for some assistance in regards to a formula i have used for ranking. i have attached a sample spreadsheet of what i have done so far but struggling to work something so thot id try here.
(Sample WB) Currently i have managed to rank in accordance to column E but as i have alot of 0% i would like to be able to rank the 0% in accordance to the number of work done (column C).
For example i have 62 0% answers and Username Q has actioned 316 jobs with 0 Failures so should be ranked 1st as he has the highest number of jobs.
Is it possible if 0 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet A2 to A10 in work sheet Random Picks. And fill column D7 to D15 In sheet team matches with the contents of them cells.
If 1 cell is filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet B2 to B9 in work sheet Random Picks. And fill column D7 to D14 In sheet team matches with the contents of them cells.
If 2 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet C2 to C8 in work sheet Random Picks. And fill column D7 to D13 In sheet team matches with the contents of them cells.
If 3 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet D2 to D7 in work sheet Random Picks. And fill column D7 to D12 In sheet team matches with the contents of them cells.
If 4 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet E2 to E6 in work sheet Random Picks. And fill column D7 to D11 In sheet team matches with the contents of them cells.
If 5 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet F2 to F5 in work sheet Random Picks. And fill column D7 to D10 In sheet team matches with the contents of them cells.
If 6 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet G2 to G4 in work sheet Random Picks. And fill column D7 to D10 In sheet team matches with the contents of them cells.
Have to forecast/create a trend for the current year based on the data available for last two years. Below is the format of data. The current year data is kept blank as the data needs to be filled based on forecasting/ trending.
30-Apr-0931-May-0930-Jun-0931-Jul-098/31/20099/30/200910/31/200911/30/200912/31/20091/31/2010
2/28/20103/31/20106075443580901511256025304030-Apr-1031-May-1030-Jun-1031-Jul-108/31/20109/30/2010
10/31/201011/30/201012/31/20101/31/20112/28/20113/31/2011100150756070859611045663544
30-Apr-1131-May-1130-Jun-1131-Jul-1131-Aug-1130-Sep-1131-Oct-1130-Nov-1131-Dec-1131-Jan-1228-Feb-1230-Mar-12
I have a base model for site openings and opening schedule. I need to build a 5 yr model which reflects the base model for each opening. I have matched the month in the model to the opening month but how do I get the base model subsequent months to follow the opening month?
View 10 Replies View RelatedI only grasp the basics of loops and not any experience w/ formulas in VBA to date.
For each item in col "I" run this formula (the cell references are relative)
=RIGHT(I6,LEN(I6)-FIND(" ",I6))
I am only at this point so far:
Sub ItemName()
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer
Dim LRow As Long
Set ws = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator")
LRow = ws.Cells(Rows.Count, 9).End(xlUp).Row
For i = 6 To LRow
=RIGHT(I6,LEN(I6)-FIND(" ",I6))
End Sub
The basic function of the loop is to Delete the first term of each cell in the range including the first space.
example:
Enjoi Barletta Deck
Resulting in:
Barletta Deck
The will eventually end in a series of pivottables. I am currently at the step where I need to add vlookups. This is for an inventory management report that averages 20,000 rows and 20 columns of data. I add a few columns (using vlookups) to increase visibilty to the operations dept.
The first thing I need help with is creating the VBA language for using a vlookup table from another workbook that pastes the vlookup formula every cell to the bottom of the data
Then I need to utilize an IF formula for one column that says IF c2 is blank use vlookup-formula1, and IF it is not blank use vlookup-formula2, and then paste at every cell all the way down.
I hope someone can help, and I hope I didn't write too much to where everyone will skip pver this thread.
I am currently to this state, and ready to continue.
--------------------------------------------------
Sub OHRTODDI1()
'
' OHR1 Macro
' Macro recorded 7/22/2009 by iahopbxm
'
' Keyboard Shortcut: Ctrl+o
'
' Sort and Add column Headers
Cells.Select
Selection.ColumnWidth = 9.43...........
I get a "#value" error message when I utilize the formula noted below. I seperated it into distinct sections so that it is easier to view. Basically, what the formula is doing is determining whether if two values are the same, then take the absolute difference of the average of other values ,excluding one of the previously noted values, and compare that difference to a different parameter. If the difference does not exceed the parameter, then calculate the difference; otherwise the ending result is zero. Pls. note that the error seems to occur when I input the fifth section into the formula.
=IF(K23="","",IF(AND(K23=E23,ABS(B23-AVERAGE(F23:J23))>B20),B23-ABS(AVERAGE(F23:J23)-B20),
IF(AND(K23=F23,ABS(B23-AVERAGE(E23,G23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,G23,H23,I23,J23)-B20),
IF(AND(K23=G23,ABS(B23-AVERAGE(E23,F23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,H23,I23,J23)-B20),
IF(AND(K23=H23,ABS(B23-AVERAGE(E23,F23,G23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,I23,J23)-B20),
IF(AND(K23=I23,ABS(B23-AVERAGE(E23,F23,G23,H23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,H23,J23)-B20),
IF(AND(K23=J23,ABS(B23-AVERAGE(E23:I23))>B20),B23-ABS(AVERAGE(E23:I23)-B20),0))))))))
Current Formula
{=IF(AND(G144>0,F144:F173
the scenario is a spreadsheet for adding call centre operators evaluations (1 being worst and 5 being best) from the clients. in the spreadsheet, i need to create a function to add a quick evaluation based on the operators score from the client
so if the score is 4 or higher it would read "good - improving"
and if it is less than 4 "poor - improving"
i think this is just a simple IF question...but i have forgotten how to do it
Im doing a year nine maths project and need a little help with excel. This is my first time using macros/VBA. I have been playing around and have figured out a few things. I was asked to produce a game using excel, i am doing a kind of "racing" game with betting, i have the racing and most of the betting all working fine. I need to know how to get a message box to appear when the total of a cell reaches 0.
View 5 Replies View RelatedI thought about using it for forecasting purposes. I might try to use it together with live data (temperature, seasonal patterns)... but if I graph it, it starts from left to right...
How to modify the code a bit...
VB:
Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrDown
I tried to change the code, but it did not worked.
Forecasting using historical seasonally effected data
I organize my thoughts in numbers so it will be easier to understand.
[1] Say I have this data from January 2009 to December 2009:
1-4-7-10-13-14-15-16-18-20-22-25
As you can see, the growth is not linear.
[2] So then, I start with this data point for Janaury 2010.
26-a-b-c-d-f-g-h-i-j-k-l
[3] I now want to forecast that data point for the rest of 2009...using the growth in [1]. SO i guess that's an easy task right? Take the percentage change from Jan 08 and Feb 08...and apply that to the 26...and so on. (am i correct here at least).
[4] But lets say we have a GOAL. So, this become a forecast/plan. In December 2010, i want to grow double the amount of that in December 09. So 25 x 2 = 50. Now, the "known" data is like this:
26-a-b-c-d-f-g-h-i-j-k-50
So how do i do this now?
I have historical sales for each month from the last 3 years. I would like to set up a seasonality adjustment for forecasting. I know that it won't be perfect and some may even suggest using regression, but I'd rather not. I'd like to do the following:
In a control sheet, list the months in chronological order beginning in cell A1; January, A2, February; etc., etc. through December.
Next, below each month, I'd like to have a factor. For example, in B1 beneath January, I'd like to be able to plug in 75%. This would say that for each January going forward, that it is 75% of the annual historical average for all periods. Whereas, say in June, it is 140%.
I was wondering if someone could help me generate a Macro to do the following:
I have a sheet with the following characteristics.
Column A, rows 8-15 contain headers
Column A, rows 17-24 contain headers
Column A, rows 26-40 contain headers
Column B to CV, row 6 may or may not contain an "X"
Column B to CV, rows 8-15 & 17-24 & 26-40 may or may not contain the various data
I need a macro which does the following on the press of a button located somewhere on the sheet:
Generate a .TXT file in the folder C:Test with the name "Test_YYYY_MM_DD_HH_MM_SS_Full.txt" which uses the system time and date to fill in the values In the TXT file the following data should be created: ....
i try to lock cells in the area the VBA code affects. My goal is to actually have affected cells by this VBA code lock immediately after anything is entered in the affected cells. Area needing Debugging is in the If - Else portion. My worksheet will be protected.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LLoop As Integer
Dim LTargetRange1 As String
Dim LDestRange1 As String
LLoop = 10
While LLoop <= 1000
'Link column B to A
LTargetRange1 = "B" & CStr(LLoop)
LDestRange1 = "A" & CStr(LLoop)
If Not Intersect(Range(LTargetRange1), Target) Is Nothing Then
If Len(Range(LTargetRange1).Value) > 0 Then
Range(LDestRange1).Value = Date
Else
Range(LDestRange1).Value = Null
End If
End If
LLoop = LLoop + 1
Wend
End Sub
how I would go about setting up a spreadsheet that will allow me to look at previous orders and forecast what I need to buy. He never got into trouble for ordering too much, but he did get caught on ordering too little. I am thinking something along the lines of the attached. However, if anyone is using something that looks or works better (or those who solve this little problem have done a better looking or working sheet) let me know.
There will be some Vlookups that need to bring in description and price (or some other function if there is a better way to do it). The min need and max need will be populated and brought in by vlookup. The order column should be where the forecasting happens based on previous orders. I am willing to have all the data in the same workbook. The second or third sheet can be used to store data in what ever format that is needed to be done.
I'm trying to do a few things actually. I am somewhat a newb at programming, although I do understand how C++ and the coding works within Excel. To me, it's like the English language...knowing all of the vocabulary and formatting it right is important. If I don't know the vocabulary, I don't know what options I have.
This tool is being used in the call center I work in. It is to help eliminate repititious information used to notate accounts. I have attached a picture of what the tool looks like so you can have a better idea of what I'm working with. Here are a list of things I'm trying to do which I have searched and not found answers to:
1. I am trying to use a middle mouse scroll to scroll through validation list. This list information is located on a different locked tab. Basically, I have a few rows where a drop down selection can be made and it would be easier to scroll rather than manually moving the slider within the validation list.
2. As you can see, this "tool" an employee and I have created has many buttons. What I would like to do is to be able to click a button on the right and have it "stack" text within a single cell. In other words, if I click the button once, the text may be entered into the "actions taken" cell. If I click another button, I can have the option (or maybe a little "+" next to the button) to add this button's information to the end of the information already entered within the "actions taken" box. I've tried recording and using the keyboard and selecting "end" and then having the text added, but it didn't work. I've seen "loop" VBA code, but I don't want something to repeat, I want it to stack in the cell when clicked manually.
I'm sure there is a way to make the tool much better using VBA code and not use Excel, but I don't have an extensive knowledge of Excel to do something like that. I'm a very fast learner and understand how the coding works, so if you have an idea which may help, and you understand what I'm trying to do here, please provide your .02.
Basically, it's used so the Customer's name and phone number are entered manually, and then just about everything else is automated by the use of the buttons on the right. If I could get the 2 above options to work, it'd be perfect. I just figured out I could eliminate screen flicker and hide the macros working by using:
We have hourly amounts remaining on projects, we want to project cost at 40 hour work weeks and 50 hour work weeks. We simply multiply the 40 hour budgets times the straight time rate, but we need to calculate the over time on the 50 hour budgets.
examples:
600 hours on 40 hour budget at $25 and hour is $15,000. However on a 50 hour budget it would include 480 straight time hours and 120 overtime hours for a budget of $16,500.
Our data is hourly bugets in column "A". In column "B" we did =A3*straighttimerate. Is there a formula that can calculate 40hrs straight time then 10hrs over time until the budget runs out? We are working with Microsoft Office Proffesional 2007 with Excel 2007 but need to remain compatible with Excel 2003.
Budget (hours)straight time ($25)over time ($37.5)240$6,000.00?4652$116,300.00?152$3,800.00?258$6,450.00?645$16,125.00?
finding a way to compare the two budgets i.e 08/09 and 09/10 and if a new cost centre appears in 09/10 it will bring a yes in column G(New cc) and if the Cost Centre already exist in 08/09 it will bring a blank--but in both cases i want a variance in the next column H i.e 09/10 less 08/09.
View 2 Replies View RelatedI have to run payroll every two weeks, and part of the process involves calculating overtime for over 50 employees. The total amount of overtime hours for each employee is usually split on two or three budgets and after I calculate the budget splits, I have to enter these amounts into the payroll system. (The payroll system takes up to 2 decimal spaces.)
I have a spreadsheet set up for each employee, but what I've noticed is that the sum of the parts does not always equal the whole.
For example, Bob Goodworker worked 9 hours of overtime this pay period.
His budget split will be as follows:
Budget A (58.5%) = 5.27
Budget B (31.5%) = 2.84
Budget C (10%) = 0.90
However, 5.27 + 2.84 + .90 = 9.01
The payroll system only allows for numbers up to 2 decimal places.
