a CCT 4112 DA CT Exercise (1) Compatibility Made es Mailings Review View A EEEE 21 10 Data Analysis - Critical Thinking Project Acade ABCadi A B Cebdi AaBhCoD I am 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. I am 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 5626,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 dala. 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 , then age in months is December 31, 2015 less Delivery Date) / 30). If usage is not 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 summanze 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 efficit format that condenses data Comma separated value (CSV) files are a form of ACSI 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: Focus Share Inventory * File Edit View Insert Format Data Tools Add-ons HelpL 7 100% - $ % 0.09 123 - Default (C. - 12 General Ledger Amount from Information Given General Ledger Amount from thformation Given Row Labels London OTTAWA QUEBEC TORONTO Grand Total Sum of Quantity on Hal count of Average Cost for Sum of Quantity * A 938 81332 87309 378 45375 214954 Recommended obsolete inventory Depot Name Quantity on Hand OTTAWA QUEBEC TORONTO Grand Total Average Cost for Item Q uantity Average Cos 9011 63.687 902 58.24 11048 20961 170.332 48.4 Sum of Quantity * Avg 610 Obsolete Amount from Managers POV Row Labels Sum of Quantity on Hand Sum of Avg Cost of Item OTTAWA $29.96 QUEBEC 1486 $256.13 TORONTO 131 $68.18 Grand Total 2227 $354.27 Inventory 12-31-2015 Pivot a CCT 4112 DA CT Exercise (1) Compatibility Made es Mailings Review View A EEEE 21 10 Data Analysis - Critical Thinking Project Acade ABCadi A B Cebdi AaBhCoD I am 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. I am 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 5626,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 dala. 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 , then age in months is December 31, 2015 less Delivery Date) / 30). If usage is not 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 summanze 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 efficit format that condenses data Comma separated value (CSV) files are a form of ACSI 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: Focus Share Inventory * File Edit View Insert Format Data Tools Add-ons HelpL 7 100% - $ % 0.09 123 - Default (C. - 12 General Ledger Amount from Information Given General Ledger Amount from thformation Given Row Labels London OTTAWA QUEBEC TORONTO Grand Total Sum of Quantity on Hal count of Average Cost for Sum of Quantity * A 938 81332 87309 378 45375 214954 Recommended obsolete inventory Depot Name Quantity on Hand OTTAWA QUEBEC TORONTO Grand Total Average Cost for Item Q uantity Average Cos 9011 63.687 902 58.24 11048 20961 170.332 48.4 Sum of Quantity * Avg 610 Obsolete Amount from Managers POV Row Labels Sum of Quantity on Hand Sum of Avg Cost of Item OTTAWA $29.96 QUEBEC 1486 $256.13 TORONTO 131 $68.18 Grand Total 2227 $354.27 Inventory 12-31-2015 Pivot