9-54. (LO 5,8) Data Analytics Using Excel: A Case in the Context of the Pharmaceutical Industry

This case will enable you to practice conducting planning and substantive analytical procedures for accounts in the revenue cycle. When analyzing the financial data, you may assume that the 2015 information is unaudited, while prior year data is audited.

Consider the following features of and trends in the pharmaceutical industry and for PharmaCorp specifically:

After a long period of industry dominance by companies in the United States, the United Kingdom, and Europe, these companies are facing increasing competition from companies domiciled in emerging economies, such as Brazil, India, and China.

There exists significant uncertainty in the market because of recent regulation covering health-care and government payouts for certain procedures and related pharmaceuticals.

Health-care policy makers and the government are increasingly mandating what physicians can prescribe to patients.

Health-care policy makers and the government are increasingly focusing on prevention regimes rather than treatment regimes, thereby leading to shifts in the demand for various pharmaceuticals.

The global pharmaceutical market is anticipated to grow by 5% to 7% in 2016 compared with a 4% to 5% growth rate in 2015, according to a leading industry analyst publication.

Beginning in 2014, PharmaCorp initiated and executed a significant company-wide cost reduction initiative aimed at improving manufacturing efficiency, cutting back on research and development expenses, and eliminating unnecessary corporate overhead.

PharmaCorp's policies for extending credit to customers has remained stable over the last three years. PharmaCorp's credit-granting policies are considered stringent within the industry, and analysts have sometimes criticized the company for this,contending that such policies have hindered the company's revenue growth relative to industry peers.

PharmaCorp's policies for extending credit to customers have remained stable over the last three years. PharmaCorp's credit granting policies are considered stringent within the industry, and analysts have sometimes criticized the company for this, contending that such policies have hindered the company's revenue growth relative to industry peers.

Two of PharmaCorp's popular pharmaceuticals, Selebrax and Vyvox, came off patent during the fourth quarter of FYE 2015. These pharmaceuticals now face competition in the generic drug portion of the overall industry market.

Part I: Planning Analytical Procedures

1.Step 1: Identify Suitable Analytical Procedures. Your audit senior has suggested that you should use the following ratios (on an overall financial statement level) for planning analytical procedures in the revenue cycle at PharmaCorp:

Gross margin: (revenues-cost of sales)/revenues

Turnover of receivables: (revenues/average accounts receivable); for ease of computation simply use ending accounts receivable

Receivables as a percentage of current assets and as a percentage of total assets: (accounts receivable/total current assets) and (accounts receivable/total assets)

Allowance for uncollectible accounts as a percentage of accounts receivable: (allowance/accounts receivable)

As part ofStep 1, identify any other relevant relationships or trend analyses that would be useful to consider as part of planning analytics. Explain your reasoning.

2.Step 2: Evaluate Reliability of Data Used to Develop Expectations. The audit team has determined that the data you will be using to develop expectations in the revenue cycle are reliable. Indicate the factors that the audit team likely considered in making that determination.

3.Step 3: Develop Expectations. CompleteStep 3of planning analytical procedures by developing expectations for relevant accounts in the revenue cycle and for the ratios from Part (a). Develop expectations by considering both historical trends of PharmaCorp, and also by considering features of and historical trends in the industry. Given that this is a planning analytical procedure, the expectations are not expected to have a high level of precision. You might indicate that you expect a ratio to increase, decrease, or stay the same, and possibly indicate the size of any expected increases or decreases, or the range of the expected ratio. PharmaCorp's financial information is on first tab of the Excel file, while the financial information for Novartell and AstraZoro is provided on the last two tabs of the Excel file.

4.Step 4 and Step 5: Define and Identify Significant Unexpected Differences. Refer to the guidance inChapter 7on overall materiality, performance materiality, and posting materiality. Apply those materiality guidelines toStep 4of planning analytical procedures in the revenue cycle for PharmaCorp, to define what is meant by a significant difference. Explain your reasoning. Also, comment on qualitative materiality considerations in this context. Now that you have determined what amount of difference would be considered significant, calculate the ratios identified inStep 1(and any additional ratios or trend analyses that you suggested), based on PharmaCorp's recorded financial statement amounts. Identify those ratios where there is a significant unexpected difference.

5.Step 6 and Step 7: Investigate Significant Unexpected Differences and Ensure Proper Documentation. CompleteStep 6of planning analytical procedures by describing accounts or relationships that you would investigate further through substantive audit procedures. Explain your reasoning. To completeStep 7, describe what information should be included in the auditor's workpapers.

Part II: Substantive Analytical Procedures

f. At the Excel file, you will see three tabs that you should review:PharmaCorp Segment Information, PharmaCorp Geographic Information, andPharmaCorp Other Revenue Info. These tabs provide excerpts from PharmaCorp's footnote disclosures regarding segment, geographic, and other revenue information. Read these disclosures and describe the various operating segments and geographic regions in which the company operates.

g. Which operating segments are most important to the company in terms of revenue generation? Which geographic regions are most important to the company? What are the three most important products produced by PharmaCorp? Comment on trends that you notice in revenue in each of these categories.

h. Explain the types of ratio analysis that you could conduct in substantive analytical procedures using the data provided in the segment, geographic, and other revenue information, for example, R&D expenses/revenues. How would the substantive analytics differ from the planning analytics? Comment on the trends and relationships that you believe are most relevant, and implications for further substantive testing.

20 21 LIABILITIES AND EQUITIES Shortterm borrowings, including current portion of long-term debt: ZOE52,449; 6,424 4,016 22 201456 23 Accounts payable 4,264 3,678 24 Dividends payable 1,734 1,?96 25 Income taxes payable 1,010 1,009 26 Accrued compensation and related items 2,046 2,120 27 Other current liabilities 13,141 15,066 28 Liabilities of discontinued operations 0 1,224 29 Total current liabilities 28,619 28,909 30 Long-term debt 31,035 345926 31 Pension benefit obligations 7,830 6,355 32 Postretirement benefit obligations 3,493 3,344 33 Noncurrent deferred tax liabilities 21,593 18,861 34 Other taxes payable 6,610 6,886 35 Other noncurrent liabilities 4,939 6,100 36 Total liabilities 104,120 105,381 37 A B D F Preferred stock, without par value, at stated value; 27 shares authorized; issued: 38 2015-967; 2014-1,112 39 45 Common stock, $0.05 par value; 12,000 shares authorized; issued: 2015-8,956; 39 2014-8,902 448 445 40 Additional paid-in capital 72,608 71,423 41 Employee benefit trusts -1 -3 42 Treasury stock, shares at cost: 2015-1,680; 2014-1,327 -40, 121 -31,801 43 Retained earnings 54,240 46,210 44 Accumulated other comprehensive loss -5,953 -4, 129 45 Total shareholders' equity 81,260 82,190 46 Equity attributable to noncontrolling interests 418 431 47 Total equity 81,678 82,621 48 49 Total liabilities and equity 185,798 188,002A B D E F 50 51 PharmaCorp 52 Consolidated Statements of Income (USD $) 53 In Millions, except Per Share data, unless otherwise specified Dec. 31, 2015 Dec. 31, 2014 54 Dec. 31, 2013 55 Revenues $58.986 $65.259 $65.165 56 Costs and expenses: 57 Cost of sales 11,334 14,076 14,788 58 Selling, informational and administrative expenses 16,616 18,832 18,973 59 Research and development expenses 7,870 9.074 9,483 60 Amortization of intangible assets 5,175 5.544 5,364 61 Restructuring charges and certain acquisition-related costs 1,880 2.930 3,145 62 Other deductions-net 4.031 2,499 3.941 63 Income from continuing operations before provision for taxes on income 12,080 12,304 9,471 64 Provision for taxes on income 2,562 3.909 1,153 65 Income from continuing operations 9,518 8,395 66 Discontinued operations: 8,318 67 Income/(loss) from discontinued operations-net of tax 297 350 -19 68 Gain/(loss) on sale of discontinued operations-net of tax 4,783 1,304 -11 69 Discontinued operations-net of tax 5,080 1,654 -30 70 Net income before allocation to noncontrolling interests 14,598 10.049 8,288 71 Less: Net income attributable to noncontrolling interests 28 40 31 72 Net income attributable to PharmaCorp Inc. $14,570 $10,009 $8,257 73 Earnings per common share-basic: 74 Income from continuing operations attributable to PharmaCorp Inc. common shareholder $1.27 $1.07 $1.03 75 Discontinued operations-net of tax $0.68 $0.21 $0.00 76 Net income attributable to PharmaCorp Inc. common shareholders $1.96 $1.28 $1.03 Earnings per common share- -diluted:A B D E F 76 Net income attributable to PharmaCorp Inc. common shareholders $1.96 $1.28 $1.03 77 Earnings per common share-diluted: 78 Income from continuing operations attributable to PharmaCorp Inc. common shareholder $1.26 $1.06 $1.03 79 Discontinued operations-net of tax $0.68 $0.21 $0.00 80 Net income attributable to PharmaCorp Inc. common shareholders $1.94 $1.27 $1.02 81 Weighted-average shares-basic 7.442 82 Weighted-average shares-diluted 7,817 8,036 7,508 83 Cash dividends paid per common share 7,870 8,074 $0.88 $0.80 84 $0.72A B D E F 86 PharmaCorp 87 Consolidated Statements of Cash Flows (USD $) 88 In Millions, unless otherwise specified Dec. 31, 2015 Dec. 31, 2014 Dec. 31, 2013 89 Operating Activities (unaudited) (audited) (audited) 90 Net income before allocation to noncontrolling interests $14,598 $10,049 $8,288 91 Adjustments to reconcile net income before allocation to noncontrolling interests to net cash provided by operating activities: 92 Depreciation and amortization 7,611 8,907 8,399 93 Share-based compensation expense 181 419 94 Asset write-offs and impairment charges 405 1,299 1,198 95 (Gain)/loss on sale of discontinued operations 3,486 -7,123 -1,688 96 Deferred taxes from continuing operations 11 739 307 2,109 97 Deferred taxes from discontinued operations 1,459 147 -156 98 Benefit plan contributions (in excess of)/less than expense 135 -1,769 99 Other non-cash adjustments, net -677 -203 -172 100 Other changes in assets and liabilities, net of acquisitions and divestitures: -49 101 Accounts receivable 275 -66 -608 102 Inventories -631 1,084 103 Other assets 2,917 83 701 104 Accounts payable -818 579 -367 105 Other liabilities -301 -3,438 1,508 106 Other tax accounts, net 1,114 1,190 -18 107 Net cash provided by operating activities -12,666 17,054 20,240 108 Investing Activities 11,454 109 Purchases of property, plant and equipment -1,327 -1,660 110 Purchases of short-term investments -1,513 -24,018 -18,447 111 Proceeds from redemptions and sales of short-term investments -11,082 25,302 14,176 5,699 112 Net proceeds from redemptions and sales of short-term investments with original maturi 1,459 10,874 5,950A B 110 Purchases of short-term investments D E F 111 Proceeds from redemptions and sales of short-term investments -24,018 -18,447 -11,082 112 Net proceeds from redemptions and sales of short-term investments with original maturi 25,302 14,176 5,699 113 Purchases of long-term investments 1,459 10,874 5,950 -11,145 114 Proceeds from redemptions and sales of long-term investments -4,620 -4,128 115 Acquisitions, net of cash acquired 4,990 2,147 4,737 116 Proceeds from sale of businesses -1,050 -3,282 -273 117 Other investing activities 11,850 2,376 0 118 Net cash provided by/ (used in) investing activities 93 279 118 119 Financing Activities 6,154 1,843 -492 120 Proceeds from short-term borrowings 121 Principal payments on short-term borrowings 7,995 12,810 6,400 122 Net payments on short-term borrowings with original maturities of 90 days or less -3 -3,826 -9,249 123 Principal payments on long-term debt -8,204 -7,540 -1,297 124 Purchases of common stock -1,513 -6,986 -6 125 Cash dividends paid -8,228 -9,000 -1,000 126 Other financing activities -6,534 -6,234 -6,088 127 Net cash used in financing activities 488 169 66 128 Effect of exchange-rate changes on cash and cash equivalents -15,999 -20,607 -11,174 129 Net increase/(decrease) in cash and cash equivalents -2 -29 130 Cash and cash equivalents, beginning 7,207 -31 1,447 -243 131 Cash and cash equivalents, ending 3,182 1,735 10,389 1,978 132 Cash paid during the period for: 3,182 1,735 133 Income taxes 134 Interest 2,430 2,938 11,775 135 $1,873 $2,085 $2,155