ACCT 4112 Intermediate Accounting II

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).

1

ACCT 4112 Intermediate Accounting II

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 during class the appropriate definitions of

each of the 16 columns of data