Background: 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 an Excel file containing the physical inventory count results and asked you to use the data contained in thils file to prepare three manual journal entries adjusting the Inventory account balance 1) Remove consignments tracked in the perpetual system from the lnventory balance. 2) Use the inventory Qver/Short account to adjust the Inventory bulance to match the count results. 3) Use the toss Due to Decline in Value account to adjust the Inventory balance for known spollage. At last tally, the hospital's supply room held 2,146 unique items. Upon ordering new inventory, a supply room staff member incresses the quantity in the perpetual inventory system and computes a new averaze cost per unit consistent with the weighted average cost flow assumption. Upon distribution of inventory (to the operating room, etc.), a staff member decreases the quantity in the perpetual inventory system. The perpetual inventory system is configured to automatically cakulate and post journal entries to the hospital's general ledger based on the following logic: - 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 CR: Accounts Payable x000x0x - If: A staff member distributes inventery and updates the quantity in the perpetual system THEN: The perpetual system automatically calculates and posts this entry: DA:CostofGoodssoldCHInventoryInverox As a result of these avtomatic entries, the hospitar's inventory account balance in the general ledger is kept in line with the perpetual inventory sysem balance during the yeat, On December 11, 2018, the inventory account had a balance of 5678,79878 , which matches the perpetual iyitem. In preparation for the physical count, the supply roam supervisor locked saff members out of the perpetual syatem so that they could no longer update the shitem after 5.00 pm of December 31, 2018 Staff members conducted the phyical count on the evenine of December 31, beginning at 5.00 pm and finithing at 8.00pm. Following completion of the count, the supply room supervisor input the new inventery quantities rewulting from the phyical count into the perpetual inventory sytem. He also made the following notes, but he did not adjuat any of the imventory quantities for the situations described. 1) 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. 2) During the day on December 31 before starting the count, we ordered the following inventory FOB shipping point and increased the perpetual system counts. Our supplier shipped us these items immediately, but because we get next-day delivery, we did not physically have these items during the count and we did not count them. 3) 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. 4) Following the count, at about 8:30pm two staff members decided to celebrate successful completion of the count by smoking cigarettes despite posted warnings. Unfortunately the smoke set off the fire prevention sprinklers and all inventory in aisle nine was completely soaked. I have reprimanded these staff members, but this inventory is a total loss. 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 Copled 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. - 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. 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). HINT: An easy way to keep track of the adjusted rows is to highlight them. Highlight the rows that you adjust here in green. To highlight rows, you can use the following keyboard shortcuts: Shift + Space to select a row and then Alt, then H, then H again to bring up the highlights menu, which you can select a color from using the arrow keys. 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 aty" 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". 14 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. 3) Prepare the journal entry to decrease December 31,2018 Inventory for known spollage. a. Using Excel's Pivot Table feature, identify the spoiled inventory in aisle nine. HINT: Create a Pivot Table using what you have already learned. You will want to sum Count Values in "AIS" 09, Once complete, rename the new tab "Support for JE 3". b. On the "JEs" tab, prepare the journal entry to adjust the December 31,2018 Inventory balance using the Loss due to Decline in Value account. 4) You're finishedl 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. 5) The supply room supervisor tells you that he recently leamed that a local crime ring traffics in stolen medical supplies. Recommend two ways that you could use this data set to identify specific items that appear to be at a higher risk of theft