Create Overhead Cost Pools Through Oracle s Mass Budgets

Create Overhead Cost Pools Through Oracle’s Mass. Budgets & Auto. Allocations Atlanta - OAUG November 21, 2003 Presenters: Michael Anenen & Paul Kraskiewicz

Presentation Agenda 1. Assess & Design – Overhead Allocation Methods – Standard Cost Overhead Sub-elements 2. Oracle’s Overhead Application Basis Types 3. Cost Pool Allocation Process 4. Using Oracle’s Mass. Budgets & Auto. Allocations 5. Questions & Answers

Assess & Design Start with the End in Mind • Determine your Allocation Method – Traditional – Activity-Base Costing • Overhead Sub-Elements – Material Overhead – Resource / Department Overhead

Assess & Design ü Determine your Allocation Method – Traditional – Activity-Base Costing • Overhead Sub-Elements – Material Overhead – Resource / Department Overhead

Overhead Allocation Methods • Basic Principals – Estimate overhead costs for the coming year – Estimate activity for the coming year – Calculate application rate as Estimated Overhead Costs / Estimated Activity – Apply overhead using actual activity and the application rate. – If actual overhead > applied overhead, the difference is under-applied. – Under-applied overhead reduces income

Overhead Allocation Methods • Traditional – Single Plant-Wide Rate – Single Rate Per Department • Activity-Based Costing – Multiple Activity Based Rates Per Department

Overhead Allocation Methods • Traditional Allocation – All overhead costs are applied using the same rate. – Activity is measured with a single concept, such as: • Direct labor hours • Direct labor dollars • Machine hours • Advantages: – Simple Arithmetic – Easy to understand • Disadvantages: – Little relationship between costs and activities – Can lead to poor business decisions

Overhead Allocation Methods • Activity Based Allocation – Overhead costs are divided into cost pools. – Each cost pool has its own activity measure (cost driver). • Number of purchase orders • Number of setups • Advantages: – Closer causal links between costs and activity – Better cost allocations • Disadvantages: – Complex calculations – High start-up costs

APPLICABILITY OF OVERHEAD ALLOCATION METHODS Method of Assigning Overhead Single Plant Wide Rate Type of Allocations Number of Stages From a single aggregated cost pool, to products Basis of Overhead Rates A production volume related activity measure, e. g. , direct labor hours or dollars, machine hours, etc. When Applicable Plant produces only one, or a few similar products that consume all indirect resources in the same proportions, e. g. , Product X consumes 10% of each indirect resource in the plant.

APPLICABILITY OF OVERHEAD ALLOCATION METHODS Method of Assigning Overhead Single Rate Per Department Type of Allocations Number of Stages Basis of Overhead Rates From service departments, to producing departments' single cost pools (overhead accounts), to products A production volume related activity measure. These may be different for each department When Applicable Each department produces only one, or a few similar products that consume all of a department's indirect resources in the same proportions, e. g. , Product X consumes 20% of all indirect resources in Department A, 10% of all indirect resources in Department B, etc.

APPLICABILITY OF OVERHEAD ALLOCATION METHODS Method of Assigning Overhead Type of Allocations Number of Stages Basis of Overhead Rates Multiple Activity Based Rates Per Department From service departments, to producing departments' activity cost pools, to products Both production volume related and nonproduction volume related activity measures, e. g. , number of purchase orders, number of setups, etc. When Applicable Each department produces many products that consume resources within each department in different proportions, e. g. , Product X consumes 30% of the power, 15% of the engineering work and 20% of the repair in Department A. Multiple rates are needed in Department A.

Assess & Design • Determine your Allocation Method – Traditional – Activity-Base Costing ü Oracle Overhead Sub-Elements – Material Overhead – Overhead

Oracle’s Cost Elements Material Overhead Resource Overhead Outside processing

Oracle Cost Elements Material Overhead & Overhead • Overhead (Resource): – This is the overhead cost of resource and outside processing. Overhead is used as a means to allocate indirect production costs. • Material Overhead: – This is the overhead cost of material, calculated as a percentage of the material cost or as a fixed charge per item, lot, or activity.

Overhead Sub-Elements Overhead Cost Elements Material overhead Resource overhead Material Mgmt Facility Overhead Cost Freight-In Benefits Sub-Elements Production Control MFG Engn’g Indirect

Presentation Agenda 1. Assess & Design – Overhead Allocation Methods – Standard Cost Overhead Sub-elements 2. Oracle’s Overhead Application Basis Types 3. Cost Pool Allocation Process 4. Using Oracle’s Mass. Budgets & Auto. Allocations 5. Questions & Answers

Oracle Application Basis Types • • Activity: Directly associate the activity cost with the item. Item: Assign a fixed cost per item. Lot: Assign a lot charge to items and operations. Resource units: Charge overhead by multiplying the overhead amount by the number of resource units earned in the routing operation. • Resource value: Charge overhead by multiplying the overhead rate by the resource value earned in the routing operation. • Total value: Charge overhead by multiplying the total cost of the item by the material overhead rate.

Overhead Application Basis Type (Example) Overhead Cost Elements Sub-Elements And Application Basis Types Resource Overhead Mat’l Mgmt: Overhead Cost Material Overhead Facility: Activity: PO Process Resource Value Freight-In: Resource Value Total Value (Buy Items) MFG Engn’g: Benefits: Resource Units Prod. Control Activity: Indirect: Planning Process Resource Value

Oracle’s Overhead Application Method (Example) Material Overhead Resource Overhead Activity Application Basis Type Method of Applying Overhead P. O. Process Activity (Number of POs) Amount per Item Based on Activity Total Value (Buy Items) Single Rate ( Buy Items Only) Activity (# of Work Orders) Amount per Item Based on Activity Facility Resource Value (i. e Labor Cost) Single Rate Per Department Benefits Resource Value (i. e Labor Cost) Single Rate Plant-Wide MFG Engineering Resource Units (i. e. Std Hours) Single Amount Per Department Indirect Resource Value (i. e Labor Cost) Single Rate Per Department Material Management Freight-In Production Control Planning Process

Overhead Rates Assigned

Overhead Cost Applied To Item (Example)

Need Overhead Cost Pools Material Overhead Resource Overhead Material Management Freight-In Production Control Facility Benefits MFG Engineering Indirect Amount Per Item (Activity) Single Rate (Buy Items Only) Amount Per Item (Activity) Single Rate (Per Dept) Single Rate (Plant-Wide) Single Amt. (Per Dept) Single Rate (Per Dept) ? ? ? Overhead Cost Pools Dollar$ ? ? ? ? ? ? ? Activity / Volumes Number of Purchase Orders Total $ Value of Buy Items Number of Work Orders Resource $ Value Resource Units (Std Hours) Resource $ Value $$ % Rates or Amounts $$ % %

Presentation Agenda 1. Assess & Design – Overhead Allocation Methods – Standard Cost Overhead Sub-elements 2. Oracle’s Overhead Application Basis Types 3. Cost Pool Allocation Process 4. Using Oracle’s Mass. Budgets & Auto. Allocations 5. Questions & Answers

Cost Pool Allocation Step-Down Allocation Indirect Overhead Administration Budget Facility Budget Finance & IS Budget Manufacturing Organization Departmental Budget Material Overhead Cost Element Pools Material Mgmt Freight-In Production Control Overhead Cost Element Pools Facility Benefits MFG Engn’g Indirect

Cost Pool Allocation Many Departments

Cost Pool Allocation Indirect Overhead What Is The Process? Administration Budget Facility Budget Finance & IS Budget Manufacturing Organization Departmental Budget Material Overhead Cost Element Pools Material Mgmt Freight-In Production Control Overhead Cost Element Pools Facility Benefits MFG Engn’g Indirect

Summarize Agenda Items 1 -2 -3 1. Assess & Design – Overhead Allocation Methods – Standard Cost Overhead Sub-elements 2. Oracle’s Overhead Application Basis Types 3. Cost Pool Allocation Process 4. Using Oracle’s Mass. Budgets & Auto. Allocations 5. Questions & Answers

Summary - Agenda Items 1 -2 -3 • Design assessment > Keep the End in Mind • Overhead allocation principles • Allocation Methods: Traditional vs. Activity and the advantages and disadvantages • The applicability of overhead allocation methods • Oracle’s cost elements: MOH & ROH • Oracle’s application Basis Types • Step-down allocation process

Presentation Agenda 1. Assess & Design – Overhead Allocation Methods – Standard Cost Overhead Sub-elements 2. Oracle’s Overhead Application Basis Types 3. Cost Pool Allocation Process 4. Using Oracle’s Mass. Budgets & Auto. Allocations 5. Questions & Answers

Oracle’s Mass. Budgets Definition • A feature that allocates expenses across a group of cost centers, departments, divisions, and so on. It allows you to build a complete budget/Cost Pool using simple formulas based on actual results, other budget amounts, and statistics. – For example, you might want to allocate your facility overhead costs to each of your departments based on the square footage in each department.

Mass. Budgets vs. Mass. Allocations What is the Difference? • Same Functionality • Mass. Allocation’s Balance Types: – Actual – Encumbrance • Mass. Budget’s Balance Type – Budget only

Mass. Budget’s Major Features • Allocate cost pools to many accounts using a single formula • Base your allocations on Current Period, Previous Period, or Year Ago, Same Period • Base your allocations on monetary or statistical account balances • Define your allocation formulas once, and use them as frequently as needed • Run Mass. Budgets multiple times without reversing the outdated amounts • Use Mass. Budgets to do rate-based, usage-based and step-down allocations • Include summary accounts or foreign currency amounts in your allocations

Mass. Budgeting Process Step 1: Define Cost Pool Budget Step 5: Create Mass. Budget batches Step 2: Copy Operating Budget to Cost Pool Budget Step 6: Define a Mass. Budget formula per batch Step 3: Gather Allocation Bases (Statistical Data) Step 7: Validate Mass. Budget Batch formulas Step 4: Review and determine Allocation formulas Step 8: Generate Mass. Budget journals and Post

Step 1: Define Cost Pool Budget • Create a budget to represent a collection of estimated amounts for a range of accounting periods. You can use Auto. Copy to create a new budget from an existing budget.

Step 2: Copy Operating Budget Amounts 1. Hit Auto. Copy Button 2. Select Source Budget

Step 3: Gather Allocation Bases (Statistical Data) • Review all required statistical accounts and enter additional values into the Account Segment. For example Headcount, Production Hours, Resource $ Value, etc. • Enter Budget amounts/journals for each account combination for all statistical data.

Step 4: Determine Allocation Formulas • Review your requirements and determine the allocation formulas. For example determine your allocation basis to allocate IS overhead to other departments. • Define Rollup Groups and Summary Accounts for allocation purposes. • List the required Mass. Budget batches in sequence.

Step 5: Create Mass. Budget Batches for Each Step-Level • Group Mass. Budget batches by allocation level • This assures accurate cost pools

Step 6: Define Mass. Budget Formulas

Step 7: Validate Mass. Budget Batch Formulas • After you define a new allocation batch, or change an allocation formula, you must validate the batch by running the Mass. Budget Validation program. The program verifies that your allocation formulas conform to the allocation formula definition rules.

Step 8: Generate Mass. Budget Journals • Using budget journals maintain an audit trail for your budget balances. • Generate Mass. Budget Journals to create unposted budget journal batches based on your validated Mass. Budget formulas. The generated journal batch contains an entry for every allocation formula in the batch.

Understanding Oracle’s Formula Syntax OR Budget Pool (A)* [Usage Factor (B) / Total Usage (C)] Segment Assignments Types: - Looping >(Parent values only) - Summing >(Sum child values) - Constant >(Child Value or Parent with Summary Account) Target Account (T) Offset Account (O)

Presentation Agenda 1. Assess & Design – Overhead Allocation Methods – Standard Cost Overhead Sub-elements 2. Oracle’s Overhead Application Basis Types 3. Cost Pool Allocation Process 4. Using Oracle’s Mass. Budgets & Auto. Allocations 5. Questions & Answers

Oracle’s Auto. Allocation Definition • Auto. Allocations is a powerful feature to automate journal batch validation and generation for Mass. Budgets. • Two types of Auto. Allocation – Parallel Auto. Allocation: Validates and generates all the journal batches in your Auto. Allocation set simultaneously. – Step–Down: Create journal batches in a specific sequence. Order your journal batches so that the posted results of one step are used in the next step of the Auto. Allocation set.

Auto. Allocation Process Step 1: Define Auto. Allocation Set Step 2: Submit Auto. Allocation Step 3: Review Status of Auto. Allocation Step 4: Post Generated Journals

Step 1 Define Auto. Allocation Set

Step 2: Submit Auto. Allocation Set

Step 3: View Status of Auto. Allocation Set View Status for each Step

Now Let’s Put It All Together Example • Using Mass Budgeting create the Facility overhead cost pool. Assumptions: – The OH application method is a single rate based on resource value of the budgeted production volume per department. – You must perform all previous (top-level) allocations first. – Remember to create a step-down Auto. Allocation set to insure the proper sequence for an accurate cost pool.

Allocation Sequence 1. Administration Budget 2. Finance & IS Budget Facility Overhead Cost Pool 3. 4. Manufacturing Indirect Departments Metal Plating PCB Manufacturing Production Departments Sub-Assembly Final Assembly Test Packing

Mass. Budget Process Step 1: Define Cost Pool Budget Step 5: Create Mass. Budget batches Step 2: Copy Operating Budget to Cost Pool Budget Step 6: Define a Mass. Budget formula per batch Step 3: Gather Allocation Bases (Statistical Data) Step 7: Validate Mass. Budget Batch formulas Step 4: Review and determine Allocation formulas Step 8: Generate Mass. Budget journals.

Step 5: Define Mass Budget Batch • Defined Mass. Budget Batch: HR & Admin Allocation

Administration Expense (OH) Allocation Basis • Allocation Basis – Based on number of headcount per department • Note: You do not have to allocate all Admin. Expenses by the same basis • Remember to group all Administration allocations in the same Mass. Budget batch for proper sequencing later.

Administration Overhead Allocation 01 = Company 800 = Administration Parent T = Total All Departments HCNT = Headcount 8200 = Mass. Allocations Out Account 01 -800 -7000 Summary Account * Amount Type Relative Period USD PTD or YTD Current Period STAT PTD Current Period USD PTD Current Period C-C-C B 01 -T-HCNT / C-L-C C 01 -T-HCNT 8100 = Mass. Allocations In Currency Company-Department-Account A 7000 = Total Expense C-S-C T: 01 -T-8100 C-L-C O: 01 -530 -8200 C-C-C

Finance & IS Expense (OH) Allocation Basis • Possible Allocation Basis – Number of headcount per department – Optionally use multiple allocation steps: • Allocate Cost Accounting portion of Finance • Allocate IS expenses based on Departmental activity study of IS services consumed. • Allocate Payroll on headcount

Finance & IS Overhead Allocation 01 = Company 700 = Finance & IS Parent T = Total All Departments HCNT = Headcount 8200 = Mass. Allocations Out Account Currency Amount Type Relative Period USD PTD or YTD Current Period STAT PTD Current Period USD PTD Current Period Company-Department-Account A 01 -7000 Summary Account * C-C-C B 01 -T-HCNT / C-L-C C 01 -T-HCNT 7000 = Total Expense Parent 8100 = Mass. Allocations In C-S-C T: 01 -T-8100 C-L-C O: 01 -700 -8200 C-C-C

Facility Expense (OH) Allocation Basis • Allocation Basis – Square Footage per Department

Facility Overhead Allocation 01 = Company 840 = Facility Parent 7000 = Total Expense Parent T = Total All Departments SQFT = Square Footage 8100 = Mass. Allocations In 8200 = Mass. Allocations Out Account Currency Amount Type Relative Period USD PTD or YTD Current Period STAT PTD Current Period USD PTD Current Period Company-Department-Account A 01 -840 -7000 Summary Account * C-C-C B 01 -T-SQFT / C-L-C C 01 -T-SQFT C-S-C T: 01 -T-8100 C-L-C O: 01 -840 -8200 C-C-C

Auto. Allocation Process Step 1: Define Auto. Allocation Set Step 2: Submit Auto. Allocation Step 3: Review Status of Auto. Allocation Step 4: Post Generated Journals

Submit Auto. Allocation Set

Questions & Answers Michael Anenen SSi North America (312) 755 -8257 Michael. Anenen@us. ssiworldwide. com Paul Kraskiewicz SSi North America (312) 755 -8262 Paul. Kraskiewicz@us. ssiworldwide. com