Summarize Monthly Sales From Daily Sales

Jan 5, 2010

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.

ADVERTISEMENT

Excel 2010 :: Sales Daily Sales Data To Be Aggregated Into Monthly Data (not Using Pivot Table)

Mar 27, 2013

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.

View 4 Replies View Related

Daily Sales Target Based On Yearly And Monthly

Jan 7, 2014

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 .

View 2 Replies View Related

Using Rolling 12 Month Sales And Minimum Monthly Sales?

Oct 17, 2013

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

View 6 Replies View Related

Copy Week Total In Weekly Sales Worksheet To Appropriate Week In Monthly Sales

Oct 14, 2009

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

View 10 Replies View Related

Sheet For Daily Sales

May 18, 2009

I have a query regarding making a Excel Sheet for Daily sales.

here I go,
Well i want to make an Excel Sheet where in I just need to enter the Date, Invoice Number , Product , No of Product and rest it should calculate the VAT (Rounding Off) amount N den the Grand Total..

M givin you an example in the Below Sheet.

View 9 Replies View Related

Sales Summary Sheet - Consolidate All Data In Sales Order Number Wise

Jan 5, 2012

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

View 8 Replies View Related

Determining Top Contributors To 50% Of Sales Based On Cumulative Percent Of Sales

Jul 2, 2009

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 Related

Sales Report From Sales Data Sheet If Select Date

Feb 4, 2013

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)

View 4 Replies View Related

Pivot Table: Calculate Percentage Of X Sales To To Total Sales

Feb 20, 2008

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

View 2 Replies View Related

Daily Sales Sheet To Refresh With Every Day

Jul 15, 2014

I am trying to build a sales sheet that will keep all the records during the month but every day it refreshes to a blank sheet, then allowing users to flick back to a previous day and all the data from then is still saved?

I think this is possible in excel as I am sure I have seen a working sheet before but I don't know where to start or even what the process would be called in excel?

View 1 Replies View Related

Subtracting Monthly Sales From Constant?

Dec 12, 2013

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 Related

How To Add Monthly Sales Data Using Formula

Dec 29, 2013

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

mockup.xlsx

View 5 Replies View Related

Calculate Monthly Sales Based On Data Given?

Jun 9, 2014

How to calculate the monthly sales based on the data given ?

View 4 Replies View Related

Formula To Calculate Sales Tax From Total Sales

Jan 4, 2005

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

View 9 Replies View Related

Conditional Formula- Worksheet With Monthly Sales Figures

Aug 5, 2008

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?

View 9 Replies View Related

Finding And Calculating Highest Trending Item By Daily Sales?

Apr 20, 2012

I have a very large spreadsheet with daily sales for over 140 different items, over the past 2 years.

This is how my data looks like:

ITEM NAME 4/1/12 4/2/12 4/3/12 etc
item1 10 15 500
item2 5 0 3
item3 0 0 5
etc

I'm trying to build a formula that allows me to see which item is the highest trending over a period of time I input.

I figured out the way how to select data between specific Start and End dates, but don't know how to interpret "trending" in a formula.

I was thinking maybe highest jump from beginning to end of interval, but what if it's a new item that was launched halfway through the interval and does poorly throughout, that would still be "trending" under that rationale....

View 1 Replies View Related

Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.

May 23, 2006

I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.

Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.

I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?

Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?

attached is book.xls and sales summary.xls for reference.

View 3 Replies View Related

Get 5 Top Sales People Names And Their Sales?

Oct 3, 2012

In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.

Starting in cell B59 and C59 I'd like to display their name and sales respectively.

Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.

View 9 Replies View Related

Finding Current Month Total Sales Using Current Month To Date Sales In Formula / Macro?

Aug 20, 2013

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

View 1 Replies View Related

List Of Sales By Salesperson From A List Of All Sales

May 21, 2009

I have one sheet that shows a list of all vehicle sales for a month: with a customer column and a salesperson column and a gross profit column. I would like to give a printout to each salesperson from a different sheet that only shows that salespersons transactions on it. Can excel parse that information out and list it in order row by row showing each sale for just one salesperson per sheet?

View 9 Replies View Related

Summarize Daily Data Into Weekly Average

Jan 17, 2009

I have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).

I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this? Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used?

View 5 Replies View Related

Calculating Sales Price

Jul 18, 2008

using Excel 2002 on XP.

My partner and I are selling products. He gets 5% of the sales price, then I get the rest. But I want to make at least $2 on every sale. So, let's say the item cost us $50. If he wants 5% off the top, and I want at least $2, how do we calculate what to sell it for?

I tried the following, but it didn't work:
$50 (cost)
+ $2 (my profit)
+ 5% (partner's profit)
------
$54.60 (sales price)

It doesn't work because I end up with $1.87.
$54.60 (sales price)
- 5% (partner's profit)
- 50 (cost)
------
$1.87 (my profit)

I've tried other things, but I always end up under $2. Is it possible to calculate this? or do I need to have a percentage for myself? If Excel can't do it, do you know of any calculators out there than can?

View 9 Replies View Related

Predicting Sales Data

Jan 10, 2009

I have data for average turnover per hour of a business and have fitted it to a polynomial (order 6) trendline. So I have for example, running along the x-axis i have 8am-9am, 9am-10am, 10am-11am etc and on the y-axis the average turnover for the relevant hour.

What I would like to be able to do is use the trendline's formula to be able to predict sales for any given day. So if I were to enter the first few hours of turnover on any given day, I would like excel to predict the rest of the day's turnover based on the trendline.

View 10 Replies View Related

Sales Order Sheet

Feb 13, 2009

I want is for the the cover sheet to provide a simplified version of the price list for people to use that will auto complete price to customer once product has been selected.

- how do I create a sublist? so if a user selects a product group from a drop down list in cell b1 then cell b2 will automatically display only the items from that groups subset.

- how do I link prices? If a user selects data from a drop down menu in b2, is it possible to display the price for that item in cell b3?

View 4 Replies View Related

Summarizing Sales Data

Feb 26, 2009

I'm stumped on what I know is a pretty basic problem. Maybe i'm just trying to over think it.

I have a table of sales data...One field is the date it was sold, one field is the amount it sold for. The date field isn't in order and it contains dates over the past 12 months. I need a way to total the amount of sales in each month and not through a pivot table. I am able to count how many entries there are, but I can't find a easy way to do a count of how much was sold in each month.

View 6 Replies View Related

Sales Realization Calculation

Jan 24, 2009

I am looking for the formula for calculating Sales realization in installments i.e. if i am selling $100 in 1st year i am receiving 50% in 1st year and balance in next 3 years same thing happens in 2 year.

View 11 Replies View Related

Sales Run-rate. Too Tough For Me

Feb 4, 2009

I'm trying to calculate a sales run-rate which will change on a day-to-day basis, to predict the end-of-month sales total.

The invoice values are in the data range H17:H74 (I don't want the final formula to add up the refunds in this field i.e. negative values)

The date field is in data range C17:C74.

So basically the formula will need to add all invoice totals (excluding refunds) and divide by the current number of days worked in the month (not duplicating days in the date field) and then multiply this by the average number of working days in a month (21). This should should give a predicted end of month sales total.

Am I just making things up that are impossible to do on excel?

I've had a bet with the other guy in the sales office because I said excel can do a lot more than he thinks. He's under the impression that excel begins and ends with what they teach you at school.

View 12 Replies View Related

How To Chart Sales Data

Aug 28, 2012

I am trying to create weekly sales charts from my worksheet. The variables I have are the items in inventory, their respective price points, and units sold week by week (for about 5 weeks so far). I am having trouble formatting all of this information in the correct way to get accurate charts conveying the movement of our inventory.

View 1 Replies View Related

Total Sales Of Every Month

Jan 14, 2013

I got individual dates in Column A. The format is 01/04/2012, 02/04/2012, 03/04/2012 and so on.

The person enters sales everyday. Now I want to make a new sheet which should sum up the sales month wise.

So in the new sheet I will have

Column A- Products
Column B- Sum of days in April 2012
Column C- Sum of Days in May 2012
Column D- Sum of Days June 2012
And so on

View 5 Replies View Related

Extrapolation: Sales Data

Aug 18, 2008

So I have a Sales Data Extrapolation question.

I have August sales data up until August 17th. I need to extrapolate the data to determine what my sales numbers will look like for the entire month.

Here are some of the specifics: 17 days of sales so far, sold 12,598 units. Total stores 1,500

View 9 Replies View Related

Sales Commission Calculation

Jan 4, 2010

I need to know what formulas to put into the cells in excel to make the following sales compensation example compute properly:

Time
Period Draw
Paid Actual
Commissions Owed to
Company Commission
Paid Total
Earnings Month 1$3,000$4,000-$1,000$4,000Month 2 $3,000$2,000$1,000-$3,000Month 3 $3,000$5,000($1,000)$1,000$4,000TOTALS$9,000$11,000-$2,000$11,000

View 9 Replies View Related

YTD And Period Sales Report

Feb 2, 2008

I have an Access DB that I query with excel and I pull two years worth of sales data. I have tried using a pivot table report to display the following data, but I can't figure out how to display the data in the following format.

The pivot table will give period and YTD but the totals for YTD are not cumulative for the year up to that period (it seems to total the period only).

For the current Year- period (month) and YTD (only up to the period displayed).

For the last year- period and YTD (only up to the period displayed).

The fields I query are Customer, City, Product, Salesperson, Period(month), Year and Sales

I have tried putting the queried data on one sheet and then using formulas on another but I am not having any luck.

I would also like to be able to select which period I am viewing but this is secondary.

I can upload an example if necessary.

View 5 Replies View Related

Sales Budget Variance Report

Mar 16, 2014

I am trying to do my homework for college and the below excel grid was given to us to complete. I do not understand where to get the information it is asking. the first grid is the numbers we are suppose to use to input in the other grids. We are suppose to put a formula in on the last to two columns on each grid but I do not even know where to start.

Budget

Actual

Product
SaleUnits
$/Unit

View 7 Replies View Related

Sales Winners By Product Type?

Apr 10, 2014

Winner and runner up names , have attached expected results

Also wonder what if the formula to pull second highest in a range , I do know =Max(RANGE)

View 5 Replies View Related

Creating Sales Order With Dropdowns?

Apr 22, 2014

I have started to create a sales order. I have three columns that I will be working with. I have three lists in a separate worksheet (worksheet 2) in the same workbook. Column A has part numbers. Column B has descriptions of the part numbers in Column A. Column C has prices for the parts in Column A.

I have created a drop down list in Worksheet 1 from the list in Column A in worksheet 2. What I want to do is somehow link column B and C to Column A so that when an item is chosen from the drop down list, the information will pull through.

View 5 Replies View Related

Comparing The Same Month Sales Between Two Different Years?

Jun 6, 2014

I am trying to create a function where the user can type in a single month, and 2 different years, and it will calculate/compare that single month from the 2 different years entered.

For Example: I type in January, and I type in 2014 and 2013. The excel formula will spit out whether January 2014 is over/under January 2013.

See attached for my sales report. You can see where I left some space for the requested function.

as it is the last change I have to make to the sales report.

View 5 Replies View Related

Using The SUM Function To Record Weekly Sales

Nov 14, 2007

I would like to have a set of cells that add up all the sales within a given week. I know how to do this simply for one week, but how do I get Excel to automatically take this function and create the rest for future weeks?

After entering the SUM function in one cell, I click and drag on the box to try to get Excel to correctly input the functions in the next cells (like how Excel will correctly input the next date, week, or month). But Excel doesn't do it correctly.

View 10 Replies View Related

Countif To Find Total Sales

Jan 19, 2009

I have a column of names, and I want to be able to count all the instances of each name, as each instance represents a sale of a product.

Countif(Sales!B:B,"Dave") works, counting all the instances of Dave.

But if I have all the names in column A, and try to have column B give the results (from another WS), as in: =COUNTIF(Sales!B:B,'Best Customers'!A1), I get a "0" as the result. Yet XL help says countif can be used as =COUNTIF(A2:A5,A4). where A4 holds the value to search for.

While we are exploring this, is there a good way to look in a column, get every different instance of the names, and output them into another column?

View 9 Replies View Related

State And Local Sales Taxes

Feb 21, 2009

Our state carries a 4% sales tax on all items except food and prescriptions.
Our county carries a 3% sales tax on everything.

Attached on my work sheet:
Column "C" determines if an item is either food or non-food.
"G5" is the subtotal of column G
"G4" is the S/tx on "G5" at 3%
"G3" is the S/tx on "G5" at 4%.
"G2" is the gross pay out.

My question is:
I'd like a formula for Cells "G3" and "G4" that can determine which items paid for in column "G" match a "N" or an "NF" in column "C".

If an item in column "G" represents a "F" in column "C", then there should not be anything in cell "G4" If an item in column "G" represents a "NF" in column "C", then there should be a figure in "G3" & "G4".

View 2 Replies View Related

Predicting Sales Data In Future

Jul 28, 2009

I need to predict sales data in future using multiple independent varaibles.I used FORECAST function to predict sales value for single independent varaiables.But i dont know how to predict sales using multiple varaiables.

View 10 Replies View Related

ADVERTISEMENT