Monthly Item sales Report - Cross tab

I was looking for a cross-tab (spreadsheet style) report created by RMS 
that would put Months across the top (columns) and Items for the rows. 
It would be great to be able to see Qty on hand. The report would look 
something like:



Lookup code  Description  On Hand  Month 1  Month 2  Month 3  Month 4 
Month 5  Month 6
12345678       Test Item         67         125         80          100 
        110         100        88
87654321        Another Item   25          80          60           95 
        147        158        68


This is a great tool for seeing monthly trends as well as forecasting, 
and determining reorder points and levels

Thanks

Jay
0
3/7/2006 12:08:07 PM
pos 14173 articles. 0 followers. Follow

1 Replies
423 Views

Similar Articles

[PageSpeed] 24

I've found the most flexible way is to do a database query in Excel. This is 
exactly what I do:

Pull the entire contents of the ITEM table into a worksheet. You will need 
this for Quantity on Hand.

Get the entire Transaction Detail table on another table (you may want to 
filter by date or trans# if you have a lot of sales).

You will need to do some formulas to put the months on the transaction 
details sheet.

Run a pivot table and format it to look like your example.

Use an INDEX/MATCH formula to put the quantity on hand next to the pivot 
table.

I'm sure there is an EASIER way, but I like pivot tables. I'd marry pivot 
tables if they were a person (or if my state defined marriage as one man and 
one Excel tool).

Jason


"Jay" wrote:

> I was looking for a cross-tab (spreadsheet style) report created by RMS 
> that would put Months across the top (columns) and Items for the rows. 
> It would be great to be able to see Qty on hand. The report would look 
> something like:
> 
> 
> 
> Lookup code  Description  On Hand  Month 1  Month 2  Month 3  Month 4 
> Month 5  Month 6
> 12345678       Test Item         67         125         80          100 
>         110         100        88
> 87654321        Another Item   25          80          60           95 
>         147        158        68
> 
> 
> This is a great tool for seeing monthly trends as well as forecasting, 
> and determining reorder points and levels
> 
> Thanks
> 
> Jay
> 
0
Jason4159 (667)
3/7/2006 7:03:28 PM
Reply:

Similar Artilces:

lost two months of received emails
Help I lost about 2 months of received emails. They are not in my deleted folder. I already tried the pst restore utility. Thanks ...

Item is not discountable at POS problem
I have a lot of items that are normally not discountable at the POS, so they have the "item is not discountable at POS" box checked which works great, but every ones in a while I need to discount these product anyway (due to a damaged product or something like that). How can I accomplish this? I thought checking this box would keep the product only from being discounted due to a (selected) Customer discount as stated in the manual (quote). Item is not discountable at the POS: If selected, the sale price cannot be marked down due to customer discounts. Any work arounds or sugg...

Reports listing discounts
Is there a report out that will list all of the discounts that are listed under Database | Discounts? Also, is there a report that will show an item list, with the name of the discount sceme? Any help would be great. This is a multi-part message in MIME format. ------=_NextPart_000_0020_01C88B80.0741B2C0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit Try this one Craig "Steve Curtis" <sbc_services@frontiernet.net> wrote in message news:Osud7a5iIHA.4080@TK2MSFTNGP03.phx.gbl... > Is th...

Gray out a CListCtrl item?
I noticed while writing a handler for the NM_CUSTOMDRAW notification for a CListCtrl that the NVLMCUSTOMDRAW structure passed to the handler contains an NMCUSTOMDRAW struction, which contains a member variable uItemState. Possible settings for this state include CDIS_GRAYED and CDIS_DISABLED, both of which sound a lot like what I'd like to do to the state of some of my items (in Icon View). Is there any way to set a CListCtrl item's state to be disabled or grayed? I'm currently setting the state to LVIS_CUT and the text color to gray to represent a disabled item, w...

Open a form in context from a multiple item form
I have a multiple item form based on a query. It is a list of customers along with the total invoice amount. Each customer is identified by a category AND the customer name. I have added a button at the end of each line to open a form that allows me to edit the customer and also display a list of invlices in a subform. No matter what I try, the form I'm trying to open always displays the same customer record. It will not display the customer of the row in which I clicked on the button. I'm sure that this can't be that difficult, I just can't crack it. I am a beginn...

How to show items and assigned taxes
Our taxes collected are lower than they should be based on our sales. In random checks of inventory items, some have come up without taxes, where there should be taxes. I'm looking for a suggestion on how to determine which items have which taxes assigned, without having to go through each item individually. Thanks Christa, SO Manager --> Wizards menu --> Inventory Wizards --> Task 170 Assign Item Tax, press the All button click Next then Finish. You can sort each column by clicking on the corresponding column header. This will also allow you to make mass Item Tax ch...

Sales person Number
An alphanumeric salesperson number was created. the SOP was created and posted, I am running a Crystal Report and using the field from SOP30200.SLPRSNID, where a message is saying, 'the string is non-numeric'. If it is because of that new number why did it post? GP's salesperson ID field is in fact alphanumeric, which means it does not post any restrictions on when you set up a salesperson. This error is a Crystal error and it is referring to a certain operation you are trying to perform on the field -- typically a mathematical operation. Please review any calculation or co...

Convert years:months to months
Hello- I am hoping to use Excel to convert data that inputted in a years:months format to just months. For example, I want to translate 5:3 to 63 months. I'm running into one hiccup: Excel views the inputted data as time and is translating it into 5:30 AM, making calculations challenging Does anyone have any tips? =HOUR(A1)*12+MINUTE(A1)/10 you may need to format the cell a General as Excel can be overhelpful and use a time format best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Jen" <jvarley@u.washington.edu> wrote in ...

This Month and Last Month
In the same query, I am trying to get two counts so I can compare them in a pivot table. I have ServicesID ServicesDate I want a count of ServicesID for the current date this month AND a count of ServicesID for the same date last month in the same query. Any ideas? Use a subquery to get the count of services for the same date last month. Here's an example of how to do that sort of thing with a subquery: http://allenbrowne.com/subquery-01.html#YTD That example does year-to-date calcuations, but the process is the same. In your case you will be counting the ServicesID field where ...

MONTH(a1)
I'VE ACHANGEABLE NUMBER IIN a1 AND I PUT IN b1 "=MONTH(A1) IN ORDER TO GIVE ME THE MONTH'S NAME WHEN I CHANGE THE NUMBER IN A1 BUT IT DDINT WORK WHY? PLZ HELP THANXXXXXXXXXXXXXXXX FOR AL -- frs ----------------------------------------------------------------------- frsm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3123 View this thread: http://www.excelforum.com/showthread.php?threadid=51453 I believe you have to have a date in A1, not just a number. HTH Regards, Howard "frsm" <frsm.23jpcy_1140468300.4445@excelforum-nospam.com...

Reports Don't Show Invoices as Income
Money 2005 Small Business; Version 14.0.150.1105 I've setup clients, services, and invoices. I run any of the business reports and none of the "invoice income" shows up as income. When the invoice payments are deposited, the Category is "Payment for Invoice". Why wouldn't all invoice payments show up as revenue or income? When I try to customize the report via Customize->Category->Set Income Categories, the "Special: Payment for Invoice" is not available. I would think/hope that payment for invoices should be considered revenue by defau...

!!! SOFTWARE 4 SALE !!!���������������������������������������������������������������������� 5781
Software for sale. Huge Selection. 10 Euros/CD. Worldwide shipping. Windows, Mac, and Linux. The very latest software, stock video/photo, games, etc. Contact David for details: david007@worldnewstonight.net � ������������������������������������������������������������������������������������������������������������������������������������������������������������� (-xJdW<vz ...

Month By Month report
Does anybody have an RMS HQ sales report which would show sales in � by month? Ideally each month across the top of the page, and each department/catagory/supplier down the left of the page, with the value of sales each month. I would run this for the period of a year and it would show all my catagory sales per month etc. thanks Phil I am sending you the reports, once get then rate it. "Philip Gass" wrote: > Does anybody have an RMS HQ sales report which would show sales in £ by > month? > > Ideally each month across the top of the page, and each >...

Require report with user ID and password
We require a report which has the user ID and the password. In version 9.0 the password is encrypted so we can not generate such a report currently. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowse...

Supress X-Report Printing
Is there a way to suppress printing the X-Report? I"d like to stop wasting paper and just look at the X-Report in the Journal instead of killing a tree... Randy This feature is in our TenderView add-in for POS. Instead of printing a X Report, click TenderView. Printing to your full page Windows printer is optional. TenderView is included our RMS Toolkit add-in for RMS or is available separately. Free trials are at the link below. I suggest the RMS Toolkit trial as it includes TenderView and more. To purchase any of our add-ins, contact your RMS reseller. Greg Digital Retail Solutions...

Is there a way of Making monthly tabs without typing each month?
I would like to avoid typing in each month every time I create a workbook. I have to do individual yearly workbooks. Is there a faster way to make monthly tabs. Example type Jan, Feb, Mar and then somehow the spreadsheet would fill in the following monthly tabs? The easiest way is to make a blank file with appropriate tab names, and then make copies of that file. Or, create a tab called "List", start your list in cell A1, and run this code: Sub Add_Tabs_From_List() Dim Nayme As String Dim K As Byte K = 1 Range("a1").Select 'this is the first cell in List Do Unt...

ESM reports smaller mailbox size than Outlook
I have an Exchange 2000 SP3 on Windows 2000 Sp4 that the ESM is reporting mailbox sizes that are smaller than what Outlook 2003 is reporting. ESM reports user mailbox of 59,000 KB and Outlook is reporting mailbox size of 112,000 KB. The quota limit is set to 40MB warning, 50MB no send, and 100MB no send/receive. If single instance is playing a role in this, how can I determine what messages the user needs to delete to get below the 50MB limit (basically the messages that ESM sees)? Also, does the quota set on the mailbox store apply to all folders in the users' mailbox? (sent, de...

Pivot Table unable to Include Hidden Items In Subtotals
I have set up a pivot table that lists only the Top 10 values in a column, but also want the subtotal for the entire column, including the hidden items. Excel Help tells me that I can click the "Include Hidden Items in Totals" button, but the button is not available. I followed the help instructions to first select "Subtotal Hidden Page Items" then to unselect it and didn't get my result in either case. Any ideas on why the button is not available? ...

counting occurences specific month appears
Afternoon all, You've helped out before and I come to you again. Here's the question... 5 worksheets in my workbook, each representing a different office. Column A for each worksheet is the date in the following format: 03-May-10. Let's say the first sheet is named Montreal Office, the search range is A3:A5000, what formula am I supposed to use? I think from past posts, I'm supposed to use =sumproduct but I don't know how to incorporate my specific sheet. Thanks so much, MM On May 4, 5:41=A0am, MM <M...@discussions.microsoft.com> wrote: > After...

Suppress 'Clipboard can hold up to 12 items' message
Can anyone tell me if it is possible to suppress the 'clipboard can only hold up to 12 items' message you receive when you try to copy a 13th item into the clipboard in Excel? If so, how? Thank you... ...

Worksheet Tabs #8
Can anyone tell me if the Tabs at the bottom of the Worksheet can be adjusted to be at a 45degree angle? If so, what are the steps to be able to do it. Thanx Steve Whitenite57@optusnet.com.au I do not believe you will be able to accomplish 45 degrees. What are you trying to accomplish? You have long sheet names? You can change the size of the vertical scroll bar. Grab the line to the left of the left scroll arrow and drag to the right. Now you will have more tabs visible. -- Damon Longworth Don't miss out on the 2005 Excel User Conference Sept 16th and 17th Stockyards Hotel - ...

weird sum on report
Hi everyone I'm building a report based on several crosstab qry and it seem ok with values. That report has group values. I want, at some point, a line with totals as well as a line with the difference between 2 lines, like expenses to sales. Quite simple, i guess. Result : not only it doesn't subtract in some grouping/levels, as well it doesn't sum the other 4 lines i need. Is this weird or not? (lol) Tks in advance for all your help. Pedro Pedro, I don't see anything wrong from where I sit. Of course, I can't see your crosstabs, data, grouping levels, expressions, ...

Real Time reporting as spread grows
My department uses an Excel spreadsheet to capture daily Request Fo Quotes (RFQ) from Customers. Each RFQ entry requires Customer Name, Customer RFQ Number, Dat Received, Date Completed, Quantity, Unit Price, Total Dollars, an other fields. Each month our Department provides management with a report tha summarizes the number of bids, days in Estimating, Bids valued ove $100,000, the average response time in days, and other data. Is there a way to formulate a running summary of each customer entr that grows with each new entry? I currently apply a number of "If" an "Count" ...

First day of Next Month
Hello, In A1 I have the date 3/10/10. In B1 I would like to insert a formula that will show the Date 4/1/10. Basically, I would like a formula that will show the first of the next month no matter what date is shown in A1. Thanks for the help. Try this: =DATE(YEAR(A1),MONTH(A1)+1,1) -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Jim" <Jim@discussions.microsoft.com> wrote in message news:3A3346E7-C4DE-...

Assigning print date / batch number to reports & invoices
Hi, I want to assign a print date to things that are only supposed to be printed once such as invoices, statements, etc. I have a invoice table. On the invoice table, I have PrintDate, which is the date the individual invoice was printed. The query select all invoices with no print date and prints them. I will be changing the PrintDate to the BatchNo field. I'll have a ReportBatch table (keyed by BatchNo) where I will record the date, time, report name, and other information about the different reports that are printed. And my query will be change to select all ...