what i need to do to track daily sales with 10% growth from previous year total sales.
I have 12 separate sheets for each month.
On each sheet i have separate columns"date" "goal" "actual" "% from goal".
What function/formula is needed to calculate the daily target goal taking into account that weekends are busier
I have a problem here in calculating the Daily sales target based on Monthly Targets and Year End Target.
I am attaching the file herewith which has Yearly & Monthly Targets defined. Need calculating Daily targets which should match with Monthly & Year end target .
I have the split of day wise sales for a week as well in another tab. However not able to get the exact monthly target as listed .
From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.
View 6 Replies View Relatedi am needing to issue a dos command in excel? basically i need to send a target link to a file. i cant use a hyper link for several reasons, and this is the only way i know how to go about this.
View 2 Replies View RelatedI have the following scenario:
Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.
So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.
I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.
Does anyone know of a statistical formula that would create a normal growth curve?
For example. Let's assume that I want to forcast growth from January through December. I know that in January I will have 100 units. In December, I will have 1,000 units. Is there a formula to predict the the units in Feb-Nov (using a normal growth curve)?
I have main worksheet (target a) that I am trying to populate data from target(worksheet) b. The data I am trying to get from target b changes every month,(declining balance) based on a new month. So how can I get financial data from different cell each month from "b" into same cell in "a"? (so "A" # would be overwritten in same cell based on new # from "B". I have tried VLookup but can't be doing something correct.
View 2 Replies View RelatedI am trying to calculate the compound annual growth and my starting point is a negative number. The example is that I have (273,000) for the YE 2003 and have Growth to the amount of +767,000 at YE 2006. The formula I have been using for other calculations where the starting point is positive is =POWER(J23/C23,1/O$1)-1. Where J23 = a positive amount and C23 = a positive number. This formula works fine, but when C23 = a negative number the formula does not work and the % does not make sense.
View 6 Replies View RelatedI would like to use excel to plot a growth curve. I would like it to plot the points that correlate to my data without connecting them by a line. I would then like to give excel a formula and have it plot that line on the graph.
View 9 Replies View RelatedI have US money supply data, arranged monthly from 1975-2008. I need to calculate the monthly growth rates. I would really appreciate some help as I have no clue how to do this.
here is a link to a text version of the data I am using: [url]
I have access to a huge database in excel that involves sales data. The database is catagorized like:
Column A = Country
Column B = Product
Column C = Company
Column D = (Product) category
Column E = Sales
Column F = Period
Column G = Sales of particular company
Column H = Market category
Further details: see attached file!
I am looking for way to express developments ( absolute and relative) in market growth and market share over time.
I have 3 questions:
How can i show the growth of the total market sales over time (period 1 to 4)?
How can i show the growth of the Int comapny sales over time (period 1 to 4)?
How can i show the market share of the Int company over time (period 1 to 4)?
Market growth can be calculated by comparing the sales amounts of the total market (or the Int company) to previous periods. Market share can be calculated by expressing the sales figures of the Int company in percentage of the total sales in a partical period (i=1,2,3,4).
So, I work in a large indoor grow facility, and as such, we need to keep an active, updated calendar of all activities that will need to happen in the grow. If something happens to a set of plants, we need to change to date of transplant into the next pot size and every transplant after that, as well as moves into the flowering room from the vegetative room.
I have tried to manually make a calendar (I'm not worried about auto-generating a calendar or auto-filling dates, only things that happen on those days.) and tie in this information, but I'm having issues automatically searching a column and finding the date, and then pulling more information from that row automatically. (i.e. If anything in Column E has the date 7/3/14, then fill the data from Column C and Column B on the same row.)
I'm attaching a copy of the spreadsheet that I am trying to pull the Data from, and a copy of the Calendar I mocked up.
FYI, the spreadsheet I am pulling from Automatically fills once you put in the Clone Date, and then if you add in an adjusted date, it will adjust all later dates from that same section.
I tried to find something other than Excel as well to do this for me, but AFAIK nothing exists to make the calendar update Live, only to reproduce a new calendar with the current spreadsheet. This is not an option, I need it to stay up to date.
I am using a piece of track changes VBA code mentioned on this site, which among other things creates a new column that reports the number of any cell that has been changed (e.g., $K$32). What I would like to do is, next to that cell, report the title of the row in which that cell appears. In other words, if someone changes cell $K$32, for easier reference I'd like others to be able to see that this cell appears in a row titled "New Sales". I'll be happy to clarify with more specifics if need be.
View 2 Replies View RelatedI used HTML Maker to show my sheet below. I want to have cells K3:Z3 automatically populated using the same calculations as "Linear Trend" Auto Fill and K4:Z4 using "Growth Trend" Auto Fill without having to do it manually. The data in Row 2 will of course be continually updated with actual scores so the remaining empty month scores would need to be updated again. I am using this to come up with a more accurate prediction method than a simple trend line in Excel graphs.
******** ******************** ************************************************************************>Microsoft Excel - Auto Fill setup for questions.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL14=ABCDEFGHIJKLMNOPQRSTUVWXYZ1Months00.030.10.250.512345678910111215182124273033362Score10 1010 9887 3Linear Predicted10 1010 9887 4Growth Predicted10 1010 9887 5Auto filled Linear in Red10 1010 98877.066.666.275.885.485.094.74.33.913.513.122.732.331.941.551.156Auto filled Growth in blue10 1010 98877.146.826.526.235.955.695.435.194.964.744.534.334.143.953.783.617Actual scores10 1010 98877554456 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have excel 2007. My data sheet has columns for the sales for May 2009 and the sales for May 2008. Next to it is a column for the % growth between the 2 periods. When I put all this info into a pivot table and add the formula as below to the pivot table it shows the correct values for all the individual customers but not for the Grand Total line. Here it just shows a zero.
Can you also help so that the names on the data side of the Pivot table is the same as on your original sheet where it is reading the info from, and doesn't say "Sum of May 2009, but only says "May 2009".
Please show how you would work out the % Growth between the two different columns, as I tried it, and on the individual cell the data is correct but on the grand total line it shows it as zero and does not apply the formula.
I used the formula
=SUM('MAY 2009 H&B'/'MAY 2008 H&B'*100-100)
=sum(33,296,271 / 46,852,649 *100-100)
Result should be -29, but it shows zero
I have a model that makes projections based on annual growth rates. However, I need to evaluate the data based on monthly intervals. With an 8% return on an investment of $1,000,000 my ending balance at the end of the year should be $1,080,000 and in year 2 it would be $1,166,400 and so on. In order to evaluate the monthly data I need each month in year 1 to be based off $1,000,000, so it would be 8%/12=.006666% or $6,666.66 per month. The next year would be based off $1,000,000 + (6,666.66*12) = $1,080,000 and each month would be $7,200.
writing a formula to evaluate over 360 periods.
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.
r = 100((Y/X)^(1/n))-1)
So for X = 200, Y = 750, n = 10, we have
r = 100((750/200)^(0.1))-1) = 14.1309%
I want to create a chart that shows "Gap to Target. See attached Sample spreadsheet for more info.
Note how the yellow part of the chart is formulaic...is actually the chart...and the green bar over it is manually pasted. That green bar is supposed to be "result" and that yellow gap is supposed to represent the difference between Targeted sales and the actual Result.
How to actually build the green part into the chart? Every time I try, it just adds them together and doesn't show the gap.
Example.xlsx‎
Is it possible to add a value to a target cell without VBA Ie I have 3 worksheets and I would like to pull a varying cell value from each sheet to give me cumulative total on another sheet in a single cell.
View 2 Replies View RelatedI have a simple sub below (CopyData) to copy a cell value (I2) to next unused row in column O. If I run the sub from the VB editor, it runs fine doing exactly what I intend to do.
I've tried several macros to cause this sub to run when the value of cell I2 changes but they all behave the same way. I am initiating worksheet changes using the F9 function key. I2 changes every time I press F9 but the CopyData sub doesn't run.
Below is the latest attempt:
Code:
Sub CopyData()
Range("I2").Copy
Sheets("Calculations").Range("O" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Sub
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$2" Then
Call CopyStuff
End If
End Sub
Im tring to run the procedure called KeyEventsOn ONLY when a single cell in range E29:E30 changes.
The Intersect and Target are borrowed from a book , I dont fully understand how these work. I am assuming that 'Target' should simply reflect the currently selected range, and the 'intersect' function runs a check as to whether the target is in the range to be checked.
The problem Im having is the variable 'Target'. If the above is true, why for example when I type 'ABC' into the current selected cell does the variable 'Target' show as "ABC" and not "$E$29" (or whatever cell is selected)...
what the Taget.Address relates to in VBA. I cant figure it out, how can this be used?
View 9 Replies View Relatedin selection change event how to identify formula in target?
View 9 Replies View Related I'm working on a spreadsheet to measure performance.
Target is 10% and below
If actual is below or equal to 10%, then 100% achievement
If actual is above 10%, From 11% to 20%, then achievement is 90% and so on down until 0%
If actual is beyond 20%, then achievement should not exceed 0%
I am trying use target Bval in a sheet. Can I give conditions like if only the value is number then trigger the code else no. Is there a possibility???
View 6 Replies View RelatedBasically, I'm trying to do this:
View 11 Replies View RelatedHow would I set up a series of CASES depending on the Target.Value (a doubleclick code within spreadsheet) such that IF target value isblank, then change to Ö. If target value is Ö, then change to "NA". If target value is "NA" then clear contents
Would CASE be the best way to go or a series of IF's?
I am building a sheet of sales targets for 2009. With each month allocated a certain percentage of the annual target.
I wish to be able to take into account a change of target at some point in the year.
If i were to change the annual target in June, i need the spreadsheet to only change the monthly targets from June onwards, January - May are finished.
In the example attached there is a change in annual target in June. How do i calculate what the remaining month's targets need to be in order to meet the annual target while taking into account what has already been achieved and the shape of the budget as indicated by the percentages??
I have a row of data that holds dispatch values by week. I have a second row of data that holds stock levels by week. I simply want to create a third row that tells me how many weeks of demand a stock level will cover.
The data looks like this (comma deliminated):
Week, 1, 2, 3, 4, 5, 6, 7, 8, 9
Stock, 26490, 24490, 22490, 20490, 18490, 14239, 17898, 13646, 9393
Dispatch, 2000, 2000, 2000, 2000, 4251, 4251, 4252, 4253, 2350
In this example week 1 would have 9 weeks of stock cover, week 2 - 8 weeks and week 3 - 7 weeks of cover.
This is calculated by summing along the dispatch row until the cumulative sum of dispatch exceeds the current week stock level; then counting the number of weeks in the sum that cumulatively are less than (but closest to without being over) the total stock of that week.
Currently we manually create a massive array that sums along up to 26 weeks in advance and then have a manual formula that finds the maximum value below the target. This then outputs the column reference and then calculates the number of weeks based on the difference between the week column and the column highlighted.