Software product costing sheet

Value Forecast - the value forecast is calculated by multiplying the quantity forecast by the stock purchase prices of the appropriate bought-in stock items in column D. What-if Value Forecast - the what-if value forecast is calculated by multiplying the quantity forecast by the what-if stock purchase prices of the appropriate bought-in stock items in column E.

Note: Purchase prices in column D and what-if prices in column E should not be entered for manufactured products because the product costings of these stock items are determined based on the purchase prices and what-if prices of the components that have been linked to the manufactured products.

If you enter a purchase price or what-if price for a manufactured stock item, the input will have no effect on the costs that are calculated.

Note: We recommend that you review all bought-in stock items on the StockCode sheet in order to ensure that no manufactured products are classified as bought-in stock items. If a manufactured product is incorrectly classified as a bought-in product, it means that no components have been linked to the appropriate product on the BOM sheet. Once you add the appropriate components to the BOM sheet, the stock code will be automatically be classified as a manufactured product.

The purpose of the BOM sheet is to create a link between stock components and manufactured products. This is accomplished by entering the appropriate stock code of the manufactured product in column A and entering the stock code of the appropriate component in column B.

Multiple stock components can be added to a single manufactured product in order to create a product costing which consists of multiple stock components. Note: Columns A and B both contain list boxes that include all the stock codes that have been created on the StockCode sheet. You therefore need to create a stock code for each manufactured or component product before you will be able to select the appropriate stock code from the list boxes in these columns.

You can add a new stock component to the sheet by simply selecting the appropriate product code from the list box in the first empty cell in column A - the table will be extended automatically to include the new product code. All the columns on the BOM sheet with a yellow column heading require user input. The columns with light blue column headings contain formulas that are automatically copied for all new stock components that are added to the Excel table.

Product Stock Code - the stock code of the manufactured product to which the stock component should be added needs to be selected from the list box in this column. The list box includes all the stock codes that have been created on the StockCode sheet. The product stock code should be repeated for all the components that are used in the manufacturing process.

For example, if 10 components are required in order to produce a particular manufactured product, you need to add 10 different component stock codes in column B in 10 separate rows and repeat the product stock code in column A in each of these rows.

All 10 component stock codes will then be linked to the same manufactured product and will be included in the same product costing. Note: Components are listed on the product costing in the same order in which they appear on the BOM sheet. This means that even though all components do not need to be grouped together on the BOM sheet by the product stock code , the order in which they are entered or sorted will determine the order in which they appear in the costing.

We therefore recommend that you always sort the data on the BOM sheet by the product code in column A and the component code in column B after making changes to the BOM sheet. By sorting the data, the components will always be listed on the product costings in a consistent order. Component Stock Code - a component stock code needs to be selected from the list box in column B for each component that is used in manufacturing the product which has been selected in column A.

The product costings that are produced by this template accommodate a maximum number of 30 components per manufactured product. The cost of a manufactured product is calculated based on the costs of all the components that have been linked to the product on the BOM sheet.

Note: A very efficient method of adding components to a manufactured product is by copying the components from a similar product, selecting the appropriate new product code from the list box in column A and editing the input quantities and yields of all the components.

This method will however only be efficient if components have previously been added to a similar manufactured product on the BOM sheet. Input Quantity - the input quantity of the stock component that is used in the manufacturing process should be entered in column C. This quantity should be entered in the same unit of measure that is specified for the particular stock code on the StockCode sheet the component UOM is listed in column I.

If the unit of measure of the manufactured product is "Units", the input quantity of the component should be sufficient in order to produce 1 unit of the manufactured product but if the unit of measure of the manufactured product is for example "Dozen", the input quantity that is entered should be sufficient in order to produce 12 units of the manufactured product. Note: The yield basis should also be taken into account when determining the appropriate component input quantity.

If the yield that is entered in column D is based on an Input basis, the component quantity that is added at the beginning of the manufacturing process should be entered in column C. If however the yield is based on an Output basis, the component quantity that remains at the end of the manufacturing process should be entered in column C.

This is because the input quantity is divided by the yield as part of the component cost calculation. Note: You may also want to consider entering a calculation in the input quantity column because this approach may make it easier to determine how the input quantity has been calculated if a calculation has been necessary. For example: if the component unit of measure is dozen and only one unit is used in the manufactured product, the component quantity is calculated by dividing 1 dozen by twelve.

You therefore have the option of entering 0. Yield - the component yield should be entered in column D as a percentage. The input quantity that is entered in column C is divided by the yield in column D in order to determine the component quantity that is required in order to produce the manufactured product. Yields can be determined on an Input or an Output basis - the difference between the two bases is best explained by a definition and a few examples.

Definition: The inherent nature of a manufacturing process may result in the component quantity at the end of the manufacturing process being less than the component quantity that is introduced at the start of the manufacturing process. The quantity difference can be described as a yield loss. The Input basis refers to the component quantity that is introduced at the start of the manufacturing process, while the Output basis refers to the component quantity which remains after the manufacturing process has been completed.

Note: If there is a significant yield loss during the manufacturing process and the incorrect yield basis is used to determine the yield that is entered in column D, the product costing of the manufactured product may be inaccurate. As we've mentioned before, the yield basis also affects the input quantity that needs to be entered. It is therefore important to take the yield basis into account when determining the component input quantity that needs to be entered in column C.

Example: If we use minced meat in the manufacturing of a beef burger, we have to decide on a yield basis before we can determine the appropriate input quantity that should be specified.

If our aim is to produce a g burger at the end of the manufacturing process, the quantity is based on the output after manufacturing. We may know that on average g of minced meat is required in order to produce a g burger at the end of the manufacturing process - the g is therefore the Input based quantity. Example: Our example beef burger includes one slice of onions.

With this component, we are not really concerned about the output weight because we have already decided that only one slice of onions will be included on our beef burger.

The unit of measure of the onions component is kilogram and we therefore need to calculate the input quantity of this component based on how many slices are included in a kilogram of onions.

The quantity that represents one slice then needs to be entered in the Input Quantity column. We also need to take this yield loss into account in order to compile an accurate beef burger product costing. The BOM sheet also includes 22 columns with light blue column headings. These columns contain formulas that are automatically copied for all the new components that are added to the Excel table on the BOM sheet. The purpose of each of these columns is as follows:.

Product Description , Product UOM - these columns are included on the BOM sheet to enable users to view the description of the product codes that are selected in column A and to ensure that the correct unit of measure is used when entering component input quantities.

Component Description , Component UOM - these columns are included to enable users to view the description of the component codes that are selected in column B and to ensure that the correct unit of measure is used when entering component input quantities.

Component Type - this column reflects the type of component that has been selected in column B. If the component type is "Bought-in", it means that the component is purchased from a supplier.

If the component type is "Manufactured", it means that the component is an intermediate product which needs to be manufactured. Bought-In Price - if the component is a bought-in stock item, the purchase price of the component that is entered on the StockCode sheet will be included in this column. If the component is a manufactured stock item, the bought-in price will be nil and the component cost will be based on the calculations in the intermediate columns.

Bought-In Cost - the bought-in cost of components is calculated by multiplying the bought-in price of the component by the input quantity in column C and dividing the result by the yield in column D. Intermed Comp Cost - if the component that is selected in column B is a manufactured product, the component cost in this column is determined based on the product cost of the intermediate product which is calculated in column O.

Note that the component cost is calculated as the sum of all the product costs of components that are linked to the intermediate product on the BOM sheet.

If the component is a bought-in product, the intermediate component cost of the product will be nil. Intermed Prod Cost - the intermediate product costs of components are calculated by multiplying the intermediate component cost of the component by the input quantity in column C and dividing the result by the yield in column D. Product Cost - if the component is a bought-in product, the component product cost will be equal to the component bought-in cost.

If the component is an intermediate product, the component cost will be equal to the intermediate product cost. Note that the product cost of the manufactured product that has been selected in column A will be equal to the sum of all the component product costs that are calculated for the particular product in this column.

Bought-In WI Price - if the component is a bought-in stock item, the what-if price of the component that is entered on the StockCode sheet will be included in this column. If the component is a manufactured stock item, the bought-in what-if price will be nil and the component what-if cost will be based on the calculations in the intermediate columns. Bought-In WI Cost - the bought-in what-if cost of components is calculated by multiplying the bought-in what-if price of the component by the input quantity in column C and dividing the result by the yield in column D.

Note that the what-if component cost is calculated as the sum of all the what-if costs of components that are linked to the intermediate product on the BOM sheet. If the component is a bought-in product, the what-if intermediate component cost of the product will be nil. What-If Cost - if the component is a bought-in product, the component what-if cost will be equal to the component bought-in what-if cost.

If the component is an intermediate product, the component cost will be equal to the intermediate what-if cost. Note that the what-if cost of the manufactured product that has been selected in column A will equal the sum of all the component what-if costs that are calculated for the particular product in this column. Level1 Forecast - the stock quantities of all bought-in and intermediate products that are required in order to produce the forecasted finished product quantities that have been entered on the Forecast sheet are calculated in this column.

Note that only the components that are directly included in the finished product bills of material will reflect quantities in this column. Level2 Forecast - the stock quantities of all bought-in and intermediate products that are required in order to produce the Level1 Forecast quantities that have been calculated in the previous column will be included in this column.

Note that only the intermediate product quantities that are included in the previous column will have an effect on the calculations in this column because bought-in items contain no components. Level3 Forecast - the stock quantities of all bought-in and intermediate products that are required in order to produce the Level2 Forecast quantities that have been calculated in the previous column will be included in this column.

Level4 Forecast - the stock quantities of all bought-in and intermediate products that are required in order to produce the Level3 Forecast quantities that have been calculated in the previous column will be included in this column. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services. Privacy policy. Setting up the costing sheet involves two objectives. As the first objective, you define the format for displaying cost of goods sold information about a manufactured item or production order.

The formatted display is termed a costing sheet. As the second objective, you define the basis for calculating indirect costs. The costing sheet setup builds on the cost group feature for displaying information and for the indirect cost calculation formulas.

The two objectives of costing sheet setup are described in this article. The following table lists the out-of-box security roles that can access costing sheets, including the level of access granted to each role by the default settings. A costing sheet is the formatted display of information about the cost of goods that are sold for a manufactured item or a production order. When you set up a costing sheet, you define the format for the information and also define the basis for calculating indirect costs.

The costing sheet setup builds on the cost group features for displaying information and for the formulas that are used to calculated indirect cost. Here is more information about the two objectives of costing sheet setup:. This Best Practice includes 1 Excel sheet. Add to bookmarks. We use cookies to understand how you use our site and to improve your experience.

This includes personalized content and Best Practices suggestions. To learn more click here. By continuing to use our site, you accept our use of cookies, revised Privacy Policy and Terms of Use. View all Eloqoons. Why do I need to sign up with LinkedIn? Bookmark download for free.

Send to a friend linkedin twitter facebook code.