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 are allowed to 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:

Baking Department?# units % complete

Beginning WIP Inventory 120 30%

Units Completed 5,000

Ending WIP Inventory 80 15%

Cost Data:

Beginning WIP Inventory Balance * $ 58

Basic Ingredients used this month 990

Raisins used this month 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 # units % complete

Beginning WIP Inventory 75 80%

Ending WIP Inventory 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). 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.

Remember, a spreadsheet is most helpful when it can be reused in the future. Design this project so that Healthy Harvest Bakery could use the same spreadsheet next month to compute product costs by simply changing the input section.

?

?

UNITS Units charged to production Inventory in process, Beginning Started in Production During Month Total units accounted for by department Units to be assigned cost Beg WIP Started and Completed End WIP Total units to be assigned cost Cost Unit Cost (Step 4) Beginning Work in Process Cost Added During Month Total Cost to Account For Total Equivalent Units Cost Per Equivalent Unit Cost allocated to completed or partially completed units (Step 5) 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 Healthy Harvest Bakery - FIFO Baking January Whole Units (Step 1) 5,080 Whole Units (Step 2) Basic Ingredients Raisins Conversion Example of IF: =IF('Input Sheet'!D6>0.25,0,B17) =B12*(1-'Input Sheet'!D7) Example: Basic Ingredients Raisins Conversion #DIV/0! #DIV/0! #DIV/0! $ $ $ $ $ $ $ Total Cost #DIV/0! - - UNITS Units charged to production Inventory in process, Beginning Started in Production During Month Total units accounted for by department Units to be assigned cost Beg WIP Started and Completed End WIP Total units to be assigned cost Unit Cost (Step 4) Beginning Work in Process Cost Added During Month Total Cost to Account For Total Equivalent Units Cost Per Equivalent Unit Cost Cost allocated to completed or partially completed units (Step 5) 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 Healthy Harvest Bakery - FIFO Packaging January Whole Units (Step 1) Whole Units (Step 2) Merge & Center Equivalent Units (Step 3) Tranfered In Tranfered In #DIV/0! Wrapper Conversion Cost Wrapper Conversion #DIV/0! #DIV/0! $ $ $ $ $ $ $ Total Cost - #DIV/0! - - Baking Department Unit Data Beginning WIP Inventory Units Completed Ending WIP Inventory Cost Data Basic Ingredients Raisins Conversion Packaging Department Unit Data Beginning WIP Inventory Units Completed Ending WIP Inventory Cost Data Transferred In Wrappers Conversion Healthy Harvest Bakery - FIFO Prcess Inputs January Units 120 5,000 80 $990 $680 $2,400 $4,070 Units 75 50 Cost $ Begin WIP $62 $5 $67 % Complete 30% 15% $0 % Complete 80% 50% Cost $ Current $150 $500 $650