Formula 8

Operators/Functions Used

If-Then-Else, Less than (<), Negate (-), Add (+), Round, Average([array]), Parentheses ().

Formula Purpose

To better control her purchasing and inventory, a store owner wants the inventory report to include a QtyToOrder column. She wants that column to tell her what quantity she needs to order to keep on hand the average quantity sold monthly during the previous three months.

If there is stock on hand, she will want to order the average quantity sold less the stock on hand.
If there is no stock on hand but no backorders, she will want to order the average quantity sold.
If there is no stock on hand and backorders, she will want to order enough to clear the backorders plus the average quantity sold.

Formula

If {file.ONHAND} < 0 Then
((- ({file.ONHAND})) + Round
((Average([{file.MONTH1}{file.MONTH2},
{file.MONTH3}]))))
Else
(Round((Average([{file.MONTH1}{file.MONTH2},
{file.MONTH3}])) - {file.ONHAND}))

Result

OnHand Month1 Month2 Month3 Avg. Order

2

16

6

30

17

15

5-

7

3

19

10

15

0

9

8

18

12

12

Explanation

The formula uses the If-Then-Else operator to set up one set of calculations if there are backorders ({file.ONHAND} < 0), and a different set of calculations if there are no backorders.
The If part of the formula uses the Less than operator (<) to see if the quantity on hand is less than zero. This indicates that, not only is all stock gone but there are unfilled orders (a backorder situation).
If there is a backorder, the Then part of the formula calculates the amount to order as the average monthly sales over a specified three month period plus the amount backordered (expressed as a positive number).
It uses the Negate operator (-) to convert the negative value of {file.ONHAND} to a positive value.
It uses the Average([array]) function to average the product sales for {file.MONTH1}, {file.MONTH2}, and {file.MONTH3} (the three months used to determine a typical sales pattern for the product).
It uses the Round function to round the average product sales to the nearest whole number.
It uses the Add operator (+) to add the quantity on hand (now expressed as a positive) to the rounded average monthly sales figure.
The result is the amount to order.
If there is no backorder, the Else part of the formula calculates the amount to order as the average monthly sales over the specified three month period less the quantity already on hand.
It uses the Average function to average the product sales for {file.MONTH1}, {file.MONTH2}, and {file.MONTH3} (the three months used to determine a typical sales pattern for the product).
It uses the Round function to round the average product sales to the nearest whole number.
It uses the Subtract operator (-) to subtract the quantity on hand from the rounded, average monthly sales figure.
The result is the amount to order.