Chapter 17 Using Excel P17-40 Using Excel to calculate a predetermined overhead allocation rate, journalize and post manufacturing journal entries, and adjust for overallocated or underallocated overhead. The company manufactures three-wheeled bikes for adults. The company allocates manufacturing overhead based on machine hours. Cedar River expects to incur $250,000 of manufacturing overhead costs, and to use 10,000 machine hours during fiscal year 2018. Cedar River reported the following inventories at May 31, 2018: Raw Materials Inventory $25,000 $18,000 $43,000 Work-in-Process Inventory Finished Goods Inventory During June, 2018, Cedar River actually used 1,100 machine hours. Requirements: 1. Compute the predetermined overhead allocation rate for fiscal year 2018. Use the blue shaded areas for inputs. 2. Use Excel to journalize the journal entries in the general journal. The account titles are available when you click the down-arrow. Use the Increase Indent button on the Home tab to indent items. 3. Enter the beginning balances for the inventories. Assume the rest of the accounts have beginning balances of zero Post the journal entries to T-accounts. The balances in the T-accounts are automatically updated. 4. Adjust the manufacturing overhead account. Prepare the journal entry. . b. Post to T-accounts. 5. List the ending balances for Raw Materials Inventory, Work-in-Process Inventory, Finished Goods Inventory, and Cost of Goods Sold. Excel Skills: Enter numbers or text into cells. 1. Use Excel's data validation to select account names for the journal entries 2 Use the Increase Indent button to indent accounts to be credited for the journal entries. 3. Requirement 1 Compute the predetermined overhead allocation rate for fiscal year 2018 Predetermined Total estimated overhead costs /Total estimated machine hours overhead allocation rate Total estimated overhead costs Total estimated machine hours Predetermined overhead allocation rate Requirement 2 Journalize the journal entries in the general journal. Account/Explanation Debit Credit Date (a) Purchased materials on account, $140,000 (b) Used direct materials, $110,000 (c) Manufacturing wages incurred totaled $80,000; 94% was direct labor and 6% was indirect labor. (d) Used indirect materials, $5,500 (e) Incurred other manufacturing overhead on account, $22,000 (f) Allocated manufacturing overhead for June 2018. Machine hours: 1,100 during June. OH Rate: $25/hour. (g) $220,000 of trikes were completed during June. (h) Sold $450,000 of trikes on account. Requirement 3 Post the journal entries. Accounts Payable Sales Revenue Accounts Receivable 0 Wages Payable Raw Materials Inventory Manufacturing Overhead Bal Work-in-Process Inventory Bal Cost of Goods Sold 0 Finished Goods Inventory Bal Requirement 4 Adjust the manufacturing overhead 1. Prepare the journal entry. 2. Post the journal entry to the T-accounts worksheet. Account/Explanation Debit Credit Date (i) Adjusted manufacturing overhead. Requirement 5 Ending balances. Account Raw Materials Inventory Work-in-Process Inventory Finished Goods Inventory Ending balance Cost of Goods Sold