Microsoft Office Tutorials and References
In Depth Information
Figure 2.6
Common TEXT string
functions.
There are situations common to many businesses. Take a look at how Excel functions can
Sell In Versus Sell Through
Sell in versus sell through is the important factor when running your business if you sell goods
into a distribution channel. In addition, if your products aren’t revolving off the shelf, the
retailer will ultimately pull the product and find a product that generates revenues for the store
or chain. Pivot tables are great summaries of information for analyzing the marketplace and
measuring product velocity per store, chain, sku, product type, and so on. However, you’ll still
need to view the product in a manner that allows you to see the product and the volume dollars
per period that it is generating. Given this, you’ll have to create a formula that shows velocity,
or the rate at which the product is selling—which is viewed as a percentage. A conditional SUM
formula is used in the following examples. The key is setup. Notice in Figure 2.7 that the
conditional SUM formula in cell D4 is in the form of an array and looks up the date in cell D3 in the
Date column in the In/Through table. The formula then looks up the product in cell C4 in the
product column in the In/Through table. It returns the quantity for that date and product from
the range D12 through D36, which is the sell in column. The same formula is applied to the
sold through row (5), however the return is from column E, which is the sell through column.
The array is activated by pressing Ctrl+Shft+Enter. The formula’s syntax is as follows.
Channel Velocity
Channel velocity is the rate at which your products are selling through a channel, store, or
marketplace. This can be measured in the form of a percentage. The velocity is based on the
amount of time the product will remain in the channel or the life of the product—the total
unit sales expected for the product and the weekly projected flow or sell through for the
product. However simple the formula may be, the structure and having all the components in
place is important. The formula just divides the actual sell through by the weekly average. If
your sales are seasonal and you have fluctuating performance, as most products do, find the
velocity by dividing by that particular week (see Figure 2.8).
Search JabSto ::

Custom Search