Sales by State report

[edisto21]

Is there an easy way of creating a sales by state report? We're using version 2002 and we do not have Crystal.

[dringstrom]

As always, easy is a relative term. It's easy for me to set up a DDE (dynamic data exchange) routine that builds the report you want in Excel with just one mouse click. As for a non-programming-based solution, though, I don't see an easy, or at least clean, fix.

The reason it's not clean is that the sales journal export and the sales journal report both limit you to the Ship To State field. If you're not filling this field out with each invoice, then there's not an easy way to just dump the data to Excel. It can be done, but it requires two exports and some knowledge of formulas and pivot tables. Basically, you'd have to export the customer list, and export the sales journal, use lookup formulas to thread the customer's state together with their sales transactions, then use a pivot table to summarize the data by state. Pretty easy stuff if you're very familiar with Excel, rather daunting if you're not.

A third solution is PawCOM (www.multiwareinc.com). However, this powerful solution also isn't for the faint of heart. You can download a free demo, though, so it's worth trying out. If you're up to speed with Microsoft Access, this tool easily exposes all of your data, so you could create the report that way.

In any case, you've bumped up against one of Peachtree's limitations where the software developers made almost whimiscal decisions as to what fields one might wish to base a report on.

[JSimpson]

This may not be a very elegant solution, but I have setup the State code under "Customer Type" field. This lets me print reports fairly easily by state.

[bluegal]

Have you thought of setting up a different GL Sales account for each state?

[Deborah Bean]

Depending on how often you do this and for what quantity of records, don't forget that you can also export a report to Excel and fool with the data there. In some cases, I find that this is the easiest solution.

[dringstrom]

I think that JSimpson's method of putting the State ID in the Customer Type field is the most elegant solution to the problem. It's quite clever, and a great illustration of a use for the Customer Type field. An easy way to populate this field is to export the customer ID and state to a .CSV file. Then, reimport this into Peachtree, but update the customer ID and customer type fields. You can then use the built-in functionality of Peachtree and the Excel button to easily segregate sales by state. In particular, look at Pivot Tables and the Subtotal feature within Excel.

[bluegal]

Personally I hate having to export information to Excel and fool around with it; I want a report ready to go. If you're just looking for the total dollar figure by region per month and YTD, setting up a different GL account per region, and selecting the proper one while invoicing separates the sales by regions nicely, and the report is then available from the Income Statement.

If other information is needed from the customer files along with the sales figures, then you need to go another way.:rolleyes:

[DLByma]

We deal with this issue by assigning sales reps to each customer. For example if we have a customer(s) in TX. We assign a sales rep named Texas. You can then print a report that is ordered by sales rep giving you the customers total for commissioned and non-commissioned sales and a percent of total sales. It also totals the territory amount. It works very well and we actually use the report to compared actual sales by region to projected sales. Good luck.