FORECASTING SALES

Document Sample

```					                                    Chapter 12

FORECASTING SALES AND COST OF SALES

In This Chapter
• EasyRefresher™: Sales and Cost of Sales Forecasting
• Using the Sales Forecasting Starter Workbook
• Understanding the Starter Workbook’s Calculations
• Customizing the Starter Workbook

B    usiness planning usually requires detailed forecasts of sales and related variables, such
as cost of sales, gross margins, and the inventory levels required to support the fore-
casted sales. The sales forecasting starter workbook (SALESRPT.XLS) described in this
chapter provides a framework for you to use in forecasting sales, cost of sales, gross mar-
gins, and inventory levels in a variety of businesses, including manufacturing, wholesaling
and retailing, and service firms. This chapter shows how to use the sales forecasting starter
workbook , print it, modify it, and link it with subsidiary spreadsheets.

EasyRefresher™:
Sales and Cost of Sales Forecasting
Sales forecasting is basic to any business plan or budget and to some investment analysis.
Essentially, you need to collect information for or make forecasts concerning as many as five
related variables.
For example, you typically need to collect the units and dollars of inventory that you al-
ready hold or that you estimate you will hold at the beginning of the sales forecast. In a

343
manufacturing firm, these amounts are the sum of the work in process, or partially manu-
factured, inventory and the finished goods, or ready to sell, inventory. In a wholesaling or
retailing firm, these amounts are the sum of those items purchased for resale. You need to
collect both the units of inventory and the dollars of inventory. (In a service business, no
inventory is manufactured or purchased, so these amounts are 0.)
Second, for each period in the forecasting horizon, you need to forecast the number of units
produced if you’re a manufacturer or the number of units purchased if you’re a wholesaler
or retailer. Typically, the amounts cannot be forecasted independent of sales, but sales isn’t
the only variable that affects production or purchases. Other important variables such as
manufacturing capacities and availability of inventory items to be purchased also impact
planned production and purchases.
Third, you need to forecast the costs of producing or purchasing the inventory for each period
over the forecasting horizon. For a manufacturing firm, costs are often forecasted by clas-
sifications such as direct labor (the wages and employee benefits incurred in making the
item), direct material (the raw materials and components that go into the finished prod-
uct), and factory overhead (the miscellaneous and incidental costs associated with making
the item, such as electricity to run the manufacturing equipment). For wholesalers and re-
tailers, forecasted costs are the amounts paid to suppliers for those items purchased for resale.
In a service business, no inventory is manufactured or purchased, so no production or pur-
chase costs exist.
Fourth, you need to forecast the number of units sold and the price per unit sold for each
period over the forecasting horizon. For manufacturers, wholesalers, and retailers, the unit
forecast simply is the number of cars, shirts, or basketballs sold; the unit price forecast is
simply the price at which these items are sold to the customer. For service firms, the unit
forecast simply is the number of times a service is provided or the hours of work performed;
the unit price forecast is simply the price at which the service or work is billed to the
customer.
Fifth, you need to forecast any other variable costs associated with sales. For any type of
business, these other costs might include sales commissions incurred as a result of the sale,
taxes on the sale, and any other costs incurred and directly tied to the sale.
With this information, you should be able to forecast total sales, cost of sales, and margins.
Usually when you forecast sales and cost of sales, you use either the financial accounting or
managerial accounting format. Using the financial accounting format, you calculate sales
revenue, cost of goods sold, and gross sales margin.
Using the managerial accounting format, you calculate the marginal sales revenue, the vari-
able costs, and the marginal contribution. The marginal sales revenue is the number of units

344   MBA’s Guide to Microsoft Excel 2000
MBA’           Microsoft
sold times the unit price at which the sales are made. The variable costs include both the cost
of goods sold, which is the sum of the production or purchasing costs of the items sold, and
also any other variable costs incurred as a result of the sale. The marginal contribution is the
marginal sales revenue minus the variable costs. The marginal contribution is that amount
generated by your sales to pay your fixed costs, those costs that do not vary with sales volumes.
You might want to use a combination of both formats in your forecasting, so the sales and cost
of sales starter workbook amounts to a hybrid of the two formats. You can use sales revenue
for either the financial accounting sales revenue or the managerial accounting marginal sales
revenue. You can use the cost of sales for either the financial accounting cost of goods sold or
the managerial accounting variable costs. Depending on how you use the workbook’s sales
revenue and cost of sales, you can use the gross margin for either the financial accounting gross
sales margin or the managerial accounting marginal contribution. Total sales simply are the
number of units sold times the unit price at which the sales are made. The cost of the goods
sold is the sum of the production or purchasing costs of the items sold. The gross sales mar-
gin is the total sales less the cost of sales. The gross sales margin is that amount actually gen-
erated by your sales to pay for your operating and financing costs. Any amounts left over after
paying these costs represent your profit.

Using the Sales Forecasting Starter Workbook
Workbook
You can use the sales forecasting starter workbook(SALESRTP.XLS), as shown in Figures
12-1 and 12-2, to construct sales forecast schedules for each product or service for which
you want to estimate sales and production activity separately. This starter workbook pro-
vides a framework for the development of your own sales forecasts. To complete it for a
product or service line, you develop and then enter your sales forecasts, your manufactur-
ing or purchasing forecasts, and your beginning inventory levels for work in process and
finished goods.
Given the beginning inventory (expressed both in units and in dollars), the number of units
produced or purchased and their costs by period, and the sales volumes and unit sales prices
by period, this workbook details and calculates the total sales, production activity, and in-
ventory balances by period on the forecasting horizon. You need this information to calcu-
late product sales and gross margins, business profits and losses, and business cash flows,
and you need it to report the inventory balance on the balance sheet.
To enter your own data in sales forecasting starter workbook, follow these steps:
1. Open the sales forecasting starter workbook, SALESRPT.XLS, from the companion CD.

Chapter 12 Forecasting Sales and Cost of Sales          345
The workbook initially contains the default inputs shown in Figure 12-1.

Figure 12-1    The inputs area of the sales forecasting starter workbook.

2. Enter the beginning inventory balance in dollars and in units on hand for the first period.
The values you enter for Units on Hand and Balance in Dollars under Beginning Inven-
tory come from your accounting records; they document your starting inventory balances.
Notice that subsequent periods’ beginning inventory figures are calculated, not entered,
using the forecasts of sales and manufacturing or purchasing activity.
3. Enter the units produced or purchased for each period over the forecasting horizon.
The period production figures stem from your forecasts of the anticipated manufactur-
ing or the anticipated purchasing volumes necessary to support the sales plan.
TIP     For manufacturing firms, the number of units in the starting inventory balance and
the number of units produced should be expressed in equivalent units. For example,
100 units that are 50% complete are included in the schedule instead as 50 units that
are 100% complete. This approach is necessary because if you don’t use equivalent
units, only a percentage of the costs are included and the calculated unit cost will be
too low.

4. Enter the production costs (direct labor, direct material, and factory overhead) associ-
ated with manufacturing or purchasing volumes forecasted for each period over the
forecasting horizon.
The production costs—Direct Labor, Direct Material, and Factory Overhead—are those
costs associated with manufacturing or purchasing the product. If you are in a wholesale
or retail business that has no manufacturing activity, enter only the Direct Material value
(which should be called purchases).

346   MBA’s Guide to Microsoft Excel 2000
MBA’           Microsoft
5. Enter the units sold and the unit sales price forecasted for each period over the forecast-
ing horizon.
Forecast the units sold and the unit sales price based on your sales and marketing research.
In general, you estimate future sales based on your past sales history and expectations about
future orders.
6. Enter any other variable costs associated with consummating a sale for each period over
the forecasting horizon.
Other variable costs associated with a sale might include commissions or bonuses owed
to the salespeople who close the sale, bad debt expense that might be expressed as a func-
tion of the sale, and marketing costs related to packaging and distributing the product.
You’ll often enter this item as a formula that is calculated from unit sales, unit sales price,
or the production/purchase costs.

Understanding the Starter
orkbook’s
Workbook’s Calculations
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 Mar-
gin 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 inte-
ger 1. Subsequent period numbers are calculated as 1 plus the previous period.

Totals
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.

Chapter 12 Forecasting Sales and Cost of Sales          347
Figure 12-2   The schedule calculated by the sales forecasting starter workbook.

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 sched-
ule. For example, the formula for the first period is:
=B46/B40

348   MBA’s Guide to Microsoft Excel 2000
MBA’           Microsoft
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 bal-
ances 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
Average
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.

Chapter 12 Forecasting Sales and Cost of Sales        349
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 for-
mula 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 on.

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 on.

Variable
Other Variable Costs
The Other Variable Costs figure shows the other direct costs associated with consummat-
ing 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

350   MBA’s Guide to Microsoft Excel 2000
MBA’           Microsoft
The formula for the second period is:
=C32+C33

and so on.

Gross Sales Margin
The Gross Sales Margin figure shows the amount remaining from sale proceeds after de-
ducting the cost of sales. The Gross Sales Margin figure represents the funds that go to-
ward 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 on.

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 sched-
ule 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 Be-
ginning 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.

Chapter 12 Forecasting Sales and Cost of Sales        351
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 num-
ber 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.

352   MBA’s Guide to Microsoft Excel 2000
MBA’           Microsoft
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.

Workbook
Customizing the Starter Workbook
You can use the sales forecasting starter workbook without modification for many sales
forecasts. However, you might want to change the workbook so that it more closely matches
your requirements. For example, you can add text that describes the product being manu-
factured or purchased and for which sales are forecasted. You can also increase or decrease
the number of periods. For example, you can increase the number of periods to 12 if your
periods are months and you want to forecast an entire year.

NOTE    Before you change anything in the starter workbook other than the Sales Forecast
Inputs schedule, unprotect the document.

To increase the number of periods, remove the borders from the last column; then copy the
current last column to the right as needed. To decrease the number of periods, simply de-
lete any unneeded column from the right side of the schedule. When you finish these steps,
you can replace the borders on the right and reinstate cell protection as needed.

Chapter 12 Forecasting Sales and Cost of Sales        353

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 27 posted: 3/19/2012 language: English pages: 12
Description: FORECASTING SALES AND COST SALES GIVES GREAT IDEA WHAT TO DO AND WHAT NOT TO DO.