Using Data Analytics to Analyze Data DA9.2 Data analytics can be used to determine costs using activity-based costing. Problem Creations is a wholesaler that bakes and decorates special occasion multi-tiered cakes, the majority of which are wedding cakes. The supervisor, Clara Cook, is questioning if using direct labor cost provides an accurate allocation base for overhead assigned to each cake. Creations uses job costing for each job which usually consists of six to nine cakes. Cakes produced for each job are delivered to local retail locations that have ordered the cakes for their respective customers. After meeting with the cost accountant, Clara identified seven activities used in the production of cakes. She has provided estimates of the annual activities along with the actual usage of activities for the jobs completed during the busiest quarter of the year presented here. Annual estimated activity Number of cakes 1,531 Direct labor cost Average direct labor cost per cake $ 452,000 $ 295.23 Activities, drivers, annual estimated activity, and estimated annual overhead cost Activities Driver Annual Estimated Activity Est. Annual Overhead Measuring # of pounds of ingredients 10,074 pounds $ 9,563 Mixing batter Baking # of mixer hours 365 mixer hours 24,578 # of oven hours 986 oven hours 21,238 Mixing icing # of cups 4,599 cups 13,845 Decorating Quality control Cleaning # of decorating hours 6,570 decorating hours 15,456 # of supervision hours # of labor hours 219 supervision hours 6,475 913 labor hours 12,271 Activity during the second quarter Mixing Mixing Quality Cakes Job Measuring Batter Baking Icing Decorating Control Cleaning Produced 407 29.31 0.96 2.65 10.50 16.30 0.63 2.35 5 408 30.60 0.98 2.68 10.70 16.90 0.61 2.40 06 6 409 28.20 1.01 2.71 10.90 17.50 0.59 2.66 4 410 27.30 1.00 2.66 11.10 18.10 0.63 2.33 411 31.20 0.97 2.77 11.30 20.10 0.64 2.73 412 30.90 0.95 2.80 11.50 16.25 0.56 2.68 413 29.20 0.98 2.74 11.70 16.40 0.57 2.58 414 27.30 1.01 2.81 11.90 20.10 0.58 2.59 415 27.15 1.02 2.66 10.55 20.15 0.61 2.63 416 26.99 0.99 2.68 14.52 16.99 0.59 2.72 417 26.54 1.02 2.79 12.50 17.45 0.63 2.33 418 31.23 0.96 2.78 13.90 17.65 0.61 2.38 419 30.20 0.97 2.66 14.10 16.33 0.65 2.45 420 30.30 0.96 2.67 10.50 20.10 0.64 2.43 46544554456 421 28.20 0.99 2.75 10.80 19.65 0.55 2.41 4 422 28.62 0.98 2.76 14.51 19.30 0.56 2.49 423 31.20 0.97 2.74 10.90 19.20 0.57 2.55 66 424 31.50 1.00 2.66 10.92 16.97 0.58 2.59 4 425 29.08 0.95 2.66 12.69 17.17 0.64 2.15 426 29.39 0.98 2.68 11.38 18.81 0.56 2.14 427 27.22 1.01 2.79 11.16 19.79 0.57 2.19 428 31.55 1.02 2.78 12.88 17.31 0.58 2.21 429 29.86 0.99 2.66 11.29 18.33 0.61 2.06 430 29.11 1.02 2.68 12.83 19.93 0.59 2.27 431 31.34 0.95 2.79 13.75 18.17 0.63 2.22 432 29.52 0.98 2.78 12.17 18.09 0.59 2.36 433 29.28 1.01 2.66 11.18 18.55 0.63 2.49 434 31.05 1.02 2.67 11.51 19.11 0.61 2.13 435 31.17 0.99 2.66 12.57 18.13 0.65 2.12 436 31.84 1.02 2.68 13.19 18.88 0.64 2.37 437 28.99 0.96 2.79 14.22 19.53 0.55 2.21 438 29.25 0.97 2.78 14.68 19.97 0.57 2.01 439 31.21 0.95 2.66 11.75 17.34 0.58 2.09 45644545465446N 7 440 29.47 0.98 2.67 12.08 19.19 0.64 2.02 441 29.51 1.01 2.75 13.46 17.62 0.56 2.11 442 29.86 0.95 2.66 14.63 17.35 0.57 2.44 443 31.39 0.98 2.68 14.68 19.70 0.58 2.03 444 27.98 1.01 2.79 11.10 17.77 0.61 2.06 445 28.00 1.02 2.78 12.33 18.98 0.57 2.08 446 27.83 0.99 2.66 14.36 18.68 0.58 2.41 447 31.87 1.02 2.68 11.99 18.43 0.61 2.48 64546546 448 29.75 0.96 2.79 11.07 18.13 0.59 2.49 449 31.20 0.97 2.78 13.21 18.08 0.63 2.03 7 60 450 30.37 0.96 2.66 11.42 17.16 0.59 2.38 451 28.43 0.95 2.67 12.64 19.03 0.57 2.17 452 29.69 0.98 2.75 13.97 17.33 0.58 2.26 665 453 29.76 0.95 2.66 14.60 18.71 0.64 2.17 4 454 28.67 0.98 2.68 13.89 19.53 0.56 2.35 455 28.54 1.01 2.79 11.48 17.86 0.57 2.31 456 29.45 1.02 2.78 12.11 20.00 0.58 2.49 457 30.85 0.99 2.66 11.70 17.21 0.61 2.46 458 29.34 1.02 2.67 13.73 19.00 0.63 2.09 459 28.51 0.96 2.75 14.63 19.26 0.61 2.33 460 29.14 0.97 2.66 13.93 19.14 0.59 2.37 461 27.16 0.96 2.68 12.58 17.67 0.63 2.08 462 31.51 0.95 2.66 11.56 19.80 0.64 2.06 463 27.80 0.98 2.68 12.24 17.97 0.56 2.07 464 27.44 1.01 2.79 13.51 17.88 0.57 2.33 465 28.53 1.02 2.78 14.87 18.50 0.58 2.46 466 29.84 0.99 2.66 14.89 17.13 0.61 2.29 6454455654447 7 468 31.58 0.96 2.75 14.47 17.53 0.63 2.04 469 30.68 0.97 2.76 11.50 19.55 0.63 2.39 470 30.71 0.95 2.74 13.97 18.55 0.59 2.26 471 28.84 0.98 2.66 12.69 17.53 0.57 2.16 472 31.48 1.01 2.68 14.40 18.52 0.58 2.30 473 28.59 1.02 2.79 11.36 17.01 0.64 2.18 474 28.64 0.95 2.78 13.41 18.06 0.56 2.07 7966744 475 27.25 0.98 2.66 11.54 18.60 0.57 2.04 476 29.10 1.01 2.68 11.34 19.94 0.58 2.24 477 27.21 1.02 2.79 14.43 18.14 0.59 2.44 478 29.34 0.99 2.78 13.97 17.38 0.63 2.47 656 N 7 479 27.74 1.02 2.66 14.59 19.15 0.59 2.36 480 27.17 0.96 2.67 14.77 19.68 0.63 2.36 481 30.51 0.97 2.75 13.38 18.15 0.61 2.26 482 27.03 0.96 2.76 12.18 19.13 0.65 2.10 483 30.02 0.95 2.74 12.77 17.98 0.64 2.28 484 27.01 0.98 2.66 13.90 17.19 0.55 2.07 485 28.78 1.01 2.67 12.52 18.17 0.59 2.33 486 31.57 1.02 2.75 11.70 17.98 0.63 2.40 487 29.15 0.99 2.66 11.13 19.31 0.59 2.17 488 29.46 0.95 2.68 14.79 19.52 0.63 2.31 489 27.17 0.98 2.79 14.48 17.95 0.61 2.25 490 30.07 1.01 2.78 12.86 18.03 0.65 2.45 491 30.66 1.02 2.66 11.65 17.29 0.64 2.26 492 31.75 0.99 2.67 11.35 18.29 0.55 2.23 493 30.35 1.02 2.75 11.70 19.87 0.61 2.39 494 27.28 0.96 2.76 13.07 18.81 0.63 2.23 495 31.44 0.97 2.74 13.68 17.14 0.61 2.44 496 28.35 0.96 2.66 11.73 17.83 0.59 2.06 497 27.71 0.99 2.72 12.31 17.84 0.63 2.07 5775757N 45555566564 7 7 7 Instructions: There are four parts to this problem. Use Excel to perform the following: a. Determine the average daily usage of each activity. b. Determine the overhead allocation rates for each cost pool using activity-based costing. Determine the overhead to be assigned to job 412 using activity-based costing. d. How can Clara use this information to reduce its total overhead cost? Explain.