Hi. I need help filling out the provided excel templates. It is all part of one problem. Please fill out the answers in excel, copying the provided format. The 4 pages are labeled as the following:

1. Main Processing page
2. Further breakdown of the Raw Material and Factory Labor T-Accounts
3. General Journal
4. ABC Breakdown of Factory Overhead

Now I will provide the instructions for the project.

Jacksons Vacuum company makes two types of vacuums: Basic and Advanced. The factory is set up into 3 working departments. Both vacuums utilize process #1 to assemble the basic components. Those units destined as basic will transition to department #2 to be finalized. The advanced vacuums will skip department #2 and go directly to the advanced processing department #3.

There are 4 types of raw material. #1-3 are all direct materials and #4 is an indirect material #1 #2 #3 #4 Beg. Raw Material 13,000 6,000 45,000 7,000 Purchased on account $193,343 $76,897 $395,223 $54,532 End. Raw materials $5,668 $14,413 $26,985 $5,310

Requested materials Dept. #1 $106,675 in raw material #1 was requested, $40,000 for #2, $195,000 for #3 Dept. #2 $20,000 in raw material #1 was requested, $15,484 for #2, $46,000 for #3 Dept. #3 $74,000 in raw material #1 was requested, $13,000 for #2, $172,238 for #3 Indirect materials (#4) used this year amounted to $56,222

There are 3 type of factory workers. Assembly line, area supervisor, and repairman/maintenance.

1. Assembly line workers:

a. 12 workers each work 1,900 hours per year.

b. 6 workers work in process 1 and earn $12/hour.

c. The other 6 workers work equally in departments 2&3 and make $16/hour.

2. In total, there are 2 supervisors each paid $72,786 each and 2 repairman/maintenance each paid $40,000.

Other factory overhead consists of the following: 1. Property taxes paid for the year - $35,000 2. Utilities paid for the year - $148,897 3. Six large machines. Each with a value of $100,000 depreciated over 10 years. Two machines are used in each department.

Factory overhead is allocated using the ABC method and based on machine setups and machine hours WIP #1 WIP #2 WIP #3

Total units transferred 56,000 37,000 19,000 Total machine hours 53,355 31,400 21,000 Total machine setups 4,200 4,744 8,590 Total Labor hours 11,400 4,560 6,840

Factory Overhead using machine hours = Fact. Dep. & Indirect materials total Factory overhead using machine set ups = Property taxes, utilities and indirect labor total

Department #1 had 3,500 units in beginning WIP that were 50% complete as to conversion costs (totaling $12,000) and 100% complete for materials (totaling 42,000). 57,003 units were started into production. Transferred out 56,000 units which included 37,000 basic to WIP #2 and 19,000 advanced to WIP #3. The remaining ending inventory was 100% complete for materials and 40% for conversion costs.

Department #2 had 2,000 units in beginning WIP that were 50% complete as to conversion costs (totaling $3,000) and 100% complete for materials (totaling 10,000). 37,000 units were started into production. Transferred out 35,000 units. The remaining ending inventory was 100% complete for materials and 64%for conversion costs.

Department #3 had 1,000 units in beginning WIP that were 50% complete as to conversion costs (totaling $4,000) and 100% complete for materials (totaling 13,000). 19,000 units were started into production. Transferred out 19,200 units. The remaining ending inventory was 100% complete for materials and 70% for conversion costs.

Basic and advanced will split the WIP #1 based on the number of units of each type. Thus if there were $100 in WIP #1 and 20 total units (14 basic and 6 advanced), Each unit would absorb $5. Therefore the basic would account for $70 of WIP #1

Beginning finished goods for basic vacuums totaled 6,500 units at a total cost of $142,384 Beginning finished goods for advanced vacuums totaled 2,300 units at a total costs of $98,555

Total sales for the basic vacuum were 38,033 units @ $55 each Total sales for the advanced vacuum were 19,800 units @ $87each

Instructions

A. Prepare the following journal entries

1. Raw material purchases

2. Distribution of raw materials to each WIP or Factory Overhead

3. Accumulation of wages owed

4. Distribution of wages to each WIP or Factory Overhead

5. Accumulation of factory overhead

6. Allocation of Factory overhead using ABC per WIP

7. Transition of WIP 1 goods to either WIP 2 or 3

8. Transition of WIP 2 goods to Finished Goods

9. Transition of WIP 3 goods to Finished Goods

10. Sale of Basic vacuums

11. Sale of Advanced vacuums

B. What is the Gross Profit of each separately and combined?

THE ANSWER MUST COMPLETE THE FOLLOWING EXCEL SHEETS BY USING THE INSTRUCTIONS PROVIDED ABOVE. Thank you.

beg. units started ending units transferred cC \% complete WIP-Step 2 date cr. Beg DM Beg CC all new DM new Labor NewFO DM Equivalentunits CC Equivalentunits \begin{tabular}{|} \hline \\ \hline \\ \hline \end{tabular} units transferred \#2 costs transferred to \#2 units transferred \#3 costs transferred to #3 Factory OH \begin{tabular}{l|l|l|} \multicolumn{1}{|c|}{ date } & dr. & \multicolumn{1}{c|}{\( \mathrm{cr}} \). \\ \cline { 2 - 3 } Ind. RM & & \\ Ind. FL & & \\ \cline { 3 - 3 } Other FO & & \\ \hline \hline WIP\#1 & & \\ WIPH2 & & \\ WIP\#3 & & \\ bal & & \end{tabular} WIP-Step 3 date \begin{tabular}{|l|l|} \hline dr. & cr. \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline \end{tabular} Finished Goods - B a\&/c date dr. beg B=B bal #10 bal beg. units transferred in AFS Sold Ending units Cost/unit sales/unit Beg CC all new DM new Labor Finished Goods - Adv date Beg. bal are bal beg. units started ending units transferred CC \% complete \begin{tabular}{|l|} \hline \\ \hline \\ \hline \\ \hline \end{tabular} DM Equivalentunits +2 CC Equivalentunits dr. cr. beg. units transferred in AFS Sold Ending units Cost/unit sale price DMcostunit CC costunit Totalunit units transferred total costs this dept total costs this dept. Total cost transferred Total cost transferred \begin{tabular}{|l|} \hline \\ \hline \\ \hline \\ \hline \end{tabular} Total costunit Total costs transferred Total costunit Sales Revenue date dr. cr. H10 mat bal COGS date dr. H10 bal Basic Gross Profit Advanced Gross Profit TotalGross Profit 1 2 3 Date B GENERAL JOURNAL **Skip lines between transaction dates Transaction Post. p. 1 H 6 (indent) credit account *skip a line between transactions* 7 8 10 11 12 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 45 46 47 48 50 50 52 Ref Debit Credit XXX XXX