Intermediate Accounting Assignment: Data Analysis - Critical Thinking Project

You are a senior accountant at ABC Corp. The controller indicates concern reported inventory levels appear dysfunctional.  Some inventory is too low with risk of stock outs. Some inventory is too high with risk of excessive carrying cost and risk of obsolescence that could impact reliability of financial reporting.  The controller wants you to analyze inventory data with a skeptical eye and summarize your recommendations in a professional business memorandum.  You are to make assumptions and inquiries of general management.  For purposes of this course your instructor is the aforementioned controller, therefore your memo will be addressed to your instructor.

In a succinct executive summary presented in your memo, address the following:

1. Whether or not itemized inventory records appear to reconcile to the general ledger total of $626,964, at December 31, 2015, specifying the number of products indicated in ending inventory. Call out unusual data, for example products with negative quantities or carrying values.

2. The financial accounting adjustment you recommend for obsolete inventory and the number of products impacted, relative to what management had previously identified.  By pivot table results show the recommended obsolete inventory accounting adjustment total by depot.

3. Indicate inventory carrying values and number of products, prior to the adjustment for obsolete inventory, which exceed the maximum or are less than the minimum inventory target levels.

4. Note significant questions arising from your analysis and further investigation you recommend.

Throughout remainder of the memo, discuss the thought process that drove your data analysis and provide support for all information surfaced in the executive summary.  Refer to and include exhibits to your memo allowing the reader to agree all summary information to underlying data.

Considerations and information you will find productive:

• Why is it important for accountants to analyze data supporting general ledger?
• Inventory considered by management to be obsolete is marked with a "Y". Discern if the data provides potential clues why the inventory items are in this state.
• Search for zero and negative product amounts in quantity and cost.
• Develop formula(s) addressing age or usage in terms of months of inventory. As illustration of a key assumption driver, if usage quantity over past year = 0, then age in months is [(December 31, 2015 less Delivery Date) / 30]. If usage is not = 0, then usage in months of inventory on hand is calculated as [Quantity / (Annual Usage / 12)].
• Make a key assumption based on evidence from data and from management indicating shelf life of ABC's products is only a few months. For example, anything in excess of six months' age or usage is likely to become obsolete. You may present a different logical assumption(s).
• Consider assumptions deriving strata such as less than 6 months age or usage reflects inventory may be okay, 6-12 months age or usage reflects 50% provision needed for obsolescence, and greater than 12 months usage reflects 100% provision for obsolescence is appropriate. You may also get more granular, though three strata are adequate to carry through with the exercise in this course. Seek opportunity to highlight further analyses within data provided that may be considered in the future which you did not complete.
• Develop pivot table within Excel to analyze data and summarize provision for obsolescence by location. London is not an approved depot.
• Does older inventory by location suggest a trend for further analysis?
• Consider minimum and maximum inventory levels deemed appropriate by management, writing formulas to illustrate and summarizing exceptions within the current data. As earlier noted, exceeding maximum levels may lead to obsolescence issues.

You are provided one inventory data file in ASCII delimited format, an efficient format that condenses data.  Comma separated value (CSV) files are a form of ACSII delimited where commas separate data fields.  Translate the file provided to Excel to conduct analysis.  Your instructor will outline to you the appropriate definitions of each of the 16 columns of data.

Definitions for the 16 Columns of Inventory Data

1. Product Code

2. Depot Name

3. Quantity on Hand

4. Average Cost for Item

5. Quantity * Average Cost

6. Management's Identification of Obsolete Inventory

7. Maximum Target for Inventory Level on Hand

8. Minimum Target for Inventory Level on Hand

9. Last Delivery Quantity

10. Last Delivery Order Number

11. Date of Last Delivery

12. Current Selling Price

13. Date Selling Price Effective

14. Previous Selling Price

15. Sales Quantity in Current Year

16. Unit Cost of Last Purchase

Attachment:- Inventory Data.rar