Assignment number: 1 Cost classification and costing 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 problems "Pillow Corporation Ltd. manufactures three types of pillow cases, namely Standard, XL and Nursing C. A summary of its costs for the past reporting period is summarized in the Pillow fact sheet." "1. On the ""Pillow"" sheet, break down the corporation's total costs into the categories given." 2. "Calculate the cost of one unit of output (1 unit of pillow). Use the additional data from item B. on the ""Pillow"" sheet to do this." Separate task "Corporation Profi Ltd. provides two types of consulting services - auditing and tax consulting. A summary of its costs for the past reporting period is summarized on the ""Separate"" sheet." "3. On the ""Separately"" sheet, break down the total costs of Profi Ltd. into the specified categories." 4. "Calculate the cost of one unit of performance (1 hour of consulting service). Use the additional data from item B. on the ""Separately"" sheet to do this." ##### Sheet/List 2 ##### Pillow s.r.o. A. Cost for past month Cost item Amount Prime Overhead Direct Indirect Variable Fixed manuf. non-man. Standard XL Nursing C manuf. non-man. Standard pillow material "2,800 €" Energy to power sewing machines 600 € Basic salary and health and social insurance of the Managing Director "2,700 €" Task wages and health and social insurance of seamstresses - XL pillow production "2,380 €" Cost of statutory audit of financial statements "1,000 €" Energy for lighting in the production hall 300 € Rental of the production hall determined by the price per m2 "3,750 €" Material for the production of XL pillows "2,715 €" Task wages and health and social insurance of seamstresses - Standard pillow production "4,200 €" Monthly (time) depreciation of furniture and sewing machines "1,185 €" Office supplies consumed at the head office 104 € Lump sum to bailiff for debt recovery 175 € Oil for lubrication of machines 100 € Costs for PR and promotion of the company 675 € Administrator's basic salary and health and social insurance "1,700 €" Basic salary and health and social incurance of production foreman "2,330 €" Invoice for business mediation "1,150 €" Material for the production of C cushions "3,200 €" Invoice received for regular transport of material 725 € Cleaning of production hall and waste disposal - monthly lump sum 460 € Production of special sewing template C - replacement after one month 200 € Task wages and health and social insurance of seamstresses - pillow production C "2,240 €" Total "34,689.00 EUR" B. Additional data on production and sales for the past month Standard XL Nursing C Total Costs for the past month "2,000 pc." "1,000 pc." 500 pc. "3,500 pc." Production volume "1,800 pc." 800 pc. 460 pc. "3,060 pc." Sales volume 6 EUR/pc. 15 EUR/pc. 32 EUR/pc. Selling price 15 min./pc. 17 min./pc. 32 min./pc. Average production time Indirect production costs are allocated according to the number of minutes spent in production. Indirect non-production costs are allocated according to the volume of sales realised. C. Final calculation per 1 piece Standard XL Nursing C Total Calculation method Direct costs - material Direct costs - wages and CSR Other direct Total direct costs Production indirect overheads Non-production indirect overheads Total indirect costs Total costs Profit Selling price ##### Sheet/List 3 ##### Pillow s.r.o. A. Cost for past month Cost item Amount Prime Overhead Direct Indirect Variable Fixed manuf. non-man. Standard XL Nursing C manuf. non-man. Standard pillow material "2,800 €" " 2,800.00 € " " 2,800.00 € " " 2,800.00 € " Energy to power sewing machines 600 € 600.00 € 600.00 € 600.00 € Basic salary and health and social insurance of the Managing Director "2,700 €" " 2,700.00 € " " 2,700.00 € " " 2,700.00 € " Task wages and health and social insurance of seamstresses - XL pillow production "2,380 €" " 2,380.00 € " " 2,380.00 € " " 2,380.00 € " Cost of statutory audit of financial statements "1,000 €" " 1,000.00 € " " 1,000.00 € " " 1,000.00 € " Energy for lighting in the production hall 300 € 300.00 € 300.00 € 300.00 € Rental of the production hall determined by the price per m2 "3,750 €" " 3,750.00 € " " 3,750.00 € " " 3,750.00 € " Material for the production of XL pillows "2,715 €" " 2,715.00 € " " 2,715.00 € " " 2,715.00 € " Task wages and health and social insurance of seamstresses - Standard pillow production "4,200 €" " 4,200.00 € " " 4,200.00 € " " 4,200.00 € " Monthly (time) depreciation of furniture and sewing machines "1,185 €" " 1,185.00 € " " 1,185.00 € " " 1,185.00 € " Office supplies consumed at the head office 104 € 104.00 € 104.00 € 104.00 € Lump sum to bailiff for debt recovery 175 € 175.00 € 175.00 € 175.00 € Oil for lubrication of machines 100 € 100.00 € 100.00 € 100.00 € Costs for PR and promotion of the company 675 € 675.00 € 675.00 € 675.00 € Administrator's basic salary and health and social insurance "1,700 €" " 1,700.00 € " " 1,700.00 € " " 1,700.00 € " Basic salary and health and social incurance of production foreman "2,330 €" " 2,330.00 € " " 2,330.00 € " " 2,330.00 € " Invoice for business mediation "1,150 €" " 1,150.00 € " " 1,150.00 € " " 1,150.00 € " Material for the production of C cushions "3,200 €" " 3,200.00 € " " 3,200.00 € " " 3,200.00 € " Invoice received for regular transport of material 725 € 725.00 € 725.00 € 725.00 € Cleaning of production hall and waste disposal - monthly lump sum 460 € 460.00 € 460.00 € 460.00 € Production of special sewing template C - replacement after one month 200 € 200.00 € 200.00 € 200.00 € Task wages and health and social insurance of seamstresses - pillow production C "2,240 €" " 2,240.00 € " " 2,240.00 € " " 2,240.00 € " Total "34,689.00 EUR" " 17,535.00 € " " 9,650.00 € " " 7,504.00 € " " 7,000.00 € " " 5,095.00 € " " 5,640.00 € " " 9,450.00 € " " 7,504.00 € " " 18,235.00 € " " 16,454.00 € " " 34,689.00 € " " 34,689.00 € " " 34,689.00 € " B. Additional data on production and sales for the past month Standard XL Nursing C Total Costs for the past month "2,000 pc." "1,000 pc." 500 pc. "3,500 pc." Production volume "1,800 pc." 800 pc. 460 pc. "3,060 pc." Sales volume 6 EUR/pc. 15 EUR/pc. 32 EUR/pc. Selling price 15 min./pc. 17 min./pc. 32 min./pc. Average production time Indirect production costs are allocated according to the number of minutes spent in production. Indirect non-production costs are allocated according to the volume of sales realised. C. Final calculation per 1 piece Standard XL Nursing C Total Calculation method Direct costs - material 1.40 EUR/pc. 2.72 EUR/pc. 6.40 EUR/pc. " 8,715.00 € " simple division Direct costs - wages and CSR 2.10 EUR/pc. 2.38 EUR/pc. 4.48 EUR/pc. " 8,820.00 € " simple division Other direct 0.40 EUR/pc. 200.00 € simple division Total direct costs 3.50 EUR/pc. 5.10 EUR/pc. 11.28 EUR/pc. " 17,735.00 € " Production indirect overheads 2.25 EUR/pc. 2.55 EUR/pc. 4.80 EUR/pc. " 9,450.00 € " allocation key minute 0.15 EUR/pc. Non-production indirect overheads 1.20 EUR/pc. 3.00 EUR/pc. 6.40 EUR/pc. " 7,504.00 € " allocation key sales 20.00% Total indirect costs 3.45 EUR/pc. 5.55 EUR/pc. 11.20 EUR/pc. " 16,954.00 € " Total costs 6.95 EUR/pc. 10.65 EUR/pc. 22.48 EUR/pc. " 34,689.00 € " Profit -0.95 EUR/pc. 4.36 EUR/pc. 9.52 EUR/pc. " 2,831.00 € " ROS 8% Selling price 6.00 EUR/pc. 15.00 EUR/pc. 32.00 EUR/pc. " 37,520.00 € " Sales " 10,800.00 € " " 12,000.00 € " " 14,720.00 € " " 37,520.00 € " Time in production process "30,000 min." "17,000 min." "16,000 min." "63,000 min." ##### Sheet/List 4 ##### Profi s.r.o. A. Cost for past month Cost item Amount Prime Overhead Direct Indirect Variable Fixed Audit Tax consulting Monthly licence for tax return software 850 EUR 850 EUR 850 EUR 850 EUR Personnel costs attributable to the audit according to the statements of work "35,000 EUR" "35,000 EUR" "35,000 EUR" "35,000 EUR" Depreciation of furniture and office equipment "20,000 EUR" "20,000 EUR" "20,000 EUR" Travel expenses for travel to tax and audit clients "2,100 EUR" "2,100 EUR" "2,100 EUR" Personal costs attributable to tax consultancy as per statements of work "20,000 EUR" "20,000 EUR" "20,000 EUR" "Non-billable personal costs - holidays, training, etc." "10,200 EUR" "10,200 EUR" "10,200 EUR" Rent for office space in the city centre "6,000 EUR" "6,000 EUR" "6,000 EUR" Costs of printing and sending tax returns "1,700 EUR" "1,700 EUR" "1,700 EUR" "Lump sum cost for cleaning, reception, security of common areas" "2,900 EUR" "2,900 EUR" "2,900 EUR" Costs for auditor training "1,500 EUR" "1,500 EUR" "1,500 EUR" Total "100,250 EUR" "55,000 EUR" "45,250 EUR" "36,500 EUR" "22,550 EUR" "41,200 EUR" "35,000 EUR" 850 EUR "100,250 EUR" "100,250 EUR" "35,850 EUR" B. Additional data on production and sales for the past month Audit Taxes Celkem Total invoiced performances per month "1,240 hours" 560 hours "1,800 hours" Selling price 62.50 EUR/hour 75.00 EUR/hour x Employees reported a total of "1,500 hours" 800 hours "2,300 hours" Direct and indirect costs are allocated according to the number of hours billed. C. Resulting cost calculation for 1 hour of service provision Audit Taxes Total Direct costs - in kind 1.21 EUR/hour 4.55 EUR/hour "4,050 EUR" Direct costs - personnel (salaries and CAP only) 28.23 EUR/hour 35.71 EUR/hour "55,000 EUR" Total direct costs 29.44 EUR/hour 40.27 EUR/hour "59,050 EUR" Total indirect costs 22.89 EUR/hour 22.89 EUR/hour "41,200 Kč" 22.89 EUR/hour Total costs 52.32 EUR/hour 63.16 EUR/hour Profit 10.18 EUR/hour 11.84 EUR/hour Selling price 62.50 EUR/hour 75.00 EUR/hour ROS 16.28% 15.79% 28.226 Kč 1.210 Kč 29.435 Kč