Forecasting Setup
Sep 14, 2006
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?
View 9 Replies
ADVERTISEMENT
Apr 2, 2009
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....
View 12 Replies
View Related
Jul 15, 2009
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: ...
View 10 Replies
View Related
Oct 19, 2009
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.
View 5 Replies
View Related
Dec 27, 2013
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 Related
Nov 21, 2011
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
View 3 Replies
View Related
Jun 19, 2008
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
View 9 Replies
View Related
Dec 8, 2008
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 Related
Sep 21, 2013
I 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.
View 8 Replies
View Related
Jan 15, 2010
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?
View 9 Replies
View Related
Jul 16, 2007
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%.
View 9 Replies
View Related
Apr 11, 2007
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.
View 8 Replies
View Related
Aug 23, 2007
Basically, i've got 90 players on a ladder. When one beats the other they replace them in their position and the beaten player moves down one.
Has anyone got any idea how I can set it up so that I have got a box come up with players names in a drop down box. I can then select the player on the left who has won, and the player on the right who has lost, enter it and the ladder to automatically update.
View 9 Replies
View Related
Jul 4, 2009
I have an Excel Workbook that is linked to Word.docm files and I want to put them on a CD, But I need for it to have a setup/uninstall format attached. Or maybe you might have a better method. The reasons for the setup/uninstall is for the unknowing person, with limited computer skills. This way from the autorun to setup/uninstall it will already give a destination to install file and add an icon on desktop. More user friendly.
View 9 Replies
View Related
Jun 26, 2008
I am trying to set up a costings sheet for my other half. So far I have worked out his costs for this basics i.e. van etc. man power, days, mileage. He now wants to be able to enter his miles to a job, man power, days etc, Then to show total invoice cost and show profit i.e. if miles need to be changed.
View 3 Replies
View Related
Jun 16, 2009
This might seem a strange type of Excel question.
Some "Clever" person decided to create a site plan using MS Excel. It has been done quite well tbh. The problem with Excel is that I am unable to set a custom page size as this plan sits nicely on a paper size I made ealier through using Word. Guess what there is a custom page size selection on Word but not Excel. Is there a walkaround for this ?
The only other way I can do this, is to print off 2 a4 landcape print out but the printer goes to the next page and leaves a nasty seperation.
The copy of Excel i have is 2003 sp3
View 9 Replies
View Related
Nov 27, 2012
When I protect the entire sheet, I can click in a cell and see what's in it, when I protect just a portion of the sheet, I can only click in the unprotected cells. Is this an Excel thing, or am I doing something wrong when protecting? I would like to see what the formula is in the cell when protected, but I can't click on the cell, what's the deal?
View 8 Replies
View Related
Aug 26, 2013
We have 100+ clients. Each client has inventory in our warehouse. Every day we write down how many items went out to our client on a piece of paper and subtract that number to get a balance inventory. When we run low, we order more on a new FO number. What is the best approach for tracking this? I was thinking one way is to put our clients in a drop box at the top of the page and then the data will appear at the bottom, however I can't get that to work. I am trying filters to see, but I am unable to subtract the balance automatically from the last FO# and from the correct area.
Is there a way to choose a client and product at the top of the page and have the tracking below?
View 5 Replies
View Related
Oct 17, 2005
I'm trying to Pearson Ranks correlation table. This is the formula I'm
using:
=PEARSON(Ranks!$B$3:$B$230,Ranks!B$3:B$230)
This is placed in cell B3. When I copy the formula over to DT3 the formula
correctly adjusts the B in the second part of the formula all the way to DT
and still references the values in the B column.
But I want to now copy this down to C4, D5, E6 and then over to DT for each
so that it correctly makes the correlation table.
View 12 Replies
View Related
Jun 16, 2008
I set up a spreadsheet for use by my colleagues, on an Acer 17 inch portable. My colleagues all use HP or Dell with 15 inch monitors (and different graphics cards to mine). They all complained that when they opened my sheet, the page breaks were all in the wrong place. When they fixed the breaks on their machines and I opened those file, the breaks were now in the wrong place on mine, but were all right on theirs.
I have now "upgraded" to a Toshiba 17 inch laptop, and find that my original spreadsheet done on the Acer 17 inch shows page breaks in the wrong place on my new machine.
Is this something to do with display adaptors? If so, does anyone know what I can do to standardise this across the board? (My boss uses a 20 inch screen, and the sheet for him is even more wildly out).
View 9 Replies
View Related
Jun 23, 2008
This part of my macro can be improved as it runs through over 100 sheets and takes a while?
Sheets.Select
For Each sh In Worksheets
With sh.PageSetup
.Zoom = False
.LeftFooter = "&F" & Chr(10) & "&A"
.CenterFooter = "&P of &N"
.RightFooter = "&D"
.FitToPagesWide = 1
.FitToPagesTall = 1
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.15748031496063)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0.354330708661417)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = 1
.BottomMargin = 1
.Orientation = xlPortrait
End With
Next sh
View 9 Replies
View Related
Jan 8, 2009
I visited all of the sites listed in the various posts & downloaded the XLM function help file.
My question is which syntax would I use? I'm wanting to add this to an existing macro (one that currently takes FOREVER). I think syntax 1 for worksheets & macro sheets, but I'm adding to visual basic, so maybe syntax 3 for vb modules?
Here's my existing code, if anyone wants to help me change this to XLM4pageSetUp ..
View 9 Replies
View Related
Sep 7, 2006
how to setup calender object in VBA application
View 4 Replies
View Related
Jun 13, 2007
Is it possible to protect against users changing the page setup?
View 2 Replies
View Related
Aug 19, 2013
I have to do a project, but I'm not really sure how to go about it. Here's my problem: I have about 200 stock ticker symbols already given(ie. AAPL, MSFT, NEE, etc...), and i have to create an excel file where i would copy/paste or enter in another set of stock ticker symbols and would give me back the answer if any of the newly entered symbols match the ones i already have(either in form of 1 or 0, YES or NO etc...).
View 4 Replies
View Related
May 18, 2009
Is there a way to copy page setup settings from one workbook to another in VBA?
Example, I have a spreadsheet with 5 tabs with each of them different page setup options.
I have a second spreadsheet with 5 tabs where I need to make the page setup the same for each tab as the saved spreadsheet #1.
Background: This is a workaround for not being able to do copy/paste special values for pivot tables. My workaround is to save an .xlsx as a .mht then reopen and save as .xlsx. The only problem is that I lose page setups through the process.
View 4 Replies
View Related
Mar 7, 2008
I've managed to clean up my code removing all of the selecting to get the macro to run faster but it seems to slow down when it gets to the page setup. Is it because of the ActiveSheet? Is there a better way to code the following?
View 10 Replies
View Related
Feb 21, 2009
Once a week I need to export data to excel for a report. Some weeks there may be 30 rows and other weeks there may be 100 rows. I created a macro in the attached spreadsheet called 247 formattedV2 that seems to work fine except that it seems to get caught up in the Page Set up area. Here is the things that I have already in the macro that need to be accomplished.
1.All cells should be set to wrap text
2.Bottom justify text on row 1 for wrap text
3.Top justify all rows below it for wrap text
4.Adjust width of columns to exactly what is shown. If text runs over, it should wrap (shown in parenthesis)
5.Center all titles at the top of each column - show with grey background as indicated
6.Center text in columns A, B & C
7.Lighten grey cell in beteen each call (keep them in for all notes, they were accidently removed in this version)
8.Darken grey cell for column headers
Page setup: Set margins to narrow setting at .25 top, bottom, left, right. Set to landscape. Should be 1 page width, as many as necessary for height
Set for 8.5x14 paper (legal size)
Column Widths
•A - Last Call Date (w=10),
•B - Priority (w=7),.............
View 4 Replies
View Related
Apr 14, 2009
I have a Sub test where I think can be setup in an Array or a shorter sub routine, how to shorted this routine. Ths routine was designed to check your Computer and depending if you are one of the selected on will the unprotect the sheet.
View 2 Replies
View Related
May 18, 2009
Is there a way to copy page setup settings from one workbook to another in VBA?
Example, I have a spreadsheet with 5 tabs with each of them different page setup options.
I have a second spreadsheet with 5 tabs where I need to make the page setup the same for each tab as the saved spreadsheet #1.
Background: This is a workaround for not being able to do copy/paste special values for pivot tables. My workaround is to save an .xlsx as a .mht then reopen and save as .xlsx.
View 2 Replies
View Related
