• Create BookmarkCreate Bookmark
• Create Note or TagCreate Note or Tag
• PrintPrint

### Important Tools for Any Business

This chapter is specifically designed to break out certain tools, strategies, and functions as you apply to everyday business. Although daily business might differ from individual to individual (and business to business), you’ll find problems and situations common to almost everyone. From creating tables and formulas, to presenting information graphically, Excel can handle just about any business problem.

#### 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. PivotTables 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 and then take the average over the period and that achieves velocity. A conditional SUM formula is used in the following examples. The key is setup. Notice in Figure 26.1 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 D15 through D39, 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+Shift+Enter. The formula’s syntax is as follows. This formula will achieve the same results without using the IF formula:

PREVIEW

Not a subscriber?

Start A Free Trial

• Create BookmarkCreate Bookmark
• Create Note or TagCreate Note or Tag
• PrintPrint