Question

Project Excel Template”. The file includes blank templates for Job # Cost Sheer, Job #2 Cost Sheet, General Journal. T-Accounts (General Ledger), Schedule of Cost of Goods Manufactured, Cost of goods sold and Income Statement.

You work for Thunderduck Custom Tables Inc. This is the first month of operations. The company designs and manufactures specialty tables. Each table is specially customized for the customer. This month, you have been asked to develop and manufacture two new tables for customers. You will design and build the tables. This is a no nail, no screw, and no glue manufacturing ( no indirect materials used). You will be keeping track of the costs incurred to manufacture the tables using Job #1 Cost Sheet and Job #2 Cost Sheet. The cost of the direct materials that can be used to manufacture the table are as follows. These cost are on a per unit basis.  Table Top $2,800.00   Table Leg $1,050.00   Drawer   $480.00  The company uses a job order costing system and applies manufacturing overhead to jobs based on direct labor hours.    The company estimates that there will be 12 direct labor hours worked during the month.       The estimated manufacturing overhead cost for the month is:   a.Factory supervisor salary per month $4,000.00  b.Rent for the factory per month $1,400.00  c.Depreciation of factory equipment per month   $600.00   Total Estimated manufacturing overhead $6,000.00       What is the predetermined manufacturing overhead rate?   

  Step 2The first order you received was to manufacture a table using a table top and four legs. This is your Job #1. Step 3The customer that has ordered Job #2, wants a table that is the same as Job #1, but wants to also add a drawer to the table. Step 4The following is a list of transactions that need to be recorded for the company for activity in the month of December. Record those in the "General Journal" tab of the excel file using the proper format. Please use the following accounts: Accounts Receivables, Raw materials, Work in process, Finished goods, Accumulated depreciation, Accounts payable, Salaries and wages payable, Sales revenue, Manufacturing overhead, Cost of goods sold, Salaries and wages expense, Advertising expenses, and Depreciation expense.1-DecRaw Materials purchased on account, $28,000.5-DecAll Raw Materials needed for Job #1 were requisitioned from the material storage for use during the month. Assume all materials are direct. (After you journalize this entry please enter the information into Job #1 Cost Sheet)10-DecThe following employee costs were incurred but not paid during the month: There are three assembly employees that spend 2 hours each, $30 per hour to make the table for Job #1. (After you journalize this entry please enter the information into Job #1 Cost Sheet)Salary for supervisor of the factory $4,500.Administrative Salary $2,000.15-DecAll Raw Materials needed for Job #2 were requisitioned from the material storage for use during the month. Assume all materials are direct. (After you journalize this entry please enter the information into Job #2 Cost Sheet)16-DecRent for the month of December for the factory building incurred but not paid $1,400.17-DecAdvertising costs incurred but not paid for the month was $1,200.20-DecDepreciation for the month of December was recorded on equipment was $750 ($150 for equipment used in the factory and the remainder for equipment used in selling and administrative activities).22-DecManufacturing overhead cost was applied based on direct labor hours to Job #1 based on the POHR determined on the "Job Cost Sheet". (After you journalize this entry please enter the information into Job #1 Cost Sheet)26-DecJob #1 was completed and transferred to Finished Goods during the month.28-DecThe completed table from Job #1 was sold on account to the customer for $33,000 during the month. (Hint: Make sure to account for the cost of the table that was sold using the cost from the job cost sheet.)31-DecDirect labor cost incurred but not paid for three employees to start manufacturing Job #2. The employees only worked one hour each, three hours total, $30 per hour during the month and they did not complete their work on the job. (After you journalize this entry please enter the information into Job #2 Cost Sheet)31-DecManufacturing overhead cost was applied based on direct labor hours to Job #2 based on the POHR. Only three direct labor hours were worked on Job #2 during the month. (After you journalize this entry please enter the information into Job #2 Cost Sheet)31-DecAny underapplied or overapplied overhead for the month was closed out to Cost of Goods Sold.Step 5Post the journal entries that you recorded on the "General Journal" tab to the "T-accounts" tab. This is the company's first month of business, so there will not be any beginning balances. Compute the balance for each T-account after all of the entries have been posted.Step 6Prepare a Schedule of Cost of Goods Manufactured and a Schedule of Cost of Goods Sold on the "Schedule of COGM and COGS" tab for Job #1 and Job #2 that were worked on during the month by the company. Make sure to follow the format noted in your book (pg. 109). (Hint: This is the company's first month of operations and therefore the beginning balances will be zero.)Step 7Prepare an Income Statement for the month using the Traditional Format on the "Income Statement" tab.  Step 8Answer the additional questions below      Check Figure: Cost of Goods Manufactured= $10,180, Net operating income=$17,470       What is the ending balance for raw materials? What is the ending balance for work in process? What is the ending balance for finished goods? What is the actual manufacturing overhead cost incurred during December before adjustment? What is the total applied manufacturing overhead cost during December before adjustment? What is the unadjusted cost of goods sold? Was the manufacturing overhead for the month of December overapplied/underapplied ? What is the amount of Manufacturing overhead overapplied/underapplied? What is the adjusted cost of goods sold? What is gross margin? What is the total prime cost for Job#1? What is the total conversion cost for job #1? What is the total product cost for job#1? What was the period cost incurred for the month of December?   What is the total variable cost incurred for Job #1(assume that all selling and administrative cost and all manufacturing overhead costs are fixed.)? What is the contribution margin for Job #1 (assume that all selling and administrative cost and all manufacturing overhead costs are fixed.)? What would be the actual (not applied) total fixed manufacturing overhead cost incurred for the company for the month if the order in Job #1 is for five tables instead of one table assuming this cost is with in the relevant range?

please add the excel template for each: Job # cost sheet, job #2 cost sheet, General Journal, T-Accounts (General Ledger), Schedule of cost of Goods Manufacturer, Cost of goods sold and Income Statement

Answer 1

Step 1 Determination of the predetermined Manufacturing overhead rate Total estimated manufacturing Predetermined overhead cost $6,000 per direct $500 Manufacturing Overhead labour hour Total estimated direct labor Rate = 12 hours Step 2 The first order you received was to manufacture a table using a table top and four legs. This is Job Cost Sheet 5-Dec 26-Dec Job Number Date Initiated Date Completed Item Direct Materials Direct Labor Manufacturing Overhead Amount Hours Rate Amount Hours Rate Amount $30.00 $ $500 $ 2,800 2 hours* 3 employees- 6 hours Table Top 180 6 3,000 Table Leg $ 4,200 (1050*4) $ $ $ Total 7,000 180 3,000 Cost Summary $ Direct Materials 7,000 Direct Labor Manufacturing Overhead $ 180 $ 3,000 Total Product Cost 10,180 $ Unit Product Cost 10,180

Step 3 The customer that has ordered Job #2, wants a table that is the same as Job #1, but wants to also add a drawer to the table. :- Job Cost Sheet 15-Dec Job Number Date Initiated 2 Date Completed Item Manufacturing Overhead Direct Materials Direct Labor Amount Hours Rate Amount Hours Rate Amount $500 $ 2,800 1 hour* 3 employees- 3 hours $30.00 $ $ Table Top 90 1,500 Table Leg $ 4,200 (1050*4 $ Drawer 480 $ $ $ Total 1,500 7,480 90 Cost Summary $ Direct Materials 7,480 Direct Labor 90 Manufacturing Overhead Total Product Cost $ 1,500 $ 9,070 Unit Product Cost 9,070

Step 4 Journal entries General Journal Debit Credit Sr. No. Description $28,000 1-Dec Raw Materials Dr. To Accounts payable $28,000 (Being Raw materials purchased on account) 5-Dec Work in Process Dr. 7,000 $7,000 To Raw Materials (Being Raw materials issued for use in Job #1) 10-Dec Work in Process (2 hours* 3 employees*$30 per hour) Dr. 180 Manufacturing Overheads Salaries and Wages Expense $4,500 $2,000 Dr. Dr. To Salaries and wages payable 6,680 (Being employee cost incurred) 15-Dec Work in Process $ Dr. 7,480 $7,480 To Raw Materials (Being Raw materials issued for use in Job #2) 16-Dec Manufacturing Overheads $1,400 Dr. To Rent Payable $1,400 (Being Rent for the factory building incurred) 17-Dec Advertising Expenses $1,200 Dr. $1,200 To Advertising Expenses Payable (Being Advertising Expenses incurred) 20-Dec Manufacturing Overheads Depreciation expense $150 Dr. $600 Dr. $750 To Accumulated depreciation (Being depreciation charged on factory and office equipments) 22-Dec Work in Process Dr. 3,000 $ 3,000 To Manufacturing Overheads (Being Manufacturing Overheads charged to job #1 @ $500 per direct labour) (500* 6) 26-Dec Finished Goods $ Dr. 10,180 To Work in Process $10,180 (Being job #1 completed) $ 28-Dec Cost of goods sold Dr. 10,180 $10,180 To Finished Goods (Being goods sold transferred) 28-Dec Account Receivable $33,000 Dr. $33,000 To Sales Revenue (Being goods sold on account) 31-Dec Work in Process (1 hour* 3 employees*$30 per hour) $ Dr. 90 To Salaries and wages payable $ 90 (Being direct labor cost incurred) 31-Dec Work in Process Dr. 1,500 To Manufacturing Overheads (Being Manufacturing Overheads charged to job #2 @ $500 per direct labour) (500* 3) $1,500 $ 31-Dec Cost of goods sold Dr. 1,550 To Manufacturing Overheads (Being underapplied overheads charged) S 1,550

Step 5 T-Accounts Raw materials To Balance b/d $ $ By Work in Process 14,480 $ 28,000 To Accounts payable By Balance c/d 13,520 $28,000 28,000 Work In Process $ $ To Balance b/d By Finished Goods 10,180 $14,480 $ To Raw Materials By Balance c/d 9,070 To Salaries and wages payable $ 270 $ 4,500 To Manufacturing Overheads $19,250 19,250 Finished Goods By Cost of goods sold To Balance b/d $ $ 10,180 $ By Balance c/d $10,180 To Work in Process $10,180 10,180 Manufacturing Overheads To Salaries and wages payable $ 4,500 4,500 By Work in Process To Rent Payable $1,400 $ By Cost of Goods Sold 1,550 To Accumulated depreciation $ 150 By Balance c/d $6,050 6,050 Cost of Goods Sold $ 10,180 $ 1,550 By Balance c/d To Finished Goods 11,730 To Manufacturing Overheads $ $11,730 11,730

Accounts Receivables 33,000 To Sales Revenue By Balance c/d 33,000 $33,000 33,000 Sales Revenue $ 33,000 To Balance c/d By Accounts Receivables 33,000 $33,000 33,000 Accumulated depreciation To Balance c/d $ By Manufacturing Overheads By Selling & Administration Overheads 750 150 $ 600 $ 750 750 Rent Payable $ 1,400 $ 1,400 To Balance c/d By Manufacturing Overheads 1,400 1,400 Advertising Expenses Payable $ To Balance c/d Advertising Expenses 1,200 1,200 1,200 1,200 Accounts Payable $ 28,000 $ To Balance c/d By Raw Materials 28,000 $28,000 $ 28,000 Salaries and wages payable $ 6,770 To Balance c/d By Work in PrOcess 270 By Manufacturing Overheads 4,500 By Salaries and Wages Expense 2,000 $6,770 6,770 Salaries and wages Expenses To Salaries and wages payable $2,000 By Balance c/d 2,000 2,000 2,000 Advertising Expenses To Advertising Expenses Payable By Balance c/d $ 1,200 1,200 $1,200 1,200

Depreciation Expense To Accumulated depreciation $ $ By Balance c/d 600 600 600 600 Trial Balance Debit Credit $ Accounts Receivables 33,000 Raw materials $ 13,520 Work in process $ 9,070 Finished goods $ $ Accumulated depreciation 750 Accounts payable Rent payable Advertising Expenses Payable Salaries and wages payable Sales revenue 28,000 $ 1,400 $ 1,200 $ 6,770 $ 33,000 Manufacturing overhead $ Cost of goods sold 11,730 Salaries and wages expense $ 2,000 Advertising expenses Depreciation expense $ 1,200 $ 600 $ Total 71.120 71,120 Manufacturing Overheads are underapplied for the month $ 6,050 Actual manufacturing overheads incurred $4,500 Applied manufacturing overheads (3000+1500) $ 1,550 Underapplied overheads

Step 6 Schedule of Cost of Goods Manufactured Thunderduck Custom Tables Inc. Schedule of Cost of Goods Manufactured For the Month Ended December 31, 20XX Particulars $14,480 Raw material used Direct Labor $ 270 $ 4,500 Manufacturing Overheads Add: Total manufacuring costs 19,250 $ Add: Beginning inventory of Work in Process Total Cost of Work in process 19,250 Ending inventory of Work in Process Less: 9,070 Cost of Goods manufactured $10,180 Thunderduck Custom Tables Inc. Schedule of Cost of Goods Sold For the Month Ended December 31, 20XX Beginning inventory of Finished Goods $ Cost of Goods manufactured $ 10,180 Add: $ Cost of Goods available for sale 10,180 $ Ending inventory of Finished Goods Less: $10,180 Unadjusted Cost of Goods sold 1,550 Underaplied overheads charged Add: $ 11,730 Adjusted Cost of Goods sold Step 7 Income Statement for the month Thunderduck Custom Tables Inc. Income Statement For the Month Ended December 31, 20XX $ Sales 33,000 $ Cost of Goods sold Less: 11,730 $ Gross margin 21,270 Salaries and wages Expenses Advertising Expenses $ Less: 2,000 $ Less: 1,200 $ Less Depreciation Expense 600 Net operating income 17,470

Step 8 Additional Questions What is the ending balance for raw materials? (See Raw materials T-account above) 13,520 What is the ending balance for work in process? (See Work in process T-account above) 2 9,070 $ What is the ending balance for finished goods? (See Finished Goods T-account above) 3 What is the actual manufacturing overhead cost incurred during December before adjustment? (See 4 6,050 What is the total applied manufacturing overhead cost during December before adjustment? (3000+1500) 4,500 5 What is the unadjusted cost of goods sold? (See Schedule of Cost of Goods sold) 6 10,180 Was the manufacturing overhead for the month of December overapplied/underapplied? Underapplied 7 What is the amount of Manufacturing overhead overapplied/underapplied? (See above working) 1,550 8 What is the adjusted cost of goods sold? (See Schedule of Cost of Goods sold) 11,730 9 What is gross margin? ((Gross Margin/Sales)*100) 64.45% 10 What is the total prime cost for Job#1? (Direct materials Cost+ Direct labor cost) 11 7,180 What is the total conversion cost for job #1? (Direct labor cost+ Manufacturing overhead cost) 12 3,180 13 What is the total product cost for job#1? (See cost summary in Job cost sheet) 10,180 What was the period cost incurred for the month of December? (Salaries of supervisor +Rent) $ 14 5,900 What is the total variable cost incurred for Job # 1( assume that all selling and administrative cost and all manufacturing overhead costs are fixed.)? (Direct materials Cost+ Direct labor cost) 15 7,180 What is the contribution margin for Job #1 (assume that all selling and administrative cost and all manufacturing 16 overhead costs are fixed.)? (Sales- variable cost as per 15 q.) 25,820 What would be the actual (not applied) total fixed manufacturing overhead cost incurred for the company for the month if the order in Job #1 is for five tables instead of one table assuming this cost is with in the relevant range? 17 (Fixed manufacturing cost will remain the same irrespective of no. of units produced) (See Manufacturing $ 6,050 overheads T-account total above)

Feel free to ask any clarification, if required. Kindly provide feedback by thumbs up, if satisfied. It will be highly appreciated.
Thank you.