question archive You are an accountant at a large hospital, reporting to the controller
You are an accountant at a large hospital, reporting to the controller
Subject:AccountingPrice: Bought3
You are an accountant at a large hospital, reporting to the controller. The hospital maintains a perpetual inventory system. Hospital policy requires that a physical count of inventory be performed at year-end and that the Inventory account balance must be adjusted to match the physical count results. Following the results of the year end physical inventory count, the controller has provided this Excel file containing the physical inventory count results and asked you to use the data contained in this file to prepare three manual journal entries adjusting the Inventory account balance:
1) Remove consignments tracked in the perpetual system from the Inventory balance.
2) Use the Inventory Over/Short account to adjust the Inventory balance to match the count results. 3) Use the Loss Due to Decline in Value account to adjust the Inventory balance for known spoilage. At last tally, the hospital’s supply room held 2,146 unique items. Upon ordering new inventory, a supply room staff member increases the quantity in the perpetual inventory system and computes a new average cost per unit consistent with the weighted average cost flow assumption.
?IF: A staff member orders new inventory and updates the quantity/cost in the perpetual system THEN: The perpetual system automatically calculates and posts this entry:
DR: Inventory XXXX
CR: Accounts Payable XXXX
? IF: A staff member distributes inventory and updates the quantity in the perpetual system THEN: The perpetual system automatically calculates and posts this entry:
DR: Cost of Goods Sold XXXX CR: Inventory XXXX
As a result of these automatic entries, the hospital’s Inventory account balance in the general ledger is kept in line with the perpetual inventory system balance during the year. On December 31, 2018, the Inventory account had a balance of $678,798.28, which matches the perpetual system. In preparation for the physical count, the supply room supervisor locked staff members out of the perpetual system so that they could no longer update the system after 5:00 pm of December 31, 2018. Staff members conducted the physical count on the evening of December 31, beginning at 5:00 pm and finishing at 8:00 pm. Following completion of the count, the supply room supervisor input the new inventory quantities resulting from the physical count into the perpetual inventory system. He also made the following notes, but he did not adjust any of the inventory quantities for the situations described.
- REMINDER FOR CONTROLLER’S OFFICE: All kits in our inventory are held on consignment per the hospital’s longstanding agreement with our vendor Magnus Labs. As part of this agreement, we keep records of kit quantities and pricing in our perpetual inventory system for tracking and accountability purposes, but we do not actually own any of this inventory. Both our perpetual system records and our physical count results will include quantities and pricing for these kits.
- During the count, we had to send out the following inventory for patients in urgent need. Because we had locked the perpetual system, we could not decrease quantities in the system.
Instructions:
1) Prepare the journal entry to remove consignments tracked in the perpetual inventory system from the December 31, 2018 Inventory account balance. a. Using Excel’s Text to Columns, split up the Descr column so that you can identify “kits”. HINT: Excel’s Text to Columns eliminates the original data you are splitting up. To avoid losing the original data, copy and paste the original column before you run the Text to Columns. To copy the Descr column, left click the entire column, hold CTRL and hit the C key, right click the next column, and select “Insert Copied Cells”. HINT: To run Text to Columns:
? Left click to select the new Descr column.
? Click the Data tab, and then click “Text to Columns
? Select “Delimited”, and then click “Next”.
? Under “Delimiters”, click “Space”, and then click “Next”.
? Examine the “Data Preview”. You should see lines between each word. Click “Finish”. If there are existing columns to the right of the column that you are splitting up, you will get a message that Excel is going to overwrite the existing columns to the right. Usually, you do not want this. This is easy to avoid by adding new blank columns to the right of your data. HINT: You will only want the first word, so you can delete the other columns that you created. You will want to label your new columns as Type. b. Using Excel’s Pivot Table feature, identify the dollar amount of “kit” inventory.
? Check that Excel has highlighted all of the data in the worksheet. Also, check that Excel will place the Pivot Table report in a “New Worksheet”. It should do both automatically. If Excel has done both of these things, click “Next”.
? Excel will create a new tab. If you click in the cells with the Pivot Table, you will see, on the right side of the screen, a list of all of your data fields.
? Drag “Type” into the Rows area and “Pre Inv Value” into the Values area. Check that Excel is summing the Values. It should do so automatically.
? Find “kit” in the new table. Highlight this row as support for your journal entry.
? Rename the new tab “Support for JE 1”. c. Create a new tab called “JEs.” Prepare the journal entry to remove consignments from the December 31, 2018 Inventory balance. HINT: To remove consignments, you should reverse the automatic entry or entries that originally established the consignments as Inventory in the general ledger. 2) Prepare the journal entry to adjust the December 31, 2018 Inventory balance to match the count results.
a. Adjust the count quantities and values for the five items shipped on the day of the count. While the items were not yet in the supply room, they should have been included in the count because they belong to the hospital as of December 31, 2018. HINT: Insert new columns to the right of the “Count Qty” and “Count Value” columns. Name these new columns “Adj Count Qty” and “Adj Count Value”. To insert columns, you can use the following keyboard shortcuts: Ctrl + Space to select a column and then Ctrl + Shift + Plus key to insert a new column to the left of the selected column.
HINT: To find the specific rows you need to adjust, use Excel’s find feature (Ctrl + F).
b. Adjust the pre inventory quantities and values for the five items distributed during the count. Supply room staff ordinarily would have decreased the quantity of these items in the perpetual system upon distribution, but they could not do so because the supervisor locked the system during the count. HINT: Insert new columns to the right of the “Pre Inv Qty” and “Pre Inv Value” columns. Name these new columns “Adj Pre Inv Qty” and “Adj Pre Inv Value”. Highlight the rows that you adjust here in yellow.
c. On the “JEs” tab, prepare the journal entry to adjust the December 31, 2018 Inventory balance using the Inventory Over/Short account. Note that distributions during the account should be accounted for as Cost of Goods Sold instead of Inventory Over/Short. HINT: You should insert one final adjusted column in the raw data: “Adj Difference”. HINT: It will be easier to work with your data if you sort it. Click on Data, then Sort. Since you highlighted the rows you adjusted, you can easily sort your data so that these rows appear first. To do this, sort by cell color. You can add additional levels to a sort by clicking the “Add Level” button. If you sort the Descr column alphabetically, it will be easier to exclude kits when you calculate the Over/Short entry
HINT: You can now calculate the Inventory Over/Short entry using Excel’s SUM function. Make sure to sum the “Adj Difference” where necessary. Also, make sure to exclude “kits” from your sum because the hospital does not own this inventory.
- Prepare the journal entry to decrease December 31, 2018 Inventory for known spoilage
Inventory balance using the Loss due to Decline in Value account. 4) You’re finished! To check your work, you should be able to tie out the new Inventory balance that you calculate after your journal entries to the total adjusted count value in the Raw Data.
Questions:
When preparing the journal entry to remove consignment, how much was the credit to inventory? A)$913.62
B)$9,177.33
C)$4013.52
D)$44,948.62
In the Support for JE 3, how much was the spoilage in aisle 9?
A) $27,352.38
B)$ 95,036.47
C)$686102.07
D)$4,013.52
In support for JE1, what was the value for the mattress?
A)$16,469.51
B)$27,352.38
C)$44,948.62
D)$95,036.47
How much was amount of Over/Short adjustment needed to match the count results?
A)$9,177.33
B)$913.62
C) $4,013.52
D) $44,948.62
