$1$ Chapter $4$: Applying Excel

$2$

$3$ Data

$4$ Beginning work in process inventory:

$5$

Units in process

$6$

Completion with respect to materials

$7$

Completion with respect to conversion

$200$

$55\%$

$30\%$

$8$

Costs in the beginning work in process inventory:

$9$

Materials cost

$10$

Conversion cost

$11$ Units started into production during the period

$$9\mathrm{.}600$

$$5\mathrm{.}575$

$5\mathrm{.}000$

$12$ Costs added to production during the period:

$13$ Materials cost

$14$ Conversion cost

$$368,600$

$$350\mathrm{.}900$

$15$ Ending work in process inventory:

$16$ Units in process

$17$ Completion with respect to materials

$18$ Completion with respect to conversion

$400$

$40\%$

$25\%$

$19$

$20$ Enter a formula into each of the cells marked with a $?$ below

$21$

$22$ Weighted$-$Average method:

$23$

$24$

Equivalent Units of Production

$25$

Materials

Conversion

$26$ Units transferred to the next department

$?$

$?$

$27$ Ending work in process inventory:

$28$ Materials

$29$ Conversion

$30$ Equivalent units of production

$?$

$31$

$32$

Costs per Equivalent Unit

$33$

Materials

Conversion

$34$ Cost of beginning work in process inventory

$35$ Costs added during the period

$?$

$?$

$?$

$?$

$36$ Total cost

$37$ Equivalent units of production

$38$ Cost per equivalent unit

$?$

$?$

$?$

$?$

$39$

$40$

$41$

Costs of Ending Work in Process Inventory and the Units Transferred Out

Materials

Conversion

$42$ Ending work in process inventory:

$43$ Equivalent units of production

$44$ Cost per equivalent unit

$45$ Cost of ending work in process inventory

$?$

$?$

$?$

$?$

$?$

$?$

$46$

$47$ Units completed and transferred out:

$48$

Units transferred to the next department

$49$

Cost per equivalent unit

$50$ Cost of units transferred out

$?$

$?$

$51$

$52$

Cost Reconciliation

$53$ Costs to be accounted for:

$54$

Cost of beginning work in process inventory

$55$ Costs added to production during the period

$56$

Total cost to be accounted for

$?$

$?$

$?$

$57$ Costs accounted for as follows:

$58$ Cost of ending work in process inventory

$59$

Cost of units transferred out

$?$

$60$

Total cost accounted for $?$

Required:

$1.$ Check your worksheet by changing the beginning work in process inventory to $100$ units, the units started into production during the period to $2,500$ units, and the units in ending work in process inventory to $200$ units, keeping all of the other data the same as in the original example. If your worksheet is operating properly, the cost per equivalent unit for materials should now be $$152\mathrm{.}50$ and the cost per equivalent unit for conversion should be $$145\mathrm{.}50.$ If you do not get these answers, find the errors in your worksheet and correct them.

How much is the total cost of the units transferred out? Did it change? Why or why not?

$2.$ Change all of the numbers in the Data area of your worksheet so that it looks like this:

Beginning work in process inventory:

Units in process

Completion with respect to materials

Completion with respect to conversion

Costs in the beginning work in process inventory:

Materials cost

Conversion cost

Units started into production during the period

Costs added during the period:

Materials cost

Conversion cost

Ending work in process inventory:

Units in process

Completion with respect to materials

Completion with respect to conversion

$200$

$100\%$

$20\%$

$$2,000$

$$800$

$1,800$

$$18,400$

$$38,765$

$100$

$100\%$

$30\%$

What is the cost of the units transferred out?

$3.$ What happens to the cost of the units transferred out in part $(2)$ above if the percentage completion with respect to conversion for the beginning inventory is changed from $20\%$ to $40\%$ and everything else remains the same? What happens to the cost per equivalent unit for conversion? Explain.

Complete spreadsheet please$\mathrm{.}1$

$\backslash$table$[[$Date$,$Description,PR$,$Debit,Credit$],[,,,,],[,,,,],[,,,,],[,,,,],[,,,,],[,,,,]]$

$2$

$\backslash$table$[[$Date$,$Description,PR$,$Debit,Credit$],[,,,,],[,,,,],[,,,,],[,,,,]]$

$3$ Unit Completed and Transferred

$4$ Equivalent Units of Production for Materials

$5$ Equivalent Units of Production for Conversion

$6$ Cost of Beginning Work$-$in$-$Process Inventory $+$ Costs Added for Materials

$7$ Cost of Beginning Work$-$in$-$Process Inventory $+$ Costs Added for Conversion

$8$ Cost per Equivalent Unit for Materials

$9$ Cost per Equivalent Unit for Conversion

$10$ Cost of Ending Work$-$in$-$Process Inventory for Materials

$11$ Cost of Ending Work$-$in$-$Process Inventory for Conversion

$12$ Cost of Materials Transferred to Finished Goods

$13$ Cost of Conversion Transferred to Finished Goods

$\backslash$table$[[$Date$,$Description,PR$,$Debit,Credit$],[,,,,],[,,,,],[,,,,],[,,,,]]$

$15$ Total Cost to Be Accounted For

Total Cost Accounted For

$\begin{array}{cc}\text{\$}& ?\\ \text{\$}& ?\\ \text{\$}& ?\\ \text{\$}& ?\\ \text{\$}& ?\\ \text{\$}& ?\\ \text{\$}& ?\\ \text{\$}& ?\\ \text{\$}& ?\\ \text{\$}& ?\\ ?& ?\\ ?& ?\\ ?& ?\end{array}$

Ready

Accessibility: Good to go