AutoSave O A A C 5 . 9 ... X: A325 dream choc tables su24 3 No Label Q Search (Cmd + Ctrl + U) Home Insert Page Layout Formulas View Automate Comments Share Draw Data Review Developer Insert v Calibri (Body) v 11 AA = a Wrap Text v General Ap . O. Ex Delete v Conditional Format Cell Sort & Find & Sensitivity Analyze Paste BIU BY vAv E Merge & Center v $ ~ % 9 Formatting as Table Styles Format v Filter Select Data P8 4 fx R A D E F G H K L M N P Q Average labor rates and capacity volumes by labor area Area Labor rate/hour 1.25 oz bar 3.0 oz bar 3.25 oz bar 4 Pouring 15.40 480 200 184 5 Inspecting 11.00 240 480 480 6 Foiling 9.90 175 175 175 7 Labeling 9.90 175 175 175 8 9 Labor rate/hour includes payroll taxes and benefits. 10 Capacity is measured in the number of bards per hour. 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 21 Table 1 Table 2 Table 3 Table 4 Table 5 Job costing ABC Process costing + + 130% Ready iJ Accessibility: Good to goAutoSave O A A C 5 . 9 ... x: A325 dream choc tables su24 @ No Label Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Developer Comments Share Calibri (Body) v 11 AA = =1 ap Wrap Text v Custom Insert v Ex Delete v V v Z Ap . O. Paste BIUV DVAv E = = Merge & Center v $ ~ % 9 Conditional Format Cell Sort & Find & Sensitivity Analyze Formatting as Table Styles Format v Filter Select Data B9 X V fx =SUM(B4:B8) A B C D E F G H K L M N O P Table 3 W NY Budgeted monthly overhead cost breakdown Cost item Amount Admin costs 1,000 UI P Production area supplies 3,800 6 Salaries 10,000 Customer service 3,000 8 Lease payments 2,000 9 Total budgetred overhead costs $ 19,800 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Table 1 Table 2 Table 3 Table 4 Table 5 Job costing ABC Process costing + Ready iJ { Accessibility: Good to go + 140%AutoSave O A A C 5 . 9 ... X: A325 dream choc tables su24 @ No Label Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Developer Comments Share v 11 a Wrap Text v Accounting Insert v Calibri (Body) AA AP- O. Ex Delete v V v Z Paste BIUV V E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Sensitivity Analyze Formatting as Table Styles Format v Filter Select Data C28 4 X fx 0.69 A B C D E F G H K L M N O 16 Pouring 0.03 $ 0.08 $ 0.08 17 Inspecting 0.05 $ 0.02 $ 0.02 18 Foiling 0.06 0.06 $ 0.06 19 Labeling 0.06 0.06 0.06 20 Total labor cost per bar S 0.20 S 0.22 S 0.22 21 22 Panel C: Compute estimated overhead cost per bar 23 Total overhead costs S 19,800 24 Bottleneck bars/hour 175 25 Hours/day 8 26 Avg. work days per month 20.5 27 Divided by capacity volume per month 28700 28 Overhead cost per bar .69 29 30 Panel D: Compute estimated profitability per bar 31 1.25 oz 1.25 oz 3.0 oz 3.25 oz 32 Organic Non-organic Non-organic Organic 33 Price per bar $ 1.50 $ 1.40 $ 2.40 $ 2.55 34 Cost per bar 35 Total materials cost 0.39 $ 0.24 $ 0.58 $ 0.97 36 Total labor cost 0.20 $ 0.20 $ 0.22 0.22 37 Overhead cost per bar 0.69 0.69 $ 0.69 0.69 38 Total cost per bar $ 1.28 1.13 $ 1.49 $ 1.88 39 Profit per bar $ 0.22 S S 0.91 $ 0.67 40 Profit percentage 14.7% 19.3% 37.9% 26.3% 41 42 Additional costs required for certial flavor additives 43 44 45 46 47 Table 1 Table 2 Table 3 Table 4 Table 5 Job costing ABC Process costing + Ready iJ Accessibility: Good to go + 130%AutoSave O A A C 5 . 9 ... X: A325 dream choc tables su24 @ No Label Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Developer Comments Share Calibri (Body) v 11 AA a Wrap Text v General Insert v AY . O. Ex Delete v V v Z Paste BIUV DVAv E EE Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Sensitivity Analyze Formatting as Table Styles Format v Filter Select Data K23 fx B D E F G H 1 J K L M N 0 P Q R S T Table 5 Additional estimated data for requirement 5 Panel A: Job-specific information Job Job 2 Job 3 Job 4 1.25 oz 1.25 oz 3.0 oz 3.25 oz Organic Non-organic Non-organic Organic Other jobs Total month 7 No. of bars 10,000 5,000 200 300 10,500 26,000 8 Cost of chocolate 3,135 $ 945 $ 84 $ 274 $ 3,162 $ 7,600 9 Cost of foil 274 $ 133 11 $ 18 $ 374 S 810 10 Cost of label 347 $ 163 $ 16 $ 21 283 $ 830 11 3,756 5 1,241 $ 111 5 313 5 3,819 9,240 12 13 Panel B: Beginning work-in-process (BWIP), direct labor (DL), and overhead (OH) costs added (per month) 14 BWIP- materials BWIP-CC DL added OH added Total added 15 Pouring/inspection 1,550 757 $ 2,348 B $ 1,800 $ 4,148 16 Foiling 1,430 $ 400 $ 1,830 17 Labeling 1,016 $ 1,400 $ 2,416 18 Total area costs added 3,600 $ 8,394 19 Other overhead costs 20 Supplies 200 200 21 Admin costs 1000 1000 22 Salaries 10000 10000 23 Customer service :00 3000 24 Lease payment 2000 25 Total other overhead 16200 16200 26 Total costs added $ 1,550 $ 757 $ 4,794 $ 19,800 $ 24,594 27 Total OH added represents supplies costs traced directly to labor areas. 28 Total other overhead added represents other overhead costs that cannot be directly traced to labor areas. 29 30 Panel C: Expected monthly production volume (in bars) 31 Type of production Total bars 1.25 oz 3.0/3.25 oz /IP % completed 32 Beginning WIP in pouring/inspection 3000 2500 50 33 Bars satrted in pouring/inspection 26000 25000 1000 34 Bars completed in pouring/inspection 27000 26500 500 50% 35 Bars foiled 26600 2612 475 36 Bars labeled (assume 25% bright stock) 19950 19594 356 37 38 Panel D: Activity-based information 39 Activity Actual amount Level Driver Actual volume 40 Area supplies 3,600 U Trace to areas 41 Setting up melting pots 0 Batch Setups LOO 42 Purchasing 2,000 Batch Purchase orders 80 43 Filling orders 6,300 Customer # orders 500 44 Designing labels 4,000 Customer Design hours 40 Table 1 Table 2 Table 3 Table 4 Table 5 Job costing ABC Process costing + Ready iJ Z Accessibility: Good to go + 100%AutoSave O A A C 5 . 9 ... X: A325 dream choc tables su24 @ No Label Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Developer Comments Share Insert v Calibri (Body) v 11 AA a Wrap Text v General V v Z AY . O. Ex Delete v Paste EE Find & Analyze BIUV DVAv Merge & Center v $ ~ % " Conditional Format Cell Sort & Sensitivity Formatting as Table Styles Format v Filter Select Data K23 4 fx D E F G H 1 J K L M N 0 P Q R S T 27 Total OH added represents supplies costs traced directly to labor areas. 28 Total other overhead added represents other overhead costs that cannot be directly traced to labor areas. 29 30 Panel C: Expected monthly production volume (in bars) 31 Type of production Total bars 1.25 oz 3.0/3.25 oz (IP % completed 32 Beginning WIP in pouring/inspection 3000 2500 500 33 Bars satrted in pouring/inspection 26000 25000 1000 34 Bars completed in pouring/inspection 27000 26500 500 50% 35 Bars foiled 26600 26125 475 0 36 Bars labeled (assume 25% bright stock) 19950 19594 356 0 37 38 Panel D: Activity-based information 39 Activity Actual amount Level Driver Actual volume 40 Area supplies 3,600 Unit-level Trace to areas 41 Setting up melting pots 2,000 Batc Setups 100 42 Purchasing 2,000 Batch Purchase orders 80 43 Filling orders 6,300 Customer # orders 500 44 Designing labels 4,000 Customer Design hours 4 45 Facility-related costs 1,900 Facility Square feet 3000 46 Total overhead costs 19,800 47 48 Panel E: Area-specific activity 49 Pouring Inspection Foiling Labeling Total 50 Purchase orders 60 16 80 51 Square feet 750 750 750 750 3000 52 53 Panel F: Job-specific activity volumes 54 Activity Job 1 Job 2 Job 3 Job 4 55 Setup: 10 5 N 56 Design hours 57 Labor hours 58 Pouring 22 11 1 1.75 59 Inspection 45 23 0.5 0.75 60 Foiling 58 29 1.2 1.75 61 Labeling 58 29 1.2 1.75 62 63 64 65 66 67 68 69 70 Table 1 Table 2 Table 3 Table 4 Table 5 Job costing ABC Process costing + + 100% Ready IT Accessibility: Good to goAutoSave O A A C 5 . 9 ... X: A325 dream choc tables su24 @ No Label Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Developer Comments Share Insert v Calibri (Body) v 11 AA = = ab Wrap Text v General V v Z AY . O. Ex Delete v Paste BIUV VAv E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Sensitivity Analyze Formatting as Table Styles Format v Filter Select Data G29 fx A B D E G H 1 K L M N 0 P Q R S U V W Job Costing and simple OH Job 1 Job 2 Job 3 Job 4 1.25 oz 1.25 oz 3.0 oz 3.25 oz Organic Non-organic Non-organic Organic Sales 8 DM costs DL costs 10 OH costs 1 Total job costs 12 13 Profit margin 14 Profit margin % 15 16 Cost per bar 17 Profit margin per bar 18 Profit margin % 19 20 Enter your answers in the red box. 21 All numbers inside the red box are referenced from other tables or calculated using Excel formulas. 22 You can add additional information outside the red box. 23 Numbers appearing in additional information are referenced from other tables or calcualted using Excel formulas. 24 25 26 27 28 29 30 31 32 34 35 36 37 38 39 40 41 D Table 1 Table 2 Table 3 Table 4 Table 5 Job costing ABC Process costing + Ready iJ Accessibility: Good to go + 100%AutoSave O A A C 5 . 9 ... X: A325 dream choc tables su24 @ No Label Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Developer Comments Share Insert v Calibri (Body) v 11 AA = a Wrap Text v General V v Z AY . O. Ex Delete v Analyze Paste BIUV vAv E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Sensitivity Formatting as Table Styles Format v Filter Select Data A10 X V fx OH costs A B D E G H 1 K L M N P Q R S U V W Job 1 Job 2 Job 3 Job 4 1.25 oz 1.25 oz 3.0 oz 3.25 oz Organic Non-organic Non-organic Organic Sale 7 8 DM costs 9 DL costs 10 OH costs 11 Total job costs 12 13 Profit margin 14 Profit margin % 15 16 Cost per bar 7 Profit margin per bar 8 Profit margin % 19 20 Enter your answers in the red box 21 All numbers inside the red box are referenced from other tables or calculated using Excel formulas. 22 You can add additional information outside the red box. 23 Numbers appearing in additional information are referenced from other tables or calcualted using Excel formulas. 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Table 1 Table 2 Table 3 Table 4 Table 5 Job costing ABC Process costing + Ready iJ Accessibility: Good to go + 100%AutoSave O A A C 5 . 9 ... X: A325 dream choc tables su24 @ No Label Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Developer Comments Share AA a Wrap Text v Accounting Insert v Calibri (Body) v 11 AY . O. Ex Delete v V v Z Paste BIUV DVAv E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Sensitivity Analyze Formatting as Table Styles Format v Filter Select Data B10 4 fx A B D E G H 1 K L M N 0 P Q R S U V W Process Costing and simple OH Job Job 2 Job 3 Job 4 1.25 0 1.25 oz 3.0 oz 3.25 oz Organic Non-organic Non-organic Organic Sales 8 DM costs 9 Conversion costs 10 Total job costs 11 12 Profit margin 13 Profit margin % 14 15 Cost per bar 16 Profit margin per bar 7 Profit margin % 18 19 Enter your answers in the red box. 20 All numbers inside the red box are referenced from other tables or calculated using Excel formulas. 21 You can add additional information outside the red box. 22 Numbers appearing in additional information are referenced from other tables or calcualted using Excel formulas. 23 24 25 26 27 28 29 30 31 34 35 36 37 38 39 Table 1 Table 2 Table 3 Table 4 Table 5 Job costing ABC Process costing + Ready Accessibility: Good to go + 100%