Random Stuff

8/30/09

Sales Tax Simplified

It seems like the Dynamics GP forums are alive with sales tax questions lately. So, I thought I might tackle this one in the same manner that I do in the classroom-- by breaking it down in to steps. I treat sales tax as a three step process.

1. Document Default
2. Item or Setup Default (depending on if you are working with Receivables or Sales Order Processing, for example)
3. Tax schedule comparison

So, for this post, we are going to keep it simple and look at taxes in Receivables Management and Payables Management. I promise another post on Sales Order Processing and Purchase Order Processing next week :) But, for now, lets keep it simple.

Let's start with Step 1. There is one key setup that can impact the tax schedule that appears on a document (whether it be Payables or Receivables) by default. In your company setup, Tools>>Setup>>Company>>Company>>Options, there is a setting to Use Shipping Method to Determine Default Tax Schedule.

If this option is marked, then the tax schedule that appears on the transaction will be dependent on whether the shipping method is a pickup or delivery method (more on this later).
If this option is unmarked, then the tax schedule that defaults on the transaction will always be the Vendor Address' schedule (on Payables) or the Customer Address' schedule (for Receivables)

Now, let's look at Step 2. Now, Step 2 assumes that in your setup you have chosen Advanced for your tax calculations (Tools>>Setup>>Purchasing/Sales>>Payables/Receivables>>Options) rather than Single Schedule. Single schedule will always charge according to Vendor or Customer Address' schedule without Step 2 or Step 3.

In Step 2, we need to find the setup schedule (in Payables or Receivables). This is the schedule specified for each amount (Purchases, Sales, Freight, Misc, etc) in the setup options window mentioned above. In most examples in training manuals, these schedules are setup to include ALL DETAILS.

So, in Step 3, the schedule from the document (Step 1) is compared to the schedule from setup (Step 2). Details that the two schedules are compared, and only those that exist in both places are calculated.

Easy right? I know, I know..you are probably saying, huh? So, lets look at an example. Let's say that I have the following tax schedules set up:

MOTAX: Contains the MOSTATE, KCCITY, MOSPEC, and JCKCTY Details which will be assigned to my Missouri based customers
KSTAX: Contains the KSSTATE and KCKCITY Details which will be assigned to my Kansas based customers
VALID: Contains the MOSTATE, KCCITY, JCKCTY, KSSTATE, KCKCITY which contains all of the currently valid tax details. I have purposely left off MOSPEC, it was a special tax that was only charged last year in MO and is no longer valid.

I set up my customer ABC AUTO with the tax schedule MOTAX, and I assign KSTAX as the default tax schedule for sales in Company Setup. VALID is specified in my Receivables Management Setup as the tax schedule for Sales.

Let's assume that we have chosen to have shipping method determine default tax schedule. So if I enter a Receivables invoice for ABC AUTO, the tax schedule will default as follows:

Shipping Method with a delivery type: MOTAX (from Customer Address)
Shipping Method with a pickup type: KSTAX (from Company Setup)

So, lets assume I use a Shipping Method with a delivery type, so MOTAX defaults. This would then be compared to the VALID tax schedule specified in setup. What is in common?

MOSTATE, KCCITY, JCKCTY are the only taxes that would be calculated, since MOSPEC did not exist in the tax schedule in Receivables Management setup for comparison

I think of the tax schedule specified in setup as my "control" schedule, containing all valid tax details to be used in comparison against the documents. In many cases, this would be all details. But in some cases, as outlined above, it may be easier to remove a tax detail from the one control/setup schedule than to remove it from all schedules that contain it. This is also a great approach if a tax is only valid (or invalid) for certain portions of the year (think of sales tax holidays).

Please post back your questions or comments or examples to help with the understanding. I promise more next week on distribution. Have a great weekend!

PART TWO

Okay, okay, okay, please feel free to give me a very hard time for the delay in posting part two in our compelling sales tax saga. Last time, we worked through the basics of defaulting and comparing tax schedules for receivables and payables transactions. So, now we can move on to Sales Order Processing and Purchase Order Processing, where inventory items can impact the taxes that are calculated. The examples that follow assume that you have marked to use shipping method to determine default tax schedule in your company setup (as discussed in our previous post). Again, let's look at taxes as a three step process.

1. Document Default
2. Line Item Default
3. Tax Schedule Comparison

So, step one works pretty much the same as it did in our earlier post. However, we need to take it a step further by considering the inventory site, like the following examples (assuming you are registered for inventory) in Sales Order Processing:

Shipping Method Delivery: Default tax schedule assigned to Customer's Ship To Address
Shipping Method Pickup: Default tax schedule assigned to the Site

Now, although I call this a "document" default, because Shipping Method and Site can vary by line item in Sales Order Processing and Purchase Order Processing, this default actually is stored by line item.

So, what about Step 2? Well Step 2 takes in to consideration the inventory item card (or the Sales Order/Purchase Order Setup when dealing with non-inventory items). There are three possible settings for inventory items for Sales and Purchase taxes:

Nontaxable: No tax is ever charged for this item
Base on Customer/Vendor: This will charge according to the shipping method
Taxable: Specify a tax schedule that represents all possible tax details to be charged on this item, more on this later...

So, let's work through a couple examples. First, let's look at the Base on Customer setting. Let's assume that we have entered an invoice for our customer ABC AUTO. They want all items to be shipped to them (Shipping Method- Delivery) at their Missouri location which is assigned to the tax schedule MOTAX which includes the MOCITY and MOSTATE tax details.

Let's say that ABC AUTO buys two items from us, the first item is a gift basket, BASKETOFUN. BASKETOFUN is set with the sales tax option of Base on Customer. So what does that do? If we were to click the item number expansion arrow for this line in Sales Transaction Entry, we would see the following information being stored in the Sales Item Detail Entry window:

Shipping Method: Delivery
Ship To Tax Schedule: MOTAX
Item Tax Option: Base on Customer
Item Tax Schedule: n/a

Therefore, in this example, all details in MOTAX would be calculated. Now, let's shake it up and say that this particular line item changed to a Shipping Method of Pickup. Let's say that this line item is being sold from our NORTH site with the tax schedule NYTAX. We would then see the following in the Sales Item Detail Entry window:

Shipping Method: Pickup
Site Tax Schedule: NYTAX
Item Tax Option: Base on Customer
Item Tax Schedule: n/a

So, in this scenario, what taxes would be calculated? All details in the NYTAX schedule would be calculated. Sometimes this scenario is confusing to users, because the item is set to Base on Customers. But what that really means is that the item's taxability is based on the Shipping Method itself, and how the tax schedule defaults.

But, wait, I said that we sold two items right? So let's look at the next item that ABC AUTO bought. They bought our new virtual gift basket, VIRTUALBASKET. Now, some states tax the virtual gift basket, and others do not. So we have set up the VIRTUALBASKET with a sales tax option of Taxable. We have then assigned a special tax schedule to it called VIRTUAL, which includes the taxes that are calculated on virtual products. In our case, let's say that it includes the MOSTATE, KSSTATE, and COSTATE tax details.

If we look at the Sales Item Detail Entry, we would see:

Shipping Method: Delivery
Ship to Tax Schedule: MOTAX
Item Tax Option: Taxable
Item Tax Schedule: VIRTUAL

Which taxes would be calculated? Only those that are common between MOTAX and VIRTUAL, in this case the MOSTATE detail would be calculated.

But, let's mix it up and say that the customer has asked that this item be shipped to their California location which is assigned to the CATAX schedule (which includes the CASTATE and CACITY tax details). So, let's look a the Sales Item Detail Entry again with these details:

Shipping Method: Delivery
Ship to Tax Schedule: CATAX
Item Tax Option: Taxable
Item Tax Schedule: VIRTUAL

Which taxes would be calculated in this scenario? None, because the CATAX schedule does not have any details in common with VIRTUAL. This is correct, since we said that our item was only taxable by Missouri (MOSTATE), Kansas (KSSTATE), and Colorado (COSTATE) not by California.

So, the Taxable option for items creates the most flexibility when you have items that are taxed in some states and other items taxed in all states (or different states). I have found this comes in to play quite often with technology products, including software downloads, where some states are more aggressive in taxing these items than others.

Clear as mud? Share you questions, hints, etc and I am happy to update the post--from Alaska, where I am this week teaching :)

8/29/09

Land of the Landed Costs- Part 1 & 2

Landed cost is the total cost it takes to "land" an item on your doorstep, including additional charges like freight, customs, and processing. More and more, I find that companies are interested in landed cost as they increase the use of international suppliers and manufacturers. By using landed cost, the "total" cost of the item is reflected in inventory and is valued as an asset, rather than immediately expensing those additional costs. Take the following example, where I purchase a widget from my European supplier:

Purchase 100 widgets @ $20/each
Overseas shipping costs are $1500
Customs, Duties, and other Processing Fees are $500
Domestic shipping costs are $400

If I were to value my widgets only at the cost from my vendor, the cost in inventory would be $2000 for the 100 widgets at $20/each. And then I would expense the additional $2400 in costs.
The situation changes though, if I am using landed costs. Using the same costs above, I would value my inventory at $4400 dollars in total, or 100 widgets at $44/each. Perhaps a more accurate picture of the inventory value? In turn, this increased cost will affect my Cost of Goods Sold on the sales side, impacting my margin appropriately.
So, now that we have an example, how about talking through how Landed Costs work in Dynamics GP? With Landed Costs in Dynamics GP, these costs can be:

Recorded as an estimate amount on a receipt
Matched to an invoice and revalued to provide a more accurate amount

There are five key aspects to Landed Costs in Dynamcs GP from my perspective:

Landed Cost Setup: Includes setup of individual landed costs, landed cost groups, and assignment to item/site combinations
Purchase Order Landed Cost: Assigning landed cost groups to POs
Receivings Landed Cost: Recording of landed cost groups on Receipts
Receivings Landed Cost Apportionment: Apportionment of landed costs across an entire receipt
Invoicing Landed Cost: Recording invoices for landed cost vendors and matching them back to the original product receipts

Let's take a look at each aspect. In this post, I will cover the setup items. And in my next post, I will cover the actual transaction entry. First, Landed Cost Setup.

Cards>>Inventory>>Landed Cost

This is where you can set up each individual landed cost that you plan to track. If you plan to match invoices for the landed cost, you will want to put in a Vendor ID. Matching invoices for landed cost means that (in this example above), I could record the actual insurance invoice from Associated Insurance and match it back to the shipments from my supplier for the product I purchased.
You can also select a cost calculation method, this will be how the landed cost estimate on the receipt will be calculated. If you have chosen to match invoices, the invoice amount will be compared against the original estimate on the receipt. The resulting difference can be revalued, if Revalue Inventory for Cost Variance is marked. Otherwise, the difference will post to the Purchase Price Variance account.
The GL account specified are used in the following manner:

Accrued Purchase Account: Used as the offset to the landed cost estimate on receipts, and cleared when the amount is invoiced or matched to invoice.
Purchase Price Variance Account: Used for the variance between invoice and receipt when matching invoices but not revaluing.

Note that no inventory or expense account is specified because the item's account will be used, since landed cost is updating the inventory cost/value of the item.

Cards>>Inventory>>Landed Cost Groups

Landed Cost Groups are used to organize landed costs in to sets that can be assigned to item/site combinations and/or to document line items like Purchase Orders and Receipts. An individual landed cost can exist in more than one landed cost group. You might choose to have landed cost groups to represent a set of landed costs for:

A specific location
Certain items
A type of shipping process

In my example above, I have chose to set up a landed cost group for my domestic shipments. It includes both the insurance costs and the freight costs. Now, I can assign this landed cost as a default for certain combinations of items and sites.

Cards>>Inventory>>Quantities/Sites

This step is not required. However, if appropriate, you can assign a landed cost group to a specific item/site combination in this window. Doing this will cause the landed cost group to default on the purchase order line item, where it can be changed if necessary.

More soon on the transaction side of all of this fun!

So, here I am with part two of the landed cost saga finally. Last time, we went through the process of setting up landed costs. In our example, we were setting up insurance costs so that the invoice from the insurance vendor could be matched back to the shipments received from the product vendor. But, before we get down the road that far, let's overview the three key ways that landed costs can be used.

Landed Costs by line item

Specify a landed cost group on a line item when recieved (can default from the item setup, from the purchase order, or manually entered)
Calculated amounts can be adjusted by line item on receipt

Landed Costs by apportionment

Specify an individual landed cost for the entire receipt document
Apportion the amount across the items on the receipt by qty, extended cost, or weight
Landed cost must use flat amount cost method

Landed Costs by invoice match

Landed cost group specified by line item on receipt (default from item setup, or from the purchase order, or manually entered)
Does not have to calculate an estimate amount on receipt, but it can.
Individual landed cost must be set up for Invoice Match, and for Revalue for Cost Variance if applicable
Use Enter/Match Invoices to enter an invoice from the landed cost vendor, and match it to the shipment from the product vendor

So, let's take a look at each of these options. In Landed Costs by line item, we can specify a Landed Cost Group on the Purchase Order line item.

Transactions>>Purchasing>>Purchase Order Entry, click on a line item, and then click the expansion arrow to the right of the Item header.

Remember, this value defaults from the item/site combination in Cards>>Inventory>>Quantities/Sites. However, it can be changed here to provide a different default to the receipt.

So, when we get to Transactions>>Purchasing>>Receivings Transaction Entry, the landed cost group will appear on the line item once again. You can view it in the same way as you did on the purchase order, by clicking on the line item and then clicking on the Item header expansion arrow. However, you may find it more helpful to view the actual landed costs being calculated for the line item, based on the landed cost group assigned. This can be done by clicking on the line item, and then clicking on the expansion arrow to the right of the Unit Cost header.

You can override the amounts specified here. The percentage or amount fields will be available based on the calculation method you defined for the landed cost.

Also, on the receipt, we can do Landed Costs by Apportionment. To do this, we simply click on the Landed Costs button at the bottom of the Receivings Transaction Entry window to open the Receivings Landed Cost Apportionment window.

In this window, you can select Landed Costs to apportion across the entire receipt. You must select a landed cost that is setup with a flat amount calculation method in order to select quantity, value, or weight in the "apportion by" field.

The system calculates each method as follows:

Quantity: (Line item's quantity shipped - the quantity rejected)/(Sum of all line items' quantity shipped- the quantity rejected)
Value: [(Line item's quantity shipped -the quantity rejected)*Originating Unit Cost]/Sum of all line items [(quantity shipped -the quantity rejected)*Originating Unit Cost]
Weight: (Line item's extended shipping weight)/(Sum of all line item's extended shipping weight)

In either case, Landed Cost by Apportionment or Landed Cost by Item, the distributions that result are the same:

Debit to Inventory
Credit to Accrued Purchases for Landed Cost (per Landed Cost Maintenance)

So, that leaves us with the last method of Landed Cost by Invoice Match. In this example, let's assume that we posted the estimated landed cost of 10% of extended for the INSCARRIER landed cost on the shipment receipt. Now, we have received an invoice from the actual insurance carrier, Associated Insurance, and find that the costs were actually much greater. Since we set up the INSCARRIER landed cost for invoice match, we can now record the invoice from Associated Insurance and match it back to the shipment from Advanced Office Systems.

To do this, we go to Transactions>>Purchasing>>Enter/Match Invoice and enter an invoice for Associated Insurance (NOT Advanced Office Systems):

There are just a few key differences in how you enter the landed cost invoice:

Vendor ID is the landed cost vendor
Mark the "LC" checkbox for the line item to identify it as a landed cost
Select the landed cost to match for the item (rather than an actual item)
Match the landed cost to the original shipment from the product vendor using the Matched to Shipment expansion button

The distributions that result depend on whether you have selected to Revalue Inventory for Cost Variance:

If you are revaluing:

Debit to Inventory (if cost is greater than receipt)
Debit to Accrued Purchases for Landed Cost
Credit to Accounts Payable

If you are not revaluing:

Debit to Purchase Price Variance for Landed Cost (if cost is greater than receipt)
Debit to Accrued Purchases for Landed Cost
Credit to Accounts Payable

So, I hope this helps clarify the different ways to approach landed cost in Dynamics GP. I find that some clients will use all three methods, but many also settle on one or two ways that work best for their goals. Please share your experiences, questions, etc.

Remote Desktop Nirvana

Every once in a while, I stumble across some feature, option, utility, or application that simultaneously makes me say "Wow! That's amazing!", and "D'oh! I should have known about that!". These are great features / options / utilities / apps that are no secret, but aren't necessarily well publicized.

I received an e-mail at 9:30pm this Saturday evening from a client who suddenly started getting errors from a GP related SQL Server job. We started a GoToMeeting session to look at the SQL Server issue, and as soon as I saw his desktop I noticed something out of the ordinary. He said "We're looking at Server A now, but if you want to look at Server B, just click it in the list on the left."

This is what I saw:

Never having seen this before, I asked him if this was some type of 3rd party server management app that he uses--perhaps like a VNC or Citrix management console of some kind, something I have seen at other clients. "No, it's just part of the Server 2003 Admin Pack."

Sure enough, this is a fantastic utility included in the free Windows Server 2003 Administration Tools Pack. The tools pack includes several tools, but the one I was most interested in is the "Remote Desktops" MMC snap-in.

The Tools can be downloaded here. Just make sure to download the SP1 version, as the original version, which is still available for download, does not allow you to connect to a Terminal Server running on a port other than 3389. After installation, the Remote Desktops app should appear under your Administrative Tools program group.

This MMC snap-in allows you to save multiple RDP profiles in a single place, avoiding the need for multiple RDP files ( I currently have 13 of them sitting on my desktop) or hunting through the MRU (most recently used) list on the Remote Desktop Connection app window. Once you setup the server connections, with just a single click on a server name on the left, you can quickly login to a server and also switch from one RDP session to another.

It doesn't offer all of the connection options of the Terminal Services Client, but I generally don't need the extra options:

And something that I find very valuable: It allows you to have an RDP session that will automatically fill the MMC window, regardless of size or aspect ratio. You can choose to fill the MMC window, choose a standard desktop size, or even specify a custom resolution.

So I'm no longer limited to the standard 1024, 1280, or full screen options. (That is actually the only feature I appreciated in Virtual PC vs. virtual server--the ability to dynamically resize the VPC window.) I like the size and convenience of the Terminal Server Client full screen mode, but it's a hassle when I need to minimize or move the RDP window out of the way, or when I have to work with multiple sessions in full screen mode.

The Remote Desktops app effectively let's me have several simultaneous near-full-screen sessions in a single desktop app. Very handy when jumping between different servers throughout the day.

If you are only working in a single RDP connection at a time, the standard MS TSC app in full screen mode is great, as it gives you ALT+Tab, the Windows key, and other shortcuts that aren't available otherwise. But if you have to bounce between multiple servers, the Remote Desktops utility is much more convenient.

Because it is an MMC snap-in, it does have the standard MMC navigation and UI quirks, but given what it offers, it's a great solution.

UPDATE: Reader Jivtesh brought up an excellent point: What about this feature in Server 2008? He informed me that it is a standard feature in Windows Server 2008, but after looking for it, I didn't see an icon for it under Administrative Tools.

It appears that it may not have an icon by default in Server 2008, but it is present by default, and is very easy to get working.

Here is the KB article with instructions.

To open Remote Desktops from the MMC

Click Start, click Run, type mmc in the Open box, and then click OK.
On the File menu, click Add/Remove Snap-in.
In the Available snap-ins list, click Remote Desktops, and then click Add.
Click OK.

You can then do File --> Save As to save an MSC file that you can launch to open Remote Desktops.

Sales Tax Simplified

If this option is marked, then the tax schedule that appears on the transaction will be dependent on whether the shipping method is a pickup or delivery method (more on this later).
If this option is unmarked, then the tax schedule that defaults on the transaction will always be the Vendor Address' schedule (on Payables) or the Customer Address' schedule (for Receivables)

Easy right? I know, I know..you are probably saying, huh? So, lets look at an example. Let's say that I have the following tax schedules set up:

MOTAX: Contains the MOSTATE, KCCITY, MOSPEC, and JCKCTY Details which will be assigned to my Missouri based customers
KSTAX: Contains the KSSTATE and KCKCITY Details which will be assigned to my Kansas based customers
VALID: Contains the MOSTATE, KCCITY, JCKCTY, KSSTATE, KCKCITY which contains all of the currently valid tax details. I have purposely left off MOSPEC, it was a special tax that was only charged last year in MO and is no longer valid.

Let's assume that we have chosen to have shipping method determine default tax schedule. So if I enter a Receivables invoice for ABC AUTO, the tax schedule will default as follows:

Shipping Method with a delivery type: MOTAX (from Customer Address)
Shipping Method with a pickup type: KSTAX (from Company Setup)

So, lets assume I use a Shipping Method with a delivery type, so MOTAX defaults. This would then be compared to the VALID tax schedule specified in setup. What is in common?

MOSTATE, KCCITY, JCKCTY are the only taxes that would be calculated, since MOSPEC did not exist in the tax schedule in Receivables Management setup for comparison

Please post back your questions or comments or examples to help with the understanding. I promise more next week on distribution. Have a great weekend!

8/12/09

Microsoft Dynamics GP Database Maintenance Utility

How many times have you performed an upgrade just to realize with the first posting that some stored procedure is missing or did not -- for some misterious reason -- get created, yet the upgrade completed with no errors? This is especially true when upgrading from several versions aback, when the upgrade path is not too clear, or you have forgotten to apply that critical service pack that would avoided some of the missing objects issues you are now experiencing.

I recently came across a user who wanted to recreate all SmartList Builder objects because they suspected something was wrong with the installation, however, they did not want to have to use the backend to recreate all the stored procs used by SLB, because of the "fair of missing something or endup damaging something else"... you know what they say... the client is always right! (not always, but that's the topic of another article).

After scratching my head for a few moments, the answer came in the form of the new Microsoft Dynamics GP Database Maintenance Utility, but first some history...

Before there was the SQL Maintenance utility, back in the old days, the former Great Plains Software provided reams of text files containing scripts to recreate any missing or damaged system and/or company database object. These scripts could be found on the installation CDs and could be accessed via the old SQL Server Query Analyzer tool. All you needed to do was to open one of script files, do a search for the desired stored procedure, copy and paste into Query Analyzer, and execute the script against the appropriate database. This sounded simple enough until system administrators (we will not call them users for fear of retaliation :-) ) began corrupting the script files and introducing their own code... It was funny, because Great Plains Support used to asked as part of their support script if you had modified X or Y stored procedure for the problem you were calling for. When Microsoft took over, came the MSIs and the script files were no longer provided. Now what? There comes SQL Maintenance, however, the set of utilities provided with SQL Maintenance did not allow you to recreate stored procedures, functions, or table triggers. These still had to be obtained from Microsoft. I guess, in the end they figured out it wasn't worth the hazzle and delivered Database Maintenance Utility.

So, lets take a look.

To recreate database objects, you will need to:

1) Launch Database Maintenance Utility. Go to Start > Programs > Microsoft Dynamics > GP 10.0 > Database Maintenance.

Select your server and select an authentication method. Fortunately, Database Maintenance can use Windows Trusted Authentication. Click Next to continue.

2) Choose the company you are going to perform the updates on. My personal preference is to choose ALL databases if you suspect a damage on a stored procedure, function or trigger. Why? Chances are, the stored procedure may be damaged across the board.

Click Next to continue.

3) Select the product for which you will like to rebuild its objects. For this example, I will use SmartList Builder.

Click Next to continue.

4) Mark the objects you would like to recreate. You have the choice of Stored Procedures (includes UDFs too), table Triggers, and Views.

Note that for Tables and table Auto Stored procedures you must use the SQL Maintenance option available within Microsoft Dynamics GP. Click Next to continue.

5) Confirm your selection.

Click on Next to continue.

6) The Utility will run through a progress bar and will return a status of the operations when completed.

If recreating stored procedures, the Database Maintenance Utility will re-grant permissions to all database users in the DYNGRP role, so no need to run the GRANT.SQL utility script.

Hopefully, you use this powerful tool as a first option in recreating any missing or damaged database object, before you call Microsoft or open that support case.

8/10/09

A Better NETWORKDAYS

The following formula does everything that the traditional NETWORKDAYS function does, plus it allows you to select as many days of the week as you want to exclude from the calculations. There are two flavors of the formula. The first version does not allow a list of holidays to exclude from the count. The second version does allow a list of holidays to exclude. Both formulas require a range named ExcludeDaysOfWeek that lists the day of week numbers (1 = Sunday, 2 = Monday, ... 7 = Saturday) to exclude from the calculation.

Formula Without Holidays

=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),ExcludeDaysOfWeek,0)),1,0))

In this formula, StartDate is the data at which counting will begin. EndDate is the last date of the period to count. ExcludeDaysOfWeek is a range of up to 7 cells indicating the day-of-week numbers (1 = Sunday, 2 = Monday, ... 7 = Saturday) to exclude from the count. You may, if you choose to, replace the range reference of ExcludeDaysOfWeek to a hard-coded list of day numbers. For example,

=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),{1,6,7},0)),1,0))

Note that the days of the week are enclosed in curly braces { }, not parentheses.

Formula Supports Holidays

The formula below works must like the previous formula except that it allows you to enter holidays in a range of cells, and those holidays will be excluded from the count. You must create a named range of cells named Holidays that lists the list of holidays to exclude from the result.

=IF(OR(StartDate<=0,EndDate<=0,StartDate>EndDate,ISNUMBER(StartDate)=FALSE,
ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),
ExcludeDaysOfWeek,0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))

The formula is split into multiple lines here for the sake of readability. In practice, though, the entire formula should be on a single line. The parameters in this formula have the same meaning as they did in the previous formula, with the addition of the Holidays list. Holidays should be a range of cells that contains dates to exclude from the count. It does not matter if a holiday falls on a day of week that is listed in the ExcludeDaysOfWeek.

Forecasting Future Demand of Products

In 1987, Gordon Graham wrote a book, Distribution Inventory Management for the 1990s. In this book, Graham described what he considered to be the best method for forecasting the future demand for both seasonal and non-seasonal products. Let's take a quick look at these formulas:

Non-Seasonal Products: Calculate demand for the upcoming month by averaging the usage recorded in the past six months.

Seasonal Products: Calculate demand for the upcoming month by averaging the usage recorded in the upcoming six months, last year, and then applying a "seasonal trend factor" that expresses the anticipated increase or decrease in business experienced over the past year.

These are simple formulas. And at the time Gordon wrote the book, simple formulas were necessary for distributors to successfully manage their inventory:

Many buyers could not effectively deal with mathematical formulas or computers. Ten-key calculators were considered "state-of-the-art" technology. In fact, most purchasing decisions at the time were based on "SWAG" (silly, wild-ass guessing). Any formula (including Graham's) introduced to provide consistency in ordering had to be fairly simple and easily replicated on a calculator.
Computers did not have the power to perform comprehensive forecasting formulas for thousands of parts within a reasonable period of time. Calculating Graham's simple average for thousands of items stretched the physical capabilities of most computer systems.

The demand forecasts produced by the Graham formulas were generally more accurate than the predictions of the guy with the dull pencil and clipboard out in the warehouse. But there was still a considerable difference between Graham-based predictions and what was actually sold. At the time, these deviations were considered "unavoidable," and there was no way around them.

Now consider how market conditions have changed since 1987:

Technology has allowed distributors to expand and increase their market areas. The result: You face more competition than ever. This competition has created more pressure on distributors to consistently have the products their customers want, when and where they want them.
Increased competition has also put pressures on profit margins. Distributors have to offer lower prices in order to retain current business and attract new customers.
The number of new products introduced to the market continues to increase at a rapid rate.

These conditions present some unique challenges:

Decreased margins tend to limit the amount of money a distributor has available to invest in inventory.
Distributors must spread the money available to invest in inventory over a greater number of products.
Customers are less tolerant if product availability does not meet their expectations.

You're obviously in trouble if you don't have the inventory your customers expect you to have. And if you've bought too much of an item, your money is tied up and can't be invested in the other products that allow you to take advantage of new sales opportunities.

These challenges require the best possible product forecasting. You can no longer accept as "inevitable" great deviations between forecasts and actual sales. Formulas developed just to be "easy to understand" and "better than a guy with a clipboard" have to be replaced with more comprehensive methods.

Products with different patterns of usage, and different replenishment methods require different forecasting formulas. We need more than one formula for non-seasonal products, and one formula for seasonal products. For example, a product whose sales mirror local economic conditions requires a different formula than a product with steady, fairly predictable sales. And just as important, each formula needs to be easy to understand.

During the next several months, we'll look at some of the 29 different forecast demand formulas developed by EIM. We're going to start with a formula for non-seasonal products with fairly consistent usage. These are items that sell regularly and whose volume has increased or decreased less than 20% per month during the last several months.

When forecasting the usage of non-seasonal products with fairly consistent usage, we want to average the usage that was recorded during the past several inventory periods. But we also want to "weight," or place more emphasis on, the most recent month. Why?

There are often trends in a product's usage as it becomes more or less popular over time. For non-seasonal products, demand in the upcoming inventory period will more likely be similar to the usage recorded in the past several inventory periods than what happened six, eight, or twelve months ago.
At the same time, there is usually a certain amount of random variation in a product's usage from one inventory period to another. Notice how the usage of the item in the first example below has fluctuated over the past five months. This "up-and-down" pattern of usage is common for inventory items with moderate-to-high sales. If we were to use just the most recently completed one or two inventory periods in our calculations, the random fluctuations in usage would probably have too great an influence on the forecasted demand. We want to include enough history to ensure that random fluctuations do not have a significant impact on a product's forecast.

Here is a common set of weights to use in calculating demand for a non-seasonal item with moderate-to-high sales:

Place a weight of 3.0 on the usage recorded in the most recent period.
Place a weight of 2.5 on the usage recorded in the next previous period.
Place a weight of 2.0 on the usage recorded in the next previous period.
Place a weight of 1.5 on the usage recorded in the next previous period.
Place a weight of 1.0 on the usage recorded in the next previous period.

Let's see how the forecast for an item is calculated with the following usage history. Usage is the quantity of a product sold, transferred, used in assemblies or repair orders, or otherwise taken from stock.

*Month*	*Total Usage*	Number of Business Days in Month	Usage per Business Day
*June*	148	20	7.4
*May*	133	19	7.0
*April*	126	18	7.0
*March*	110	22	5.0
*February*	104	20	5.2

Note that we've specified the number of business days in each month, and determined the usage per business day. Utilizing usage per business day provides more accurate forecasting than traditional forecasting methods that rely on total monthly usage or usage per calendar day. After all, if a company is closed for several days during a month (remember the Christmas holidays?), considering that month's lower usage equally with the usage recorded in other months tends to underestimate future forecasted demand. For example, in the chart displayed above, total usage recorded in May (133 pieces) is about 5.5% higher than total recorded in April (126 pieces), but the demand per business day is the same.

We will apply the weights of the demand calculating formula to the usage per business day for the five preceding months to determine the forecast demand for July:

*Month*	*Weight*	Usage per Business Day	*Extension*
*June*	3.0	7.4	22.2
*May*	2.5	7.0	17.5
*April*	2.0	7.0	14.0
*March*	1.5	5.0	7.5
*February*	1.0	5.2	5.2
*Total*	10.0		66.4

The extension (66.4) is divided by the total weight (10.0) to determine our prediction of the demand per business day for July (6.64 pieces per day). And this demand per day is multiplied by the number of business days in July (21) to predict the demand of 139.4 pieces for the inventory period.

Compare the results of this calculation to the demand predictions provided by other forecast formulas and methods. We think you'll be impressed with the results. Next month we'll look at non-seasonal products with significant increasing or decreasing usage. In the meantime, if you have any specific questions, please let us know.

Some items, like beach umbrellas, are more popular in summer than in winter. On the other hand, portable heaters enjoy much higher sales when the weather gets cold. These are seasonal items. But the weather is not the only factor that determines whether or not an item is seasonal. If a product's usage is controlled by an event (such as Christmas or the start of school) or an annual activity (like yard clean up in the fall), the item is also considered to be seasonal. The usage of a seasonal product rises and falls throughout the year. Look at this seasonal item's usage history:

	Jun 1999	May 1999	Apr 1999	Mar 1999	Feb 1999	Jan 1999
Usage 1999	?	300	150	80	50	30

	Dec 1998	Nov 1998	Oct 1998	Sep 1998	Aug 1998	Jul 1998	Jun 1998
Usage 1998	50	100	150	300	520	460	400

Usage of the product is very low during the winter months. But in early spring, sales begin a gradual increase and peak during the summer months of June, July and August. If we forecast demand for June 1999 by using the formula for non-seasonal products with consistent usage (described in Part One), we get the following result:

*Month*	*Total Usage*	Number of Business Days in Month	Usage per Business Day
*May*	300	19	15.8
*April*	150	18	8.3
*March*	80	22	3.6
*February*	50	20	2.5
*January*	30	22	1.4

*Month*	*Weight*	Usage per Business Day	*Extension*
*May*	3.0	15.8	47.4
*April*	2.5	8.3	20.8
*March*	2.0	3.6	7.2
*February*	1.5	2.5	3.8
*January*	1.0	1.4	1.4
*Total*	10.0		80.6

The extension (80.6) is divided by the total weight (10.0) to determine our prediction of the demand per business day for June of 8.06 pieces. Because June has 20 business days, demand for the inventory period is 161.2 pieces (20 days x 8.06 pieces per day).

Remember that demand is defined as a prediction of the usage of a product during the upcoming inventory period. Is 161 pieces a good forecast of June's usage? Probably not. After all, usage in June 1998 was nearly three times this amount (460 pieces). It is obvious that we need different formulas for calculating the demand for seasonal items.

We've found that one of the best indicators of what demand will be for a seasonal item next month is the usage recorded during the upcoming several months, last year. For example, one formula for forecasting demand for seasonal items considers the usage for the upcoming month and the following month last year, applying the following weights:

Place weight of 2.0 on the usage recorded in the month being forecast, last year.
Place weight of 1.0 on the usage recorded in the month following the month being forecast, last year.

*Month*	*Total Usage*	Number of Business Days in Month	Usage per Business Day
June 1998	400	19	21.1
July 1998	460	18	25.6

*Month*	*Weight*	Usage per Business Day	*Extension*
June 1998	2.0	21.1	42.2
July 1998	1.0	25.6	25.6
*Total*	3.0		67.8

The extension (67.8) is divided by the total weight (3.0) to determine our prediction of the demand per business day for June of 22.6 pieces. Because June, 1999 has 20 business days, demand for the inventory period is 453 pieces (20 days x 22.6 pieces per day).

But there is a problem with forecasting demand with history that is a year old. Business in the branch where the item is located, or in its particular line of products, may have increased or decreased during the past 12 months. For this reason, a "trend factor" can be applied to the results of the weighted average formula to reflect overall changes in your volume of business.

Many systems will allow you to manually maintain trend factors. Say, for example, you determine that the sales volume in our item's product line increased 20% over the past year. To determine the actual demand forecast for the product, we'd increase the result of the seasonal weighted average formula by 20% to determine the actual demand forecast for June, 1999:

22.6 pieces/day + 20% = 27.1 pieces/day

More advanced systems calculate a suggested trend factor by comparing the total usage in the last three completed months (before the forecast demand calculation) to the total usage in the same three months in the previous year:

Total Usage March,1999 - May, 1999 = 530 pieces

Total Usage March 1998 - May, 1998 = 462 pieces

(530 - 462) ÷ 462 = 14.7%

Business in the past three months was 14.7% greater than the same period last year. This percentage is added to the results of the weighted average formula:

22.6 pieces/day + 14.7% = 25.9 pieces/day

Whether specified manually or calculated automatically by the system, trend factors must be applied whenever seasonal forecast formulas are utilized to compensate for the change in business experienced over the past 12 months.

http://www.effectiveinventory.com/article26.html

8/6/09

VAR versus Partner for Dynamics GP

In the software community the terms VAR (Value-Added Reseller) and Partner are often used interchangeably. I.E. "Who's your VAR". "My Partner is...."

When I describe both I don't think the terms should be used interchangeably. Here's a few distinctions between the two.

VAR

Sells software. (no matter what is best for the client.) This is how a VAR is compensated thus the need to sell whenever possible.

Adds value to the product by adding features or service. Services provided by a VAR often includes integrating, customizing, consulting, training and implementation. There's been a lot of blogs posted recently on why or why not you should customize. In the VAR mindset, the more done from these activities the greater the income from that client.

Partner (Partnership)

Trusted advisor. Partners are more then resellers, implementers, etc. Partners advise clients on what the software will do and what it won't do. When a Partner recommends a third party software or customization it is because of experience and knowledge that the addition will be in the best interest of the client.

Business Consultant. Not only used to implement software, used as a business/management consultant. I really believe the Dynamics software is only a small part of a successful implementation. We've all seen installations where you wonder "what in the world was that VAR/implementer thinking" and the software is a hindrance rather than a help. A partner will consult with the client and sometimes hold firm when a client is trying to do something with the software that will cause pain and heartache down the road. (I.E. Fixing something in the back end instead of through the user interface. I was asked to fix 100 transactions from SOP that had something wrong with them. I said no as the tables involved included GL, SOP, RM, CM (bank rec). I supposed I could have done it with enough time and effort but recommended simply fixing through the front end even with the large amount of transactions effected.)

Colleague - It sounds kind of hokey, I know, but clients are way more than just business transactions. I wouldn't go so far as to say that partners/clients should be friends (certainly some are) but I would say in the Partner/client relationship the word colleague (Stolen from LinkedIn) certainly fits the bill. A partner would not do anything to take advantage of a colleague and certainly always has their best interest in mind.

Every once in a while we have a customer leave our "flock." It always makes me stop and think how the relationship we had with the former customer went from being a Partner to just another VAR. Consider doing the following to protect the relationship between Partners and customers:

Consistent contact. Doesn't have to be daily or weekly in all cases. Certainly not if a project in not underway. I'd suggest at frequent contact by newsletter, email update on both company happenings and Dynamics news, phone call, Blog posting, customer lunch and learn, LinkedIn etc. Anything that can keep communication open between partners. We try to contact customers (depending on situation) by phone once a quarter and in person semi-annually.

Continual improvement - This includes improving the client/partner relationship, partner knowledge, implementation process, follow-up. If you are not getting better you are getting worse. There is no staying the same. So if you think your relationship is about the same as last year with your partner/client, I'd suggest looking at ways to improve as most likely things are getting worse and it's just not apparent.

Partner support desk - Personnel that are reachable and responsive. I think it's critical to have a support desk for partners to get a sense of their clients needs. I may be biased as I work in the support area of our company but whenever anyone needs to know about a certain client the first place consultants, sales people, and management turn to is the support people. Support is in constant contact clients. The support team's whole purpose is to help the client thus making a bond between client and Partner stronger.

Anything else you would consider important to building, maintaining or improving the Partner relationship?

Articles to consider:

http://community.dynamics.com/blogs/satha/archive/2007/09/15/the-var-value-added-reseller-partner-the-value-they-add-to-your-business.aspx

http://www.microsoft.com/midsizebusiness/business-goals/build-business-relationships/value-added-reseller.mspx

http://www.rosebizinc.com/gpblog/2009_03_01_archive.html

8/4/09

Action, Approval, Information - Which is it?

"Bill, I'm not sure what you're asking the committee to do with the material you sent out for the meeting." The man on the phone was the committee Chair and he wanted to be sure he understood what was being asked of him before going into the meeting.

That was my mistake. As the resource person supporting the work of the committee, my job is to organize the material so that its purpose is crystal clear. For example, there are only three things you can ask of a Board or Committee:

Action - You're asking them to do something
Approval - You need their approval in order to move something forward
Information - You are reporting back to them

If the Chair is clear on what is being asked of the Committee, (s)he can set the right tone for the discussion. If the supporting written material is already organized so that it flows towards a clear objective, less time will be wasted. With clarity of purpose and well organized supporting material, the work of the Committee can be done quickly and effectively.

It's when there is no clear purpose that Committees often flounder and meetings become protracted. Members end up trying to second guess the intent of the material before them or worse, re-write it completely. When that happens, when you look around the room and see a lot of tired faces, the best thing is to refer the work back to where it came from with instructions to re-draft it.

By this I do not mean to imply that Committees should be manipulated into one specific direction. If the intent of the work before them is clear, it is also easier to reject it or send it back for more work. My personal experience with Committees is that they respect a well reasoned argument and will put a lot of energy into reviewing it if the information is brief and well presented. They will usually come up with comments that I had not considered, so that the end result is better than it would have been had I been working alone.

Which brings me to the topic of brevity. Churchill famously insisted that all briefs be reduced to a single page, but your Board / Committee probably wants a little more background than that. Readable writing is more important than just the length, although if a brief is perceived as being too long you run the risk of your Committee not reading it at all. If writing is not one of your strengths, by all means delegate! There are lots of freelance editors who will help you structure your thoughts. Here are some tips:

Use bullet points - they break up the monotony of the text and make it easy to find the important parts of your argument,
Use sub-headings - they organize your work -- try reading just the sub-headings, they should give you a feel for the whole document
Keep your sentences short - avoid compound, complex sentences
Avoid the passive voice - keep your prose active

What has your Board experience been like, whether from the staff viewpoint or the Board member's? Please join the discussion and leave a comment.

http://energizedaccounting.blogspot.com/2009/06/action-approval-information-which-is-it.html

8/3/09

Buttons location and its functions

Question

Where are the NoiseAssassin™ and Talk buttons on the New Jawbone and Jawbone Prime?

Answer

The New Jawbone and Prime was designed to have a seamless, sleek look and its buttons can be accessed by pressing the faceplate at the appropriate locations. The buttons will have a distinct clicking feeling when pressed. The diagram below describes where to press on the faceplate to push each button.

Here's a list of functions for each of the buttons:

Talk Button:

Answer or End a call - press once.
Turn the power On/Off - press and hold for 3 seconds.
Disable LED - press five times while headset is on standby.

NoiseAssassin™ Button:

Volume adjust - press once during an active call to cycle through the multiple volume levels.
Last number redial - press three times while the headset is on standby.
Voice dial - press and hold for 3 seconds while the headset is on standby.
Reject a call - press once while phone is ringing.
Disable NoiseAssassin: press and hold for 3 seconds while on an active call. Note: NoiseAssassin is always on be default.

Combination:

Pairing Mode - Press both NoiseAssassin and Talk button for 3 seconds, while the headset is off, to get the headset in pairing mode. The LED should flash red and white alternately.

How to put the Jawbone Prime in pairing mode?

Question

How do I put the Jawbone Prime in pairing mode?

Answer

The first time that you use the Jawbone with your Bluetooth-enabled phone, you will have to “pair” them together. The headset must be in pairing mode (discoverable) in order for the phone to find it.

To put the Jawbone Prime headset in pairing mode follow these steps:

1) Ensure that the Jawbone is fully charged.
2) Start with the Jawbone powered OFF (no flashing LED).
3) Hold down both the NoiseAssassin and Talk button for 3-4 seconds or until the LED light flashes red and white alternately.

It will continue to flash red and white until pairing is completed.

Once the headset is in pairing mode, have your phone search for Bluetooth devices. Select Jawbone from the list of devices shown. When prompted for a passkey, enter 0000.

Monitor Windows User Log In and Log Off without software

This is a great little batch file that enables an admin to keep a log of where, when and what computer user log on to and when they log off.

1. Set up a Share on the network.

The batch file is actually two files. One runs at user log on and one runs at user log off. When the batch files run, they create a rolling log file with the details in a shared network folder.

1) Create a shared folder on the network. Mine is called Logs. Everyone should have full access to this share.
2) In that folder, create a folder called User and a folder called Computer.

2. Create the batch files.

Copy and Paste the following into two separate text files. When you save them, remember to change the file type to .bat

Name: Log On.bat

rem The following line creates a rolling log file of usage by workstation
echo Log In %Date% %TIME% %USERNAME% >> \\servername\Logs\Computer\%COMPUTERNAME%.log

rem The following line creates a rolling log file of usage by user
echo Log In %Date% %TIME% %COMPUTERNAME% >> \\servername\Logs\User\%USERNAME%.log

Name: Log Off.bat

rem The following line creates a rolling log file of usage by workstation
echo Log Off %Date% %TIME% %USERNAME% >> \\servername\Logs\Computer\%COMPUTERNAME%.log

rem The following line creates a rolling log file of usage by user
echo Log Off %Date% %TIME% %COMPUTERNAME% >> \\servername\Logs\User\%USERNAME%.log

Put these files in the Logs folder.

3. Add the batch files to group policy.

You do this on your User Container GPO.

User Configuration > Windows Settings > Scripts

Add Log In.bat to the Logon scripts box and Log Off.bat to the Logoff scripts box