The goal of this assignment is to design a spreadsheet to compute product costs for Healthy Harvest Bakery. The bakery uses a FIFO process costing system. Healthy Harvest Bakery makes breads using only natural ingredients and no added fats. The bakery makes four varieties of breads: white, wheat, sourdough and raisin. Your task is to determine the cost of raisin bread made during January. The raisin bread is made in two departments: baking and packaging. In baking, the ingredients are mixed and the bread is baked. Baked loaves are then moved to packaging, where they cool before placing them in a plastic wrapper. The basic ingredients (flour, yeast, etc.) are added to the bread at the start of production in the baking department. Raisins are added to the bread in the baking department after the basic ingredients have been mixed; this occurs when the baking department is one-fourth complete with their work. The plastic wrapper is added at the end of the production process in the packaging department, after the bread has cooled. Conversion costs are incurred evenly throughout the production process in both departments. Your workbook should consist of sheets: 1. Input Sheet 2. Baking Department Cost of Production 3. Packaging Department Cost of Production The spreadsheet should be designed so that information is added into the Input Sheet only. All information in Baking and Packaging should be either references or formulas. The input section of your spreadsheet should contain the following data: Beginning WIP Inventory Baking Department Units Completed Ending WIP Inventory Cost Data: # units % complete 120 30% 5,000 80 15% Beginning WIP Inventory Balance * Basic Ingredients used this month Raisins used this month $ 58 990 680 Conversion Costs incurred this month 2,400 *Beginning WIP Costs consist of $24 for basic ingredients, $16 for raisins, and $18 for conversion costs. Packaging Department Beginning WIP Inventory Ending WIP Inventory #units 75 % complete 80% 50 50% Cost Data: Beginning WIP Inventory Balance ** $ 67 Wrappers used this month 150 Conversion Costs incurred this month 500 **Beginning WIP Costs consist of $62 for costs transferred in from Baking (the prior depart.) and $5 for conversion costs. Your spreadsheet should produce a Cost of Production Report for each department (baking & packaging). A sample Cost of Production Report can be found on page 206 of the text. When formatting your spreadsheet, format all numbers representing dollar amounts for two decimals; format all numbers representing units to zero decimal places. Make sure that your spreadsheet is flexible enough to work for any amount of units at any stage of completion. (For example, be sure the spreadsheet still works if the units in the baking department's beginning inventory are 20% complete). Hint: this will require IF functions. 1234567 A 1 Banking Department 2 4 Unit Data 5 Beginning WIP Inventory 6 Units Completed 7 Ending WIP Inventory 8 9 10 B C D E F Units 120 5000 80 % Complete 30% 100% 15% 11 Cost Data 12 13 14 15 16 17 Basic ingredients Raisens Conversion Cost $ Begin WCost $ Current $24 $990 $16 $680 $18 $2,400 $58 $4,070 18 Packaging Department 19 20 21 Unit Data Units % Complete 22 Beginning WIP Inventory 75 80% 23 Units Completed 50 50% 24 Ending WIP Inventory 222 25 26 27 28 Cost Data 29 30 2323 31 Transferred in Wrappers Conversion Cost $ Begin W Cost $ Current $62 $150 $5 $500 $67 $650 34 35 < > Inputs Basic WA Basic FIFO + 2 3 A UNITS 4 Units charged to production 5 Inventory in process, Beginning 6 Started in Production During Month 7 Total units accounted for by department 00 8 9 Units to be assigned cost 10 Completed and Transferred Out 11 End WIP -60% mat, 40% con 12 Total units to be assigned cost 13 14 15 16 17 Cost 18 Unit Cost (Step 4) 19 Beginning Work in Process 20 Cost Added During Month 21 Total Cost to Account For 22 Total Equivalent Units 23 Cost Per Equivalent Unit 24 25 Assign Cost (Step 5) 26 Completed and Transferred Out F Equivalent Units (Step 3) B C E Whole Units (Step 1) Direct Materials 5,080 Whole Units (Step 2) 5,080 Conversion 4,976 G Cost Direct Materials Total Conversion Cost 0.482 $ 0.821 27 Inventory in process, End of month $ 28 Total Cost Accounted for 5.79 $ $ 21.76 4,128.00 29 A B D FIFO Process Cost 1 2 2 3 UNITS E F Equivalent Units (Step 3) Whole Units (Step 1) Direct Materials Conversion 5 4 Units charged to production Inventory in process, Beginning 6 Started in Production During Month 7 Total units accounted for by department 5,075 8 Whole Units 9 Units to be assigned cost (Step 2) 10 Beg WIP - M 90%, Con 75% 11 Started and Completed 12 End WIP - M 60%, C 40% 13 Total units to be assigned cost 5,075 456 14 15 16 17 18 19 Unit Cost (Step 4) Cost 20 Beginning Work in Process 21 Cost Added During Month 22 Total Cost to Account For 22 23 Total Equivalent Units 24 Cost Per Equivalent Unit < Direct Materials Cost 4,990 Conversion Total Cost $ 67.00 0.100 $ 0.951 Inputs Basic WA Basic FIFO + 24 Cost Per Equivalent Unit 25 Cost allocated to completed or partially 26 completed units (Step 5) 27 28 29 30 31 32 33 34 Inventory in process, Beginning - balance To complete inventory in process Started and completed in month Transferred out during month Inventory in process, End of month Total cost assigned to department 35 36 .... GA 0.100 $ 0.951 $ 67.00 $ 41.06 $ 2.51 $ 43.57 $ 4,823.24