Inventory Valuation by Branch

If you're interested in extracting data from HD on your on a great place to begin is a workbook to display your inventory valuation by Branch.  If you need help with creating an ODBC connection or selecting files you can go to the early April posts using the links below.

ODBC Setup For HD Files

Extracting Data from the Production Database

Select the Branch Item Master (IVPMSBR) and the Item Master (IVPMSTR) files from HD1100PD.  Join the files by the Item Number (IVNO07).

Select the following fields for your workbook:

Branch Number (IVPMSBR/IVNO10)

Product Number (IVPMSTR/IVNO04)

Product Description (IVPMSTR/IVDN01)

Quantity on Hand (IVPMSBR/IVQY01)

Weighted Average Cost (IVPMSBR/IVAMW6)

Extended WAC (IVQY01*IVAMW6)

To limit the number of records that will be returned to the worksheet we'll select a single Branch and only items that have a Quantity on Hand that is not equal to zero.  This criteria can be added by either clicking Criteria > Add Criteria and using the displayed dialog box or by clicking View > Criteria to display the Criteria field and typing your criteria directly in the Criteria Fields and Values boxes.  Either way you choose you'll need to enter IVNO10 equal a specific Branch (I used Branch 9 from our test database) and IVQY01 not equal to zero as below.

Click the button with the exclamation ( ! ) to run the query to display the data.  If the data appears correct then either click File > Return Data to Microsoft Office Excel or click the fourth button from the left; either will return your data to the worksheet.  If you're using Excel 2007 the window below will display.  Click OK to load the data beginning in the default location (cell A1) or change to another location if desired.

In Excel 2007 the data will return as a table as below:

Now, it's simply a matter of formatting your "Inventory Valuation by Branch" report the way you want.

In a future post we'll add the ability to change the query results by changing the Branch Number by changing the value in a cell on the workbook.

As always, if you have any questions related to this post or anything related to Excel leave a comment here or email me at the office.

G/L Reports Made Easy - Cont.

The workbook for G/L Reports has been completed.  Last week I sent the final version to Kara at Midway and she used it to put together a fantastic workbook for her financial reports.  There is an Index worksheet with hyperlinks to the other sheets in the workbook each of which has a hyperlink back to the Index.  She also included a macro to print the various worksheets with the click of a button which is a very nice touch!  I wish I could show you samples of the worksheets themselves but as they obviously contain private information that will not happen but the index is shown below.

Kara did a great job on this workbook and I appreciate her help in the basic workbook which allows her to easily pull in her G/L data.  The project proceeded much faster because of her help.  If you would like a copy of the basic workbook (not Kara's) for you accounting department's use you can request it here or email me at the office for details.

More information to come in future posts regarding the various elements of this workbook.

How Is The Success Of Your Company Tracked?

Every company with which I've ever been associated tracks their successes and or failures in some way.  It can be as simple as looking at inquiry screens for Sales Analysis, Inventory Management and Accounts Receivable or something more complex.  Many businesses have chosen to implement a BI tool as several of my Customers have done with our BI offering.  Even if you're not ready to take the plunge into a full blown BI solution you should be thinking about what measurements you're using and how they might be improved on.  It might be just making the output easier for your executives to get the information they need.  A good example of this might be the workbook posted here on May, 10.  It's an enhancement of Sales Analysis Option #1.

This inquiry screen in HD...

Can become this worksheet in Excel...

Hopefully you'll agree that the worksheet not only has more information but is also presented in such a way that it can more easily be digested by a busy executive.

One of my customers uses Total Inventory overlaid with Cost of Goods Sold as a Key Performance Indicator (KPI).  We've not discussed how he views this data but I do know that he gets the raw data from accounting and then does something manually to get it in the format he wants.  This process could be automated and might look something like this...

As with the previous worksheet the user can select from a drop down what is to be displayed (Company, Division, Region or Branch).

One thing that I've not previously mentioned and of which you might not be aware, Excel workbooks can be opened via a function key added to HD screens.  It requires a CL program and a change to the DDS for the screen to add the function key.  Not difficult and makes accessing the workbook just a key entry away.

In future entries we'll take a look at additional KPI's that you might find useful.  As always, comments and questions are welcome and can be posted here or emailed to me at the office.

Protect or Unprotect All Worksheets in a Workbook

Every have those workbooks with a significant number of worksheets that need to be protected so you go through each one and protect the worksheet and then two days later find it necessary to go back through and unprotect each one of them?  Well here's some code I came a cross on the internet that you can use to speed up the process.

This macro will unprotect all the worksheets and the following macro will protect them all.  Copy and paste into your Personal workbook for use on any workbook that you have open.

If you're  unfamiliar with using the Personal workbook there was a post here back in April that will help you with it's use

I have another version of this code that will display a message box for the entry of a password when protecting or unprotecting the worksheets.  If you would like a copy of that code leave a comment here on the blog or email me at the office.


Sub Unprotect_All_Sheets()

Application.ScreenUpdating = False
Dim I As Long
 For I = 1 To Sheets.Count
    Sheets(I).Select
    ActiveSheet.Unprotect
 Next I
Application.ScreenUpdating = True 

End Sub



Sub Protect_All_Sheets()

Application.ScreenUpdating = False
Dim I As Long
 For I = 1 To Sheets.Count
    Sheets(I).Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
Next I
Application.ScreenUpdating = True

End Sub

G/L Reports Made Easy - Update

I've received some great input and help from Kara and Darin on this project.  The first attempt was not particularly successful but turned out to be a springboard for some much better ideas on making the creation of financial reports easier.  

Initially the idea was to set up a workbook that already contained formulas and these formulas referenced input cells where a user could enter a G/L Account number or range of numbers and the result  would be displayed.  This seemed like a good approach but turned out to be too restrictive and cumbersome to use. 

The next attempt incorporated creating a table where each account could be categorized (i.e. Sales, Cost of Goods, Warehouse Expense, etc.) and this has the makings of being very workable.  The table is easily maintained using Excel and is available for  any workbook that is connected to the HD database.  When this table is joined to one of the G/L files and the data is loaded into a worksheet named ranges were created for the Category, MTD Amount, YTD Amoutn, Current Month Budget and  YTD Budget.  This is done for each of the three G/L files.  The result is a very manageable five named ranges for each of the three files.  Now the user can enter a formula in any cell and return the data for a specific Category of accounts for a particular period (i.e. =SUMIF(MCODE,"Sales",MCURAMT) will return the Total MTD Sales for the Current accounting period).  An alternative and preferred method would be to replace the name of the Category, Sales, with a cell reference so the formual becomes =SUMIF(MCODE,B10,MCURAMT).  This allows the user to change the results by simply changing the value entered in cell B10 and more importantly allows the formula to be copied down the desired number of rows.  Now the user simply types a valid Category in column B and the Amount for the Current Month is displayed in the formula column as below.

The date in the second column is from Table File GL03 and will always display the current period.  The third and fourth column dates are based on the selection that the user chooses in a table named "Periods".  The data displayed in columns three and four is based on the Periods selected by the user.

Hopefully, within the next week the workbook will be completed and ready for user testing.  At that time there will be a series of posts detailing the various elements used in creating the workbook.  These posts will include such things as the parameter queries used to extract the previous month and prior year data.  The drop down list of only closed months in the current year that automatically adjusts as accounting months are closed or reopened. 

As always, if you have any questions about this or anything related to Excel, please, post a comment here or email me at the office.

Free Workbook goes to Dan at EJP

The free workbook offered a couple of weeks goes to Dan at EJP.  His suggestion was for a Sales Backlog workbook.  

Here's the main worksheet.  The drop down box allows the user to select ALL, a Region or a Branch.  The MS Query executes when the selection is changed and the refreshed data is displayed.  

The table in the upper right of the worksheet displays a summary of Count and Amount  by Transaction type.

The "Branch Detail" button will display a summary by Branch by Order Status as below.  The Radio Buttons on the popup allow the User to select Branch summary by Count or Amount.

The User can select a cell containing a Customer Number and click the "Cust Detail" button to display the following worksheet.  

This worksheet displays all Open Orders for the selected Customer, the Customer Name and if over Credit Limit the warning box showing the amount by which the account is over the limit.

Clicking the "Cust Info" will display the current Account Aged Balance, Type of Account, Salesperson to which the Account is assigned and the Credit Limit.

Thanks Dan for a great suggestion.

A Gantt Chart using Excel

This is a workbook being used here at the office for a MyHD Implementation.

If you would like a copy of this workbook to use for tracking your projects just let me know and I'd be happy to forward it to you.

Free Workbook Offer

There's been very little interest in the offer last week of a free workbook connected to your database but you still have time.  Post a response or email me at the office and I'll consider it for my next project and if selected you'll get a free copy but the offer ends on May 20.

Workbooks Being Used by Our Customers (cont)

Here's a workbook done for one of my Customers that allows the user to select the FY, the Purchase Book Section and either Branch, Region or ALL.

 The "Totals by Branch" obviously displays the Branch totals but also includes % of Total Sales for each Branch.

Interuption of Blog Service

Unfortunately the blog service has been having some issues the last couple of days so if you've left any comments they have apparently been lost.

Here is one suggestion that was left as a comment but was also emailed to the office.

"Another one I would like to see is a realtime snapshot of a Sales Backlog workbook that would basically show by Branch and Company total,  all OPEN ORDER types. If it was realtime, the user could, as the day goes by, check what is being placed in the hoppers. May not be exact because of the changes that can occur in orders, but it might be a good look at what is coming in for sales. Maybe number of orders by type and sales dollars based on what is in the orders at the time."

If you left a comment please try again.  Hopefully they have the issues resolved!

Because of the issues with the site I checked to see if anyone had viewed any of the posts in the last couple of days and was pleased to see that there has been quite a bit of activity.  There have been 13 views today and there were 31 yesterday.  Below are the views by Country for the last month.

How About a Free Workbook of Your Choice?

The first post on this blog was on April, 14.  Since then 9 people have chosen to "follow" the blog and there have been 261 visits to the blog.  I have no idea if that's good or bad and it's not particularly important.  To me that just fall into the category of what Dolly used to call "It is what it is"!  I am glad that "someone" is looking at it and hopefully some of you have found it, if not useful at least interesting.

Here is the thing that is interesting to me.  In almost one month there has not been a single comment or question.  I would think that at least once or twice in 261 visits someone would have had something to say.  Of course it's possible that those hits were by 261 different people but that's unlikely.  Oh well, it is what it is.

That aside, after the SAI #1 workbook I'm looking for a new project so I'm open to suggestions and here's my offer to you.  If you make a suggestion for a project that extracts data from HD into Excel and I use it I'll send the final workbook to you for free.  If I have access to your database I'll do it using your data.  If not, when complete I'll send it to you along with instructions on how to connect it to your iSeries.  That should be a heck of a deal.  At our current rates the SAI #1 workbook would have cost about $4,500.00 and you can get something similar for free.

So....anybody interested?  You have until May 20th to get your suggestions posted as comments here or you can email me at the office.

SAI Option #1 - the Final Version (maybe)

I believe I'll call this one finished.  I have to do some data validation but I don't believe there will be any additional "structural" changes/additions.  I would guess that this took about 24 to 30 hours to complete but it was worth the effort.  I've learned several things related to extracting data from the Sales Analysis files (SAPMSSM, SAPMSSY and SATHITB).  I was surprised to find that, when completed, the workbook contains 8 queries and 97 named ranges.

In future posts we'll take a detail look at some of the elements that make up this workbook.  One of the first will be using radio buttons to display different charts. Then maybe using the "Camera" to display the "Inv Summary".  How to include the year in a header so it automatically changes when the year changes.

If you have any questions about this workbook you can leave a comment here or email me at the office and I'll respond to you as soon as possible.

SAI Option #1 - Update

Some progress over the weekend.  Here's where it's at now.......

More later this week.

SAI Option #1 in Excel

Here's another project I'm working on that might be of interest to some of you.  This came about because of one of those conversations that begins "You know I really like ...... but it would be better if ....."  This particular "I really like" was SAI Option #1 "Display Sales Status".

I've just started on this but thought I'd share with you what I have so far.  The "Branch >>" drop down allows the User to select "ALL" Branches, by Region or by Branch Number.  One of the "it would be better if" comments was to be able to select specific Sections so this has been added.  At this stage  this is just an input field but will be changed to a drop down or at least provide a way to search on Section codes by description.  The "Your Company", "Your Region" and "Your Branch" fields will be replaced with the actual Company, Region and Branch Names based on the selection made in the Branch drop down.

The "Invoice Summary" screen accessed via the F6 key is displayed by clicking the "Inv Summary" button on the worksheet.

This will display a popup of the invoice information.  The additional information displayed was also a "better if" statement.

The Sales Forecast numbers are also going to be included in the next few days.

As always any input, suggestions or questions you might have would be appreciated.

G/L Reports Made Easy (Hopefully)

For some time now I've wanted to come up with a way for non-IT users to be able to easily and quickly develop G/L Reports.  With that goal in mind I've been working on a workbook that (to begin with) pulls all the G/L Master records for the current month which will be "pulled" into the report by entering a range of valid G/L Accounts.  One of my Customers was kind enough to assist me with this but thought I would use the Blog to see if anyone else is interested in this project and if so would be willing to provide input.  Below is a prototype of the workbook.  For simplification in this workbook I'm only using Main Account Number (GLNO03) and the Branch Number (GLNO06) to pull the Current Month Net Change Amount (GLAM01).  The From and Too Main Accounts and Branch Number are entered in Columns B thru E.  Columns F and G contain formulas to convert the Main Account and Branch into a Code that is used in the formulas that return the Amount in Column O.  Columns J and K contain formulas that display an error message if the Codes in Columns F and G are invalid.  User enters Headings, Sub-Headings, Descriptions and sub-totals in columns L, M, N and P respectively.  

Well, that's what I've got so far.  I would really appreciate any input that you would be willing to share.  Leave a comment or email me regarding any suggestions or if you would like to have a copy of the workbook.

Columns B thru K should be hidden with the report is complete.

HD FILES AND FIELDS

One of the most difficult things to do when first learning to write queries is finding the data that's required for a report.  Following is a listing of some of the most frequently used Files and Fields.

Basic Customer Information

Description	File	Field
Number	ARPMCUS	ARNO01
Name	ARPMCUS	ARNM01
Mailing Address	ARPMCUS	ARAD01
Shipping Address	ARPMCUS	ARAD04
Mailing City	ARPMCUS	ARCY01
Shipping City	ARPMCUS	ARCY02
Mailing State	ARPMCUS	ARST01
Shipping State	ARPMCUS	ARST02
Mailing Zip Code	ARPMCUS	ARZP15
Shipping Zip Code	ARPMCUS	ARZP16
Discount Profile #	ARPMCUS	PRNO03
Credit Limit Amt	ARPMCUS	ARAM01
Branch Number	ARPMBAL	ARNO16
Salesman ID	ARPMBAL	ARID01
Cust Credit Rep	ARPMBAL	ARID05
Cust Service Rep	ARPMBAL	ARID08

When using the Customer Master File (ARPMCUS) and the Customer Balance File (ARPMBAL) you will need to join the files by the Customer Number (ARNO01).

When VLOOKUP won't work

If you've been using Excel for any length of time you've probably had occasion to use the VLOOKUP function.  This is a very useful function that allows you to look up a value in a range of data and return corresponding data from another column.  Unfortunately VLOOKUP will only return data from columns to the right of the look up column.  Here's a way to return data from a column to the left of the look up column.  To do this we'll use two functions, INDEX and MATCH.

First let's look at INDEX.  INDEX requires three arguments
=INDEX(data range, row number, column number).  In the table below our data range would be A1:F13.  If we entered =INDEX(A1:F13,7,4) the function would return My HD since that is the value in Row 7 and Column 4.  To become a useful function we need to be able to change the Row argument without typing in a different number.  To do this we use the MATCH function

The MATCH function also requires three arguments
=MATCH(value, single column data range, match type) where value is the value being searched for, data range is the column containing the value being searched for, match type is either 0 for exact match, 1 for less than or -1 for greater than.  In the above table =MATCH("BCM",C1:C13,0) would return 8.  This is the row number where BCM is found.

When we combine the two functions we can return data to either the left or the right of the search value.  If we enter =INDEX(A1:F13,MATCH("LMP",C1:C13,0),2) in cell J4 the function will return 201 which is the Branch Number for Leona.

One more step and we have a useful user function.  Instead of typing "LMP" in the formula we reference a cell, in this case we'll use J2.  Now we have the following formula:
=INDEX(A1:F13,MATCH(J2,C1:C13,0),2).  When the value in cell J2 is changed the Branch Number corresponding to that Sales ID will display.