1 Use cell references in your formulas instead of copying or copying values. DO NOT round decimal Excel results to whole units in ALL EXCEL CALCULATIONS!!! Solved tasks "Corporation Garner ltd. sells three main product groups - robotic lawn mowers, small tractors and provides service for both. " "1. In item B. on the ""Gardener"" worksheet, prepare a unit costing for each type of product. Use a simple 2-stage overhead rate allocation." 2. Refine the calculation from item 1 by using a multi-stage allocation in which you first reallocate service unit costs according to the following allocation keys: Delivering centre Rental Canteen Allocation driver = m2 Alocation driver = number of persons Receiving centre Sales 36.00 m2 15 persons Rental x x Canteen 17.00 m2 x Management and administration 5.00 m2 5 persons 58.00 m2 20.00 Separate task "Korporace Profi s.r.o. provides two types of consulting services - audit and tax consulting. A summary of its costs for the past reporting period is summarized in the ""Separate"" sheet." 3. "On the sheet ""Separately"", prepare a costing for 1 hour of consulting services of Profi Ltd. Allocate the indirect costs using a one-step allocation." 4. "Refine the calculation from step 1 using a multi-stage allocation, first reallocating the costs of the service units according to the following allocation keys: " supplier Facility man. IT Cost driver = m2 Cost driver - numer of connection points receiving (customer) Facility x x IT 50.00 m2 x Operations 280.00 m2 70.00 Admin 100.00 m2 10.00 ##### Sheet/List 2 ##### Gardener ltd. A. Basic data Lawn mowers Small tractors Service Total Sales volume 340 pcs 120 pcs "3,400 hours" x Number of offers made to prospective buyers 420 351 286 1057 Average selling price "1,100 EUR/pc." "2,100 EUR/pc." 20 EUR/hour x Costs: "631,939 EUR" Allocation base Cost of goods sold "269,229 EUR" "149,100 EUR" x "418,329 EUR" pieces Purchased services x x "38,110 EUR" "38,110 EUR" hours "Indirect costs of the ""Sales"" centre" x x x "62,750 EUR" number of offers made "Indirect costs of the ""Rent"" centre" x x x "43,500 EUR" revenues "Indirect costs of the ""Canteen"" centre" x x x "32,250 EUR" revenues "Indirect costs of the ""Management and admin"" centre" x x x "37,000 EUR" revenues B. Resulting costing with one-step allocation Lawn mowers (1 piece) Small tractors (1 piece) Service (1 hour) Total Notes Direct costs 792 EUR/pc. "1,243 EUR/pc." 11.21 EUR/hour "456,439 EUR" "Indirect costs - ""Sales""" 73 EUR/pc. 174 EUR/pc. 4.99 EUR/pc. "62,750 EUR" A. rate = 59.37 EUR/offer "Indirect costs - ""Rent""" 69.12 EUR/pc. 131.95 EUR/pc. 1.23 EUR/pc. "43,500 EUR" % rent = 6.28% "Indirect costs - ""Cantina""" 51 EUR/pc. 98 EUR/pc. 0.91 EUR/hour "32,250 EUR" % canteen= 4.66% "Indirect costs - ""Management and admin""" 59 EUR/pc. 112 EUR/pc. 1.04 EUR/hour "37,000 EUR" % Admin= 5.34% Total indirect costs 252 EUR/pc. 516 EUR/pc. 8.17 EUR/hour "175,500 EUR" Total costs "1,044 EUR/pc." "1,758 EUR/pc." 19.38 EUR/hour "631,939 EUR" Profit 56 EUR/pc. 342 EUR/pc. 0.12 EUR/hour "60,361 EUR" Selling price "1,100 EUR/pc." "2,100 EUR/pc." 19.50 EUR/hour "692,300 EUR" 692300 ROS 5.1% 16.3% 0.6% 8.7% additional " A. Indirect c. ""Sales"" by type of performance" "374,000 EUR" "252,000 EUR" "66,300 EUR" C. Step-down allocation (=reallocation) of costs and their resulting costing per unit of output Reallocation of INDIRECT COSTS: 1st reallocation Indirect cost 2nd reallocation Indirect cost Departmental indirect cost before allocation Service cost - Rent after 1 st reallocation Service cost Canteen after 2nd realoccation Sales Rent Canteen Management and admin Sum Lawn mowers (1 piece) Small tractors (1 piece) Service (1 hour) Total Notes Direct costs "Indirect costs - ""Sales""" "Indirect costs - ""Rent""" "Indirect costs - ""Cantina""" "Indirect costs - ""Management and admin""" Total indirect costs Total costs Profit Selling price ROS ##### Sheet/List 3 ##### Gardener ltd. A. Basic data Lawn mowers Small tractors Service Total Sales volume 340 pcs 120 pcs "3,400 hours" x Number of offers made to prospective buyers 420 351 286 1057 Average selling price "1,100 EUR/pc." "2,100 EUR/pc." 20 EUR/hour x Costs: "631,939 EUR" Allocation base Cost of goods sold "269,229 EUR" "149,100 EUR" x "418,329 EUR" piece Purchased services x x "38,110 EUR" "38,110 EUR" hours "Indirect costs of the ""Sales"" centre" x x x "62,750 EUR" numer of offers made "Indirect costs of the ""Rent"" centre" x x x "43,500 EUR" revenues "Indirect costs of the ""Canteen"" centre" x x x "32,250 EUR" revenues "Indirect costs of the ""Management and admin"" centre" x x x "37,000 EUR" revenues B. Resulting costing with one-step allocation Lawn mowers (1 piece) Small tractors (1 piece) Service (1 hour) Total Notes Direct costs 791.85 EUR/pc. "1,242.50 EUR/pc." 11.21 EUR/pc. "456,439 EUR" "Indirect costs - ""Sales""" 73.33 EUR/pc. 173.65 EUR/pc. 4.99 EUR/pc. "62,750 EUR" A. rate = 59.37 EUR/offer "Indirect costs - ""Rent""" 69.12 EUR/pc. 131.95 EUR/pc. 1.23 EUR/pc. "43,500 EUR" % rent = 6.28% "Indirect costs - ""Cantina""" 51.24 EUR/pc. 97.83 EUR/pc. 0.91 EUR/pc. "32,250 EUR" % canteen= 4.66% "Indirect costs - ""Management and admin""" 58.79 EUR/pc. 112.23 EUR/pc. 1.04 EUR/pc. "37,000 EUR" % Admin= 5.34% Total indirect costs 252.48 EUR/pc. 515.66 EUR/pc. 8.17 EUR/pc. "175,500 EUR" Total costs "1,044.33 EUR/pc." "1,758.16 EUR/pc." 19.38 EUR/pc. "631,939 EUR" Profit 55.67 EUR/pc. 341.84 EUR/pc. 0.12 EUR/pc. "60,361 EUR" Selling price "1,100.00 EUR/pc." "2,100.00 EUR/pc." 19.50 EUR/pc. "692,300 EUR" ROS 5.1% 16.3% 0.6% 8.7% additional " A. Indirect c. ""Sales"" by type of performance" "374,000 EUR" "252,000 EUR" "66,300 EUR" C. Step-down allocation (=reallocation) of costs and their resulting costing per unit of output Reallocation of INDIRECT COSTS: 1st reallocation Indirect cost 2nd reallocation Indirect cost Departmental indirect cost before allocation Service cost - Rent after 1 st reallocation Service cost Canteen after 2nd realoccation Sales " 62,750.00 EUR " " 27,000.00 EUR " " 89,750.00 EUR " " 33,750.00 EUR " " 123,500.00 EUR " Rent " 43,500.00 EUR " "-43,500.00 EUR " - EUR - EUR - EUR Canteen " 32,250.00 EUR " " 12,750.00 EUR " " 45,000.00 EUR " "-45,000.00 EUR " - EUR Management and admin " 37,000.00 EUR " " 3,750.00 EUR " " 40,750.00 EUR " " 11,250.00 EUR " " 52,000.00 EUR " Sum rate 750.00 EUR/m2 rate "2,250 EUR/pers." " 175,500.00 EUR " Lawn mowers (1 piece) Small tractors (1 piece) Service (1 hour) Total Notes Direct costs 791.85 EUR/pc. "1,242.50 EUR/pc." 11.21 EUR/pc. "456,439 EUR" "Indirect costs - ""Sales""" 144.33 EUR/pc. 341.76 EUR/pc. 9.83 EUR/pc. " 123,500.00 EUR " rate 116.84 EUR/offer "Indirect costs - ""Rent""" 0.00 EUR/pc. 0.00 EUR/pc. 0.00 EUR/pc. - EUR "Indirect costs - ""Cantina""" 0.00 EUR/pc. 0.00 EUR/pc. 0.00 EUR/pc. - EUR "Indirect costs - ""Management and admin""" 82.62 EUR/pc. 157.74 EUR/pc. 1.46 EUR/pc. " 52,000.00 EUR " rate 7.51% Total indirect costs 226.96 EUR/pc. 499.49 EUR/pc. 11.29 EUR/pc. " 175,500.00 EUR " Total costs "1,018.81 EUR/pc." "1,741.99 EUR/pc." 22.50 EUR/pc. " 631,939.00 EUR " Profit 81.19 EUR/pc. 358.01 EUR/pc. -3.00 EUR/pc. " 60,361.00 EUR " Selling price "1,100.00 EUR/pc." "2,100.00 EUR/pc." 19.50 EUR/pc. " 692,300.00 EUR " ROS 7.4% 17.0% -15.4% 8.7% ##### Sheet/List 4 ##### Profi s.r.o. A. Basic data Audit Taxes Total Total invoiced performance per month "1,280 hours" 560 hours "1,840 hours" Selling price 63 EUR/hour 75 EUR/hour x Direct costs 29 EUR/hour 40 EUR/hour x Indirect costs Allocation base Indirect costs of the 'Facilities Management' centre x x " 6,000 EUR " direct costs "Indirect costs of the ""IT"" centre" x x " 16,500 EUR " direct costs "Indirect costs of the ""Operations"" centre" x x " 21,500 EUR " invoiced hours "Indirect costs of the ""Admin"" centre" x x " 140,000 EUR " Sales Total indirect cost " 184,000 EUR " B. Resulting cost calculation with one-step allocation Audit (1 hours.) Tax (1 hour.) Total Notes Direct costs "Indirect costs - ""Facilities Management""" "Indirect costs - ""IT""" "Indirect costs - ""Operations""" "Indirect costs - ""Admin""" Total indirect costs Total costs Profit Selling price ROS 4.0% C. Multi-stage allocation (=reallocation) of costs and their resulting costing per unit of output Reallocation of DIRECT COSTS: 1st real stage Indirect cost 2nd real stage Indirect cost Primary indirect cost Secondary - Facility after 1st real. Secondary - IT after the 2nd real. Facility management IT Operations Admin Amount Audit (1 hour) Tax (1 hour) Total Note Direct costs "Indirect costs - ""Facilities Management""" "Indirect costs - ""IT""" "Indirect costs - ""Operations""" "Indirect costs - ""Admin""" Total indirect costs Total costs Profit Selling price ROS