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 RepliesIf 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?
Is there a way to automatically save a template as an xls file using the macro below?
ThisWorkbook.Activate
Sheets(" SCHEDULE").Select
ChDir "C:Documents and SettingsChrisDesktopRainforestMASTER LABOR"
ActiveWorkbook.SaveAs Filename:=Sheets("Schedule"). Range("v77").Value
I am trying to make a code that was written for the master schedule available when the workbook is renamed?
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'm trying to achieve: -
I want to create a spreadsheet which will include a column with email addresses in it.
Here's my problem.......
I want users to be able to click on an email address (like a hyperlink) and by doing this open their mail client (in this case Microsoft Outlook) and send an email. I would like the mail client to load a specific template just by clicking on the link from Excel. I'm not sure if this is possible, but there doesn't seem to be many things you can't do on Excel.
A member of my team has created numerous templates in Excel 2007 for various customers. Each template has some columns that are identical for each (Cust Name, for example), and some columns are unique to each customer. I want to combine all of these individual templates into one Excel workbook and be able to click (select from a dropdown list) the customer name, and have only those columns that pertain to that customer appear. Select another customer name, different columns appear, and so on. Is this possible to do in Excel 2007? [This is my first post, so please forgive any offenses if my question is too long.]
View 9 Replies View Relatedwhere I might find some?
View 3 Replies View RelatedHave 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 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
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 would like to have a workbook with class lists for 21 classrooms.
Then I would like to use this each month to generate a workbook that has one sheet per class with the teacher name and class name as an overall header. The row stubs would be the student names. The 2 column headers would be the weekdays (Mon through Fri) and the day of the month as a number.
It seems that this could very well be an application that already exists. It seems like the kind of thing a business would create and put on the web as a free download as a good will thing.
Does such an application already exist? If not, is there a small example of populating a workbook with information in another workbook?
I am currently working on a performance document. I am working on the principle of having a master template which people can then access, Save As, and use to monitor performance around KPI's.
The issue I am having is around the name of the document changing when it is saved as, as all records have to retained. The macro I am struggling with is designed to unlock the workbook & worksheet, copy the worksheet specified into a new workbook and then return to the workbook the macro is held within and lock it back up. However, when the name changes it just locks the new workbook rather that the version I am asking it to.
ActiveWorkbook.Unprotect Password:="KPIreview"
ActiveSheet.Unprotect Password:="KPIreview"
Sheets("EID Graphs").Select
Sheets("EID Graphs").Copy
[Code] ....
Is there anyway I can change the “Kent – Monthly Activity & Performance Review – Version 17 – Master.xlsm” statement within the macro to reflect the change in name of the document?
I have quite a few Excel templates that need to be modified by changing/adding information or sheets to them. Instead of working through the lot manually, it would be good if I could do the same through code. With my little knowledge I can do this for workbooks. Need to typical code snippets or suggestions? e.g. to open a template file, save it with the original name, etc. The usual Dim srcWB as workbook, Set srcWB= ABC.xls and Workbooks(ABC.xls). open do not seem to work with template files (ABC.xlt)
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'm trying to figure out a way to get my invoice templates to copy the names and addresses from them to another worksheet to form a customer database if you will.
View 14 Replies View RelatedI have a database that shows repeated templates down the page...ex.
"Site" (here) Remnant
Jan
Feb
Mar
"Site"(here) Remnant
Jan
Feb
Mar
All after another using the same template.
I am trying to sum all the occurances that fit the criteria Jan and Remnant:
I have this:
=SUMPRODUCT(--('"STATIONS"'!$A$4:$A$300='"GROUP"'!$A3)*('"STATIONS"'!C$3:R$3='"GROUP"'!C$2),'"STATIONS"'!$A$4:R$300)
But I get a N/A , my reference A3 matches the dates in the database and C2 matches the repeated occurances of Remnant...
Is this possible since my templates keep repeating down my database?
Basically, each template in the database is related to different stations, and I am trying to sum all the stations details in the database for each month and Header"Remant"
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 really need a comprehensive excel file which can manage my bank account id, savings, transactions, withdrawals, transfers etc etc. could be better if it can show any graphical interface with charts as well. I am willing to have a file so that i can manage more than one bank accounts. ( Excel 2003 is my version)
View 3 Replies View RelatedHow does one add files/templates to excel2010 to customize scroll bars, graphic-like or clip-art type?
View 1 Replies View Relatedhow 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 need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies View RelatedIn a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies View RelatedI have a range of cells in a work sheet "sheet 1 " my objective is to filter this range according to certain criteria (i ve succeeded to do this ) yet what i want to do now is copy this data to another existing worksheet in a certain range .
note :the existing worksheet to which i 'll copy the filtered data has some cells out of the range that i dont want to over write ..
Simply :how to copy a selected range of cells in a work sheet to already existing work sheet in a specific range aswell .
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
Following code has 2 IF statements , the code runs the way it is and the first IF works perfectly, but the second IF does not work.
View 3 Replies View Related