cost of goods sold formula

Document Sample
cost of goods sold formula Powered By Docstoc
					                                    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:
Stats:
views:23262
posted:1/16/2009
language:English
pages:12