# Calculate Monthly Sales Based On Data Given?

Jun 9, 2014How to calculate the monthly sales based on the data given ?

View 4 RepliesHow to calculate the monthly sales based on the data given ?

View 4 RepliesADVERTISEMENT

I cannot solve with Excel 2010 and I have searched all over for the answer.

I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.

For example, I would like to turn this:

3/2/2005 $xxxx

3/5/2005 $xxxx

3/20/2005 $xxxx

4/2/2005 $xxxx

4/10/2005 $xxxx

Into this:

March 2005- $xxxx (monthly average)

April 2005- $xxxx (monthly average)

I have a feeling some 'countif' formula would work but I am not sure how to do this.

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 .

I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....

The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.

Actual Rolling 12 Goal

Sep 2012 5,367.24 73,663.30

Oct 2012 5,649.93 69,496.28

Nov 2012 14,163.38 73,451.30 [code]....

How can I add data to the monthly sales section in the workbook? I can't seem to figure out the code.

mockup.xlsx

I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.

View 4 Replies View RelatedI need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).

Range I4:I28 to the monthly sales worksheet by date.

See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.

View 2 Replies View RelatedI have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and

filling in the chart on excel but I would like to create a formula that

automatically does it for me based on total sales.

I have made a dynamic calculator which will calculate the total sale based on 3 criteria in 3 different cells as follows:-

I would like to derive the sales qty in cell C5 if all the 3 conditions in the range B2:B4 is true.

In this case the sales qty for Style-Colour-MRP combination(9103-PINK-399) should be 6.

I am unable to figure out with the formula for the same....

I'm looking for a function that calculates a fee deduction based on the 28th of each month.

I'm paying back Â£200 on 28th of every month starting 28th Feb and was hoping that a formula could keep track of this...

A

B

C

D

1

Â£1,300.00

=TODAY ()

2

-Â£200.00

28/02/13

FUNCTION

How to do it but basically I'm trying to put a formula in D2 as follows:

If Today's date (C1) equals C2 I need B1 to reduce by the amount in B2

How to continue it calculating reductions per month by duplicating the formula...

I have sales per month going down a column and I would like to subtract a corresponding $ amount going across columns. It's hard to explain but I would like a formula that I can populate all of the cells with. Spreadsheet should make it clearer.

View 6 Replies View RelatedI 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 worksheet with monthly sales figures by associate and by store. The store has a monthly goal as do the associates. If the store hits it's goal then the overall sales total is multiplied by 1% and then divided by the percentage of each associates involvement to reach that goal. (ie...150,000*1%=1,500, John sold 35,000=23%, so John gets $345 extra commission). If Johns goal was $25,000 and sold $35,000 he gets 1% or $350 commission. In turn, if he meets 1 or both sets of criteria those will be added together. If he doesn't meet either one then the result is Zero.

I have the store goal and Johns goal in separate cells to reference against. The actual sales cell is formula based.

This is basically what i'm trying to do:

If criteria 1 is met then % of 1% of store goal, if criteria 2 is met then 1% of individual goal, if both are met result1+result2. if neither is met then zero. I think?

I've daily data of a stock indices returns and I would like to calculate the monthly standard deviation. Currently, I'm using the following worksheet functions: =STDEVP(C2:C20)*SQRT(COUNT(C2:C20))

However, the range changes from month to month, which makes the process of calculating the monthly standard deviation to be quite tedious if I've about 10 years worth of data. I assume I could somehow substitute the range with a dynamic range, but I'm struggling to come up with the correct formulation that would do that.

I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.

View 3 Replies View RelatedI receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.

If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.

I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.

Example:

Breakpoint:

cum sales are > 500 pay at 3%

cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due

jan/ 100.00/ 100.00/ 0

feb/ 600.00/ 700.00/ 6.00

mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....

All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.

Column A Column B

201 $200

202 $300

203 $450

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

I wanted to get the data from data sheet where i have entered the data manually for sales what have done

Now I wanted is when i enter date i should get the product and price in other sheet

Example:

If I enter date in a1 cell

I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet

And c1 - c10 price (if i sold 10 item @that price)

I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”

The Monthly data is obtained using the following formula:....

In field A2 I use now() to get the actual date.In colom C8:C1000 I have all monday dates.In colom E8:E1000 I have the weekly sales figures.

How can I calculate the average sales of the last 10 weeks?

I'm trying to calculate periodically sales for new products, which have been in the market for max 6 monts. After that 6 months the sales of the product is not to be calculated. I have a huge amount of products, where this information should be calculated, so manually calculating is not an option. The products are in rows, and periods are in columns. As the data concerns several years data there is a problem, that some products have in some months zero sales, and in the next month again some sales. This messes up always my calculations. How to truly take only the first 6 months, and leave all the rest uncalculated?

I am trying to decipher how to calculate commissions for my sales reps. I have made just a simple spreadsheet to give you an idea of what I am doing. I have tried to us an IF formula but I think there are too many options( I have 9 reps). Basically I pay them either 10 or 15% so I need a formula to take the sales price - cost times their apporpriate %.

AgentSales Price CostComm Pd

AS150 75

JK255 185

JD325 250

JD125 50

AS50 10

AS50 10

AS335 250

JW75 25

I'm trying to create a simple sales report. No VBA code, only excel formulas.

I'm stuck on trying to calculate the weekly sales. I want excel to be able to recognize the day of the week and know that the month started mid week.

Ex. If the 1st of the month started on a Wednesday, it adds all the sales from Wednesday to Saturday only and

if the month ends on a Tuesday, it will calculate the sales from Sunday to Tuesday only.

I want it done automatically.

I've included a zipped excel sheet example of the worksheet for a visual example.

I'm trying to calculate a running total of ticket sales with 5 different groups (adult, youth, senior day, senior night, and child) to where once the number of tickets are bought it will update automatically. I enter the tickets in cells B5-B9 where it then calculates the amount due and the change due. I need to put the running total in cells B18-B22.

View 3 Replies View RelatedMy company has a catalog of ~6000 unique SKUs that we sell online. Currently we do not have a way to quickly determine how much a given item has sold month over month, and the rate of that change.

I have an excel workbook full of our last year's worth of sales data. I have organized it by placing each month of data in its own worksheet. I would like to be able to create a search box that will allow us to enter in a product ID, and have excel then create a graph with each month's of sales for that particular item. Is that clear?

I have table and need to take out montly total for each worker...

Now...

Each hours in day have own factor. (I need total hours per day but for illustration)...

So when worker works day shift from 8:00 to 16:00 it's easy... 8 hours

When works from 8:00 to 20:00 it's 8 hours + 4 afternoon hours

When works from 20:00 to 8:00 it's 2 afternnoon hours + 8 night hours + 2 day hours

Aditional problem is when day intercept holliday or sunday when that factors need to be included (if holliday is at sunday then it's like holliday).

Here is some attachment:

Book1.xls

I've also added last day of previous month and first day of next month because of night shifts than need to be calulcated. Therefore correct number of hours is 168 and not 188.

Below I calculated manually those numbers wich I want to be automated...

Also.. This is table I get.. If it's easier to make it somehow else, OK by me. And any number of aditional columns is not problem...

I am trying co Calculate number of nights between to dates for example 10/26/2012 -- 11/25/2013 in a period that can be greater than the month Period. for example. some One checked in on October 24, 2013 and they will checkout on december 17, 2013. in the monthly Period of 10/26/2012 -- 11/25/2013, I am trying to calculate, the monthly nights of that period itself, and the number of days he was checked in on that period.

View 1 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved