Cost Sheet Format In Excel 42167

Partial file snippet.

Sheet 1: TDABC Exercise

	
	
	
	
	
	
	
	
		Time-driven activity-based costing
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		
 
		
		
	
		Exercise
		

		

		

		

		
	
		

		

		

		

		

		

		
	
		Use the information provided below and the TDABC spreadsheet to determine:
		

		

		

		

		

		
	
		

		1. Theoretical capacity.
		

		

		

		

		
	
		

		2. Practical capacity.
		

		

		

		

		
	
		

		3. Cost rate per minute (to 3 decimal places).
		

		

		

		

		
	
		

		4. Any unused capacity and the associated cost.
		

		

		

		

		
	
		

		5. Overhead cost allocation for a specific product.
		

		

		

		

		
	
		

		

		

		

		

		

		
	
		

		

		

		

		

		

		
	
		

		

		

		

		

		

		
	
		A window manufacturer makes windows for houses with aluminium or timber frames and single glass panes.
		

		

		

		

		

		
	
		

		

		

		

		

		

		
	
		The 'inventory management' functional area contains 8 staff members, each working 230 8-hour days per year. 
Non-productive time has been estimated at 1-hour per work day.
		

		
	
		

		

		

		

		

		

		
	
		The total budgeted costs for the inventory management function for the year is $1,020,600.
		

		

		

		

		

		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Three key activities are undertaken by the inventory management area, these are:
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		1. Ordering inventory (raw materials).
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		2. Receiving of inventory into storage.
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		3. Picking inventory items for production.
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		NOTE: You can first attempt the exercise using the blank template that does not contain any formulas to test that you can calculate the items yourself; alternatively you can first use the template with the formulas to help you understand the calculations and then attempt with the blank template.
		
	
		The total budgeted time for these activities for the year are:
		

		

		

		

		

		
	
		

		1. Ordering inventory (raw materials).
		169,000
		minutes
		

		

		
	
		

		2. Receiving of inventory into storage.
		294,000
		minutes
		

		

		
	
		

		3. Picking inventory items for production.
		299,500
		minutes
		

		

		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		A summary of the time required for these activities is presented below:
		

		

		

		

		

		Go to TDABC spreadsheet with no formulas
		
	
		

		

		

		

		

		

		
	
		

		

		Standard time per activity
		Additional time per activity
		Description of complexity requiring additional time
		

		

		

		

		

		

		

		

	
	
		

		Ordering inventory
		20
		5
		Overseas supplier rather than local.
		

		Go to TDABC spreadsheet with formulas
		
	
		

		Receiving of inventory
		30
		15
		Fragile materials (glass) requiring special handling.
		

		
	
		

		Picking inventory for production
		15
		10
		Fragile materials (glass) requiring special handling.
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		The SW-101 Window is one of the organisation's best selling products. 
Use the following activity transaction information to determine the overhead cost allocation.
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		Number of standard transactions
		Number of complex transactions
		

		

		

		

		

		

		

		

		

	
	
		

		Ordering inventory
		3,000
		1,500
		

		

		

		

		

		

		

		

		

	
	
		

		Receiving of inventory
		2,800
		3,000
		

		

		

		

		

		

		

		

		

	
	
		

		Picking inventory for production
		3,200
		3,050
		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		KnowledgEquity is an independent study support provider and is not affiliated with CPA Australia
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		Copyright in the material is owned by KnowledgEquity, unless otherwise indicated, and you agree not to infringe any intellectual property rights, including trademarks, owned by KnowledgEquity.
		

		

		

		

		

		

		

		

	
	
		

		KnowledgEquity acknowledges that CPA Australia owns copyright of all CPA Program® study guide materials and specific learning objectives within those materials.
		

		

		

		

		

		

		

		

	
	
		

		Disclaimer
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		The content and materials are prepared and made available by KnowledgEquity on a good faith basis. The content and materials are not advice and KnowledgEquity does not warrant their accuracy, completeness or adequacy. You should make your own enquiries before entering any transaction on the basis of the content on KnowledgEquity’s website.
		

		

		

		

		

		

		

		

	
	
		

		KnowledgEquity does not accept responsibility for any errors or omissions in the materials, nor for the results of any action or inaction, whether from full or part reliance, on the basis of information in these materials.
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	



Sheet 2: TDABC_blank

	
	
	
	
	
	
	
	
	
	
	
	
	
		Using TDABC to allocate indirect costs
		

		Legend:
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		input
		unprotected bordered cells with blue font and yellow background are input cells
		

		

		

		

		

		

		

	
	
		

		Theoretical capacity (total time available) in minutes:
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		Number of personnel
		Work day in hours
		Number of minutes 
in work day
		Number of work days 
in the period
		Theoretical capacity
in minutes
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		Practical capacity
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		Non-productive work time calculator:
		

		

		

		

		

	
	
		

		

		Theoretical capacity in minutes:
		

		

		

		

		

		

		

		

		

		Hours per day
		Minutes per day
		Days per period
		Number of personnel
		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		Total
		

		

		

		

	
	
		

		

		Less:
		Time spent by personnel in non-productive work (e.g. training)
		

		

		

		

		

		

		

		 -   
		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		Equals:
		Practical capacity in minutes
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		TDABC cost rate per minute
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		Total costs to be allocated
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		
 
		
		
	
		

		

		Practical capacity
		

		 - 
		

		

		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		
	
		

		

		TDABC cost rate per minute (rounded to three decimal places)
		

		

		

		

		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		
	
		

		Budgeted costs and unused capacity 
		

		

		

		

		

		

		

		

		

		

		

		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		
	
		

		

		Activity
		Budgeted time (minutes)
		Cost rate 
per minute
		Budgeted 
costs
		

		

		

		
	
		

		

		

		

		

		

		

		

		

		
	
		

		

		

		

		

		

		

		

		

		
	
		

		

		

		

		

		

		

		

		

		
	
		

		

		

		

		

		

		

		

		

		
	
		

		

		(1) Total time and cost
		

		

		

		

		

		

		

		
	
		

		

		(2) Total practical capacity and costs
		

		

		

		

		

		

		
	
		

		

		Unused capacity (2) - (1) 
		

		

		

		

		

		

		

		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		
	
		

		Budgeted time for standard and complex activity performance
		

		

		

		

		

		

		

		

		

		

		

		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		Product:
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		Standard activity performance
		Complex activity performance
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		Activity
		Unit 
time
		Transact
-ions
		Standard time
		Additional unit time
		Transact
-ions
		Additional time
		Total 
time
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		Budgeted time
		

		Standard time
		

		Additional time
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		Budgeted costs allocated to 
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		Activity
		Budgeted activity 
time (mins)
		Cost rate 
per minute
		Costs 
allocated
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		Total times

The words contained in this file might help you see if this file matches what you are looking for:

...Sheet tdabc exercise timedriven activitybased costing use the information provided below and spreadsheet to determine theoretical capacity practical cost rate per minute decimal places any unused associated overhead allocation for a specific product window manufacturer makes windows houses with aluminium or timber frames single glass panes inventory management functional area contains staff members each working hour days year nonproductive time has been estimated at work day total budgeted costs function is three key activities are undertaken by these ordering raw materials receiving of into storage picking items production note you can first attempt using blank template that does not contain formulas test calculate yourself alternatively help understand calculations then minutes summary required presented go no standard activity additional description complexity requiring overseas supplier rather than local fragile special handling sw one organisation s best selling products following...

Related files

Share

Help

Related files

Share

Share to social media

Help

Login Area