The excel charts are the following... Direct Materials, Direct Labor, Purchase order and Sample Job cost sheet

Percy Roberts, the company controller, carefully reviewed the paperwork on his desk regarding the new specialty chips division. The executive team had decided to start the line by producing three specialty chips most often requested by Roberts' loyal customers. These new chips will have the distinctive size and quality of the traditional Roberts Chips, but they will feature additional spices and herbs. Overall, the new plans and flavors were going well, but he still wasn't ready to support the new division. "Good morning, Percy," Jennifer, the new accounting clerk, said from the doorway. "What's going on?" Percy shook his head. "More work, as always. We've got to start getting ready for the new division. Production starts in just ten days, and we have to be ready to track costs, revenues, budgets... The list just goes on and on." He smiled at his new clerk. "I'm sure glad that you're around to help me deal with all this fun stuff." Jennifer sighed in mock frustration. "I just knew I should have gone into public accounting." "What, and miss all this? You've got to be kidding!" Percy laughed. "All joking aside, I have some important things for you to do for me today. First, let me give you a little background." "As you know from your work these past couple of weeks, our traditional chips division has two production departments: Cooking and Finishing. The Cooking department performs the basic manufacturing of the chips, including washing and cutting the potatoes, adding the sea salt, and cooking the chips in avocado oil." "Once the chips have been removed from the oil and cooled, they move to the Finishing department, where the chips are inspected for size and burns and then packaged for shipping." "Right," Jennifer agreed. "They walked us through all of this during employee orientation. The Finishing department also performs comprehensive quality control checks by randomly taste testing chips from each batch of potatoes." "Good," Percy said. "I'm glad that you were listening. Now we are trying to start up a new division producing specialty chips." Jennifer nodded. "The good news is that our development team was careful to plan the new chips in a way that will allow us to fit them into the Cooking and Finishing departments." "The Cooking department will perform most of the additional steps. Since we only use fresh ingredients it will take some additional cutting and grinding of the new herbs and spices to manufacture the specialty chips. The three specialty flavors will be Habanero, Garlic & Pepper, and Basil and Chive." "So," Jennifer said, as Percy finished his explanation, "we will need to keep track of the extra ingredients for the specialty chips. We'll also need to keep track of bagging materials for the Finishing department." "Right, all of that will increase our DM expenses. In addition, we will be tracking a bunch of extra DL, since all these new chip flavors are more time intensive than the current chips." "Which employees will be involved?" "Well, not the apprentices. They'll continue to perform the basic functions, such as washing and cutting. Chip Makers will be a little more involved, since they will be cutting and/or grinding the new ingredients. However, all of the real specialty work will be done by the Chip Masters, our most senior employees."Jennifer started to laugh. "I always thought that "Chip Masters" sounded like a title given to a great poker player. Percy laughed too. "Yeah, I guess it does when you think about it. Al Roberts, the company founder, actually chose those titles. Back then, things were much more formal and titles were very important in the workplace. He wanted to make it very clear which employees had earned his trust. The names seem silly now, but we keep the traditions. Besides, those who earn the Chip Master title are very proud of it. When we mentioned to some of them that we were thinking of renaming their position it almost came to blows." "So, the "Chip Masters like my dad," Jennifer said lightly, "are really supervisors, right? Their wages should be fixed costs, and I think I classified them as variable." "They are variable costs; we've tested them numerous times to make sure, but the expenses still follow the variable pattern, probably because they know that the work goes up and down based on demand. Anyway, they spend most of their time actually doing production with the Chip Makers and apprentices. They teach by doing." "Anyway," Percy continued. "The executive team has decided that only the Chip Masters will be allowed to work on the specialty chips. So, they will be doing all the measuring and applying of the new ingredients, as well as the cooking the specialty chips. Over time, we hope that we can standardize the processes enough so that Chip Makers, and perhaps apprentices, will be able to help prepare the specialty chips." Jennifer nodded. "Okay, so what does all of this have to do with me?" "I'm so glad you asked!" Percy responded. "I need you to run some numbers for me." Percy handed Jennifer a purchase order. (Located in Excel file) "This morning we got our first order for a batch of specialty chips. The production departments are using this order as a trial run form the new processes, and I think we need to do the same thing. I want you to calculate the cost of the DM and DL on this job, but when I want you to calculate it for the other two types of specialty chips as well for comparison." "Okay," Jennifer said enthusiastically and headed out of the office. She turned around after a couple of steps. "Um, before I start that, do you mind if I check my thought process with you? I don't want to get done and then find out I was doing it wrong." Percy smiled. "That's a very good idea. Let's go over this sample Job Cost Sheet together." (Document) "This sample Job Cost Sheet is for 200 normal 12 oz. bags of chips. From what I remember, there are three direct material costs in the Cooking department. They all vary with the amount of chips in a bag." "And the potatoes, sea salt, and oil all add up to $0.11 per oz. For a 12 oz. bag that gives you 1.32 per bag." "Okay," Jennifer continued. "Then we add the $0.13 per bag for the bag, adhesive, and labels, pus $0.40 per packaging box for every batch of 20."Percy nodded. "So, what will be the total direct materials for the 200 bags?" He asked, covering the Job Cost Sheet. "No peeking," she teased Jennifer whipped out her calculator. "Give me a minute," she said punching buttons, "$294?" "Exactly!" Percy beamed at Jennifer and turned the Job Cost Sheet back facing her. "In the cost spreadsheet you'll find extra materials for the specialty candles immediately below the standard direct materials. Remember that, for each specialty flavor, we are adding additional materials" Jennifer nodded. "Sounds good. Now, labor is a little different, isn't it? It doesn't vary by number of ounces because cooking a 2 oz. and cooking 12 oz. takes virtually the same amount of time.' "Right, so can you tell me how much labor will cost for our hypothetical job?" asked Percy Jennifer started thinking out loud. "Well, apprentices are only used in the Cooking department and so the cost per batch would be 0.15 hours times $10 per hour, or 1.50 per batch. Chip Makers are used in both departments, but they get paid the same rate, so we could combine their time into .12 hours and multiply that times the rate of $15 per hour, or 1.80 per batch. We can do the same thing for Chip Masters. They work a total of .06 hours in the two departments. If we take that amount times the $20 per hour wage, we get $1.20 per batch. That's a total of 4.50 per batch times 10 batches for a total of $45." Percy nodded in approval. "That's the right math. Remember, though, when you fill out a Job Cost Sheet, you need to put down each line item separately. I think you're ready to figure out the direct materials and labor for our specialty candles. We'll worry about the overhead later." "Percy," Jennifer typed in her email. "Here are the numbers you requested about the DM and DL costs for the first job, as well as the alternative totals based on the other two types of specialty chips you asked me to calculate." She hit send and leaned back in her chair. She felt good about what she had accomplished, especially since she felt that she wasn't really an Excel guru yet. Now, she could move on to some of the financial accounting tasks for which she felt better prepared. A couple hours later, she paused and checked her email. "Good work," Percy's reply began. "Now you need to apply MOH so that we have a total estimated cost of the job. Once you have those numbers, I'd like you to calculate per-unit cost as well so that we'll know if we are actually going to make money on this new line or not. You can refer to the Job Cost Sheet that we used to refresh your memory on how to apply overhead, but those POHRs are not correct; they are old. I've put a memo in your mailbox that has my total overhead estimates for the coming year and estimates from the Cooking and Finishing departments for the drivers they think they will be using (Below the Questions). "After our discussion yesterday, I decided that perhaps we should have a different POHR for each department, so I changed the sample Job Cost Sheet to reflect that. Remember that applying overhead on a department level is the same as using a single plant-wide POHR, except that you apply it in chunks, once for each department." "Good luck, and keep me advised of your progress."Jennifer sighed. It didn't look like she was going to get a chance to make any journal entries after all. Percy looked up as Jennifer walked into his office. "Did you get finished?" Jennifer nodded. "I have all the numbers. I put them on the forms you gave me, but wouldn't it work better on a spreadsheet?" "Yes, but I haven't had time to create a template yet. Do you think you can do that?" Percy asked. "Well," Jennifer replied. "I can certainly try." "That's a good attitude," Percy replied. "Just make sure that your template is easy to read so that I can show it to the rest of the management team." 1. Use the information from Percy's spreadsheet and the purchase order for the first specialty chip job to calculate the following amounts. a. Total DM costs for the job. b. Total DL costs for the job. 2. Calculate the costs of the DM and DL for the other two types of specialtychips. a. The total DM + DL for the same number of Garlic & Pepper chips: b. The total DM + DL for the same number of Basil & Chive chips: 3. Referring to the estimated overhead report calculate the POHR forthe: a. Cooking department b. Finishing department 4. Calculate the amount of MOH applied to the special order from the: a. Cooking department b. Finishing department 5. Calculate the following: a. The total cost for the special order. b. The per-unit cost for the special order. 6. Calculate the following: a. The total gross profit (margin) for the special order b. The per-unit gross profit (margin) for this orderMemo To: Jennifer Haire From: Percy Roberts Date: September 9, 2016 Re: Estimated amounts for next year. Here are my estimates of the number of drivers each department will use next year along with an estimated total overhead for each department. Please use these figures to calculate a POHR for each department, and then use that rate to apply overhead. Department Drivers Used Total Overhead Cooking 25000 DL hours 310,000.00 Finishing 125000 batches 675,000.00A B C D E F G H 1 Cooking Department: Finishing Department Per Ounce of 2 Input Chips Input Per Bag Per Batch (20 bags) 3 Potatoes S 0.08 Bag S 0.10 4 Sea Salt 0.01 Labels S 0.02 5 Oil 0.02 Adhesive S 0.01 6 Packaging Boxes S 0.40 7 8 Additional Costs for Specialty Chips 9 Habanero Pepper 0.06 Habanero only 10 Garlic S 0.05 G &P only 11 Black Pepper S 0.01 G & P only 12 Basil 0.04 B & C only 13 Chives 0.07 B & C only 14 15 16 17 18A B C D E F G 1 Cooking Department: Finishing Department Hours per Hours per Batch (20 Batch (20 2 Labor Type bags) Wage per hour Labor Type bags) Wage per hour 3 Apprentice 0.15 5 10.00 Apprentice NA NA 4 Chip Maker 0.06 15.00 Chip Maker 0.06 5 15.00 5 Chip Master 0.04 5 20.00 Chip Master 0.02 5 20.00 Additional Chip Master time for Specialty Chips Habanero 0.15 5 20.00 G & P 0.40 5 20.00 9 B & C 0.35 5 20.00 10 11 12 13Purchase Order Sundaram's Grocery P.O# 345 Sept. 11, 2020 Two Intermediate Parkway High Point, NC 27268 Vendor: Roberts Chips Company Ship To: Sundaram's Grocery 318 Cost Avenue One Intermediate Parkway Greensboro, NC 27411 High Point, NC 27268 Shipping Method Shipping Terms Delivery Date Ups FOB Destination Sept. 22, 2020 QTY Item # Description Job Unit Price Line Total 100 HA - 6 6 oz. bag of Habanero Chips H-1 3.55 S 355.00 Subtotal 355.00Client Name: Sample Purchase Order Number: Sample Item produced and quantity: 200 12 oz. bags of Normal Sea Salt chips Direct Materials Item Quantity Per-Unit Cost Extended Cost Potatoes 12 0zx 200 0.08 5 192.00 Sea Salt 12 0z x 200 0.01 24.00 oil 12 0z x 200 0.02 5 48.00 Bags 200 0.1 20.00 Labels 200 0.02 5 4.00 Adhesive 200 0.01 5 2.00 Packaging Boxes 10 0.4 5 4.00 Total Direct Material Cost 5 294.00 Direct Labor Item Total Hours Hourly Rate Extended Cost Apprentice .15 x 10 10 5 15.00 Chip Maker (.06 +.06) x 10 15 5 18.00 Chip Master (.04 +.02) x 10 20 12.00 Total Direct Labor Cost 5 45.00 Applied Manufacturing Overhead Department (Driver) Quantity POHR Extended Cost Cooking (DLH) 1.5 +.6+.4 =2.5 20 50.00 Finishing (Batches) 10 5 80.00 Total Applied MOH 130.00 Total Cost 5 169.00 Per-Unit Cost 5 2.35