The sales forecasting starter workbook has five parts: the Sales Forecast heading box, the Sales Forecast Inputs (discussed above), Cost Totals and Statistics, Sales and Gross Margin Forecast, and Inventory Forecast.

## Sales Forecast Schedule

The Sales Forecast Schedule heading box provides column headings for the schedules in the starter workbook. It uses a single formula to calculate the period number. The period identifier simply numbers the time periods for which sales, manufacturing or purchasing, and inventory levels are forecasted. You’ll probably want the number of periods in your Sales Forecast Inputs schedule to correspond to the number of periods in the other schedules that make up your financial forecasting model. The first period is stored in cell B4 as the integer 1. Subsequent period numbers are calculated as 1 plus the previous period.

## Cost Totals and Statistics

The Cost Totals and Statistics schedule calculates the total production and purchase costs, as well as the beginning, produced or purchased, and weighted average unit costs (see Figure 12-2). It has four rows that contain calculated data.

### Total Production/Purchase Costs

The Total Production/Purchase Costs value is simply the sum of the Direct Labor, Direct Material, and Factory Overhead values. For a wholesaler or retailer, because the Direct Labor and Factory Overhead figures, by definition, are 0, the total cost is the same as the Direct Material cost. For a service firm, no inventory might be manufactured or purchased for resale. Therefore, this amount might be 0.

The formula for the first period is:

*=B12+B13+B14*

The formula for the second period is:

*=C12+C13+C14*

and so on.

### Beginning Inventory Unit Cost

The Beginning Inventory Unit Cost value represents the cost of producing one of the units held in the beginning inventory. It is calculated by dividing the Beginning Dollars on Hand value by the Beginning Units on Hand value calculated in the Inventory Forecast schedule. For example, the formula for the first period is:

*=B46/B40*

The formula for the second period is:

*=C46/C40*

and so on.

### Produced/Purchased Unit Cost

The Produced/Purchased Unit Cost value represents the cost of producing or purchasing one of the units manufactured or bought during the period. Although the inventory balances reported on this schedule use an average cost inventory assumption, you can use this value to construct alternative inventory costing methods, such as First-In-First-Out (FIFO) and Last-In-First-Out (LIFO). (FIFO assumes that the first items purchased or produced are the first items sold; LIFO assumes that the last items purchased or produced are the first items sold. In a period of rising prices, FIFO calculates lower cost of goods sold and higher ending inventory.)

The formula for the first period is:

*=B21/B9*

The formula for the second period is:

*=C21/C9*

and so on.

### Weighted Average Unit Cost

The Weighted Average Unit Cost value represents the average cost of the product units, considering both the beginning inventory balance and the period production or purchase inventory. This is the per unit cost used to calculate both the cost of sales and next period’s beginning inventory levels.

The formula divides the total of the inventory Beginning Dollars on Hand and the Total Production/Purchase Costs values by the total of the inventory Beginning Units on Hand and the Units Produced/Purchased values. For example, the formula for the first period is:

*=(B46+B21)/(B40+B9)*

The formula for the second period is:

*=(C46+C21)/(C40+C9)*

and so on.

## Sales and Gross Margin Forecast

The Sales and Gross Margin Forecast schedule calculates the total sales, cost of goods sold, other variable costs, total cost of sales, and gross sales margin. It has five rows of data.

### Total Sales

The Total Sales figure represents the total sales made over the period. The Total Sales formula multiplies the Unit Sales value by the Unit Sales price value. For example, the first period total sales formula is:

*=B16*B17*

The second period total sales formula is:

*=C16*C17*

and so

### Cost of Goods Sold

The Cost of Goods Sold figure shows the total cost of manufacturing or purchasing the items sold during the period. The formula multiplies the Unit Sales value for the period by the Weighted Average Unit Cost value for the period. For example, the formula for the first period is:

*=B16*B26*

The formula for the second period is:

*=C16*C26*

and so

### Other Variable Costs

The Other Variable Costs figure shows the other direct costs associated with consummating a sale. The value is simply pulled from the cell in which you entered this figure in the Sales Forecast Inputs schedule.

### Total Cost of Sales

The Total Cost of Sales figure shows the total cost of goods sold and other costs related to the sales. The value is the sum of the Cost of Goods Sold and Other Variable Costs values. For example, the formula for the first period is:

*=B32+B33*

The formula for the second period is:

*=C32+C33*

and so

### Gross Sales Margin

The Gross Sales Margin figure shows the amount remaining from sale proceeds after deducting the cost of sales. The Gross Sales Margin figure represents the funds that go toward paying your fixed costs and profits. The formula is the Total Sales value less the Total Cost of Sales value. For example, the formula for the first period is:

*=B29-B34*

The formula for the second period is:

*=C29-C34*

and so

## Inventory Forecast

The Inventory Forecast schedule calculates the beginning inventory balance, the change in inventory balance, and the ending inventory balance, each in dollars and in units. The schedule has eight rows that contain calculated data.

### Beginning Units on Hand

The Beginning Units on Hand figure shows the number of complete products you have available for resale at the beginning of the period. For the first forecasting period, the Beginning Units on Hand figure is simply pulled from the Sales Forecast Inputs schedule. In subsequent periods, it is taken from the cell containing the previous period’s Ending Units on Hand.

### Plus: Units Produced/Purchased

The Units Produced/Purchased figure shows the number of equivalent units manufactured or the number of units bought during the period. The number is pulled from the Sales Forecast Inputs schedule.

### Less: Units Sold

The Units Sold figure shows the number of units in inventory sold during the period. The number is pulled from the Unit Sales forecast in the Sales Forecast Inputs schedule.

### Ending Units on Hand

The Ending Units on Hand figure shows the number of units of inventory held at the end of the period. This number is always the same as the number of units held at the beginning of the next period.

The number is the Beginning Units on Hand value plus the Units Produced/Purchased value for the period minus the Units Sold value for the period. For example, the formula for the first period is:

*=B40+B41-B42*

The formula for the second period is:

*=C40+C41-C42*

and so on.

### Beginning Dollars on Hand

The Beginning Dollars on Hand figure shows the dollar cost of the completed and partially completed products that you have available in inventory for resale. The number for the first period is pulled from the Sales Forecast Inputs schedule. In subsequent periods, the number is taken from the previous period’s Ending Dollars on Hand.

### Plus: Dollars Produced/Purchased

The Dollars Produced/Purchased figure shows the dollar cost of the units manufactured or bought during the period, using the weighted average unit cost as the cost per unit. The number is pulled from Total Production/Purchase Costs in the Cost Totals and Statistics schedule.

### Less: Dollars Sold

The Dollars Sold figure shows the dollar cost of the units sold during the period, using the weighted average unit cost as the cost per unit. This amount is pulled from Cost of Goods Sold in the Sales and Gross Margin Forecast.

### Ending Dollars on Hand

The Ending Dollars on Hand figure shows the dollar cost of the inventory held at the end of the period. This number is always the same as the dollar cost of the inventory held at the beginning of the next period.

If you are in a manufacturing business, you can use this amount as the dollar cost of the work in process and finished inventory that is included in the Balance Sheet. If you are in a wholesale or retail business, you use this amount as the dollar cost of all the inventory that is included in the balance sheet.

The Ending Dollars on Hand formula adds the Beginning Dollars on Hand and Dollars Produced/Purchased values and then subtracts the Dollars Sold value. For example, the formula for the first period is:

*=B46+B47-B48*

The formula for the second period is:

*=C46+C47-C48*

and so on.

## Leave a Reply