Reference no: EM133889815

Part 1 - DuPont Case

Use the dataset on Canvas called Final_P1_DuPont_Data_SU.xlsxOpen this document with ReadSpeaker docReader Download Final_P1_DuPont_Data_SU.xlsxOpen this document with ReadSpeaker docReader

The file can also be found under Files/Final Exam Preparation/Final Exam Datasets.

Accounting ratios are based on financial statement data and allow interested parties to analyze and observe relationships between different aspects of a business. Accounting ratios have been grouped into categories such as Liquidity ratios, Activity/Asset Turnover ratios, Debt ratios, Profitability ratios, and Dividend Policy ratios.

In this portion of the exam, we are going to focus on Three ratios: Return on Equity, Debt Ratio, and Return on Debt.

Return on Equity (ROE):

Return on equity = Net Income/Total Shareholders Equity

This represents the amount of net income that is generated for each dollar of shareholder's equity. It can be interpreted as the amount of net income generated for each dollar of value that a shareholder owns of the company. This number can be either negative or positive. A higher ROE indicates better performance.

Debt ratio:

The debt ratio = Total Debt / Total Assets

This ratio measures a company's leverage in terms of total assets. It answers the question: What proportion of the total assets are financed by debt? A firm with a ratio near one is considered highly leveraged. This nearly universally represents greater risk to the firm's operations and inability to borrow additional capital. A debt ratio closer to zero is preferable. A lower Debt ratio means better performance.

As a reminder, Total Debt = Short Term Debt + Long Term Debt
Return on debt:

Return on debt = Net income / Long term debt

This ratio measures the amount of profit generate for every dollar of long-term debt. A higher Return on debt ratio indicates a more efficient use of debt.

Flag question: Question 1
Question 19 pts
P1Q1-Which company is Worst Performing in 2014 for median [Debt Ratio]? What is the value of its [Debt Ratio] for that year? (Use THREE decimals)

Flag question: Question 2
Question 29 pts
P1Q2-Which industry is Best Performing in 2015 for median [Return on Debt]? What is the value of its [Return on Debt] for that year? (Use Median and THREE decimals))

Flag question: Question 3
Question 39 pts
P1Q3-Which industry has seen the least improvement in median [Debt Ratio] from 2014 to 2015? How much is the change in median [Debt Ratio] for that industry? (Use THREE decimals)

Flag question: Question 4
Question 49 pts
P1Q4-Which company has the SECOND highest one-year max [Intangible Assets] in the Finance industry for the period of 2013-2015? What is that value of this company's [Intangible Assets]? (Use THREE decimals)

Flag question: Question 5
Question 59 pts
P1Q5-Which company has seen the greatest improvement in median [ROE] from 2013 to 2015? How much was the change (Absolute value and not %, use THREE decimals
Flag question: Question 6
Question 69 pts
P1Q6-Compare Cisco Systems to Oracle Corp. Which one has the more favorable change in sum [Return on Debt] from 2014 to 2015? How much is the change in [Return on Debt] for that company (use THREE decimals)?

Flag question: Question 7
Question 79 pts
P1Q7-Compare Intel Corp. to Lockheed Martin Corp. Which one has the more favorable change in sum [Debt Ratio] from 2013 to 2014? How much is the change in [Debt Ratio] for that company (use FIVE decimals)?

Flag question: Question 8
Question 85 pts
Part 2 - Submit DuPont Tableau file

Upload the tableau file you created for Part 1-DuPont. Please make sure it is in TWBX format. (3 pts)

-Part 1-DuPont
Part 3 - TechWear Case (9 pts each, Total 81 pts)

You will use TWO datasets on Canvas called

For Questions P3Q1-P3-Q6: Final_Part 3_TechWear_Q1-Q6_SU.xlsxOpen this document with ReadSpeaker docReader Download Final_Part 3_TechWear_Q1-Q6_SU.xlsxOpen this document with ReadSpeaker docReader

For Questions P3Q7-P3Q9: Final_Part 3_TechWear_Q7-Q9_SU.xlsxOpen this document with ReadSpeaker docReader Download Final_Part 3_TechWear_Q7-Q9_SU.xlsxOpen this document with ReadSpeaker docReader

The file can also be found under Files/Final Exam Preparation/Final Exam Datasets.

Background:

TechWear is a privately owned business that began operations in March 2015. Its sole business is the manufacture and sale of upper-end, high-tech sportswear. It only sells to large distribution outlets. Its primary product is a line of lightweight exercise clothes that contain a new, long-range RFID chip that captures the following information about the user based on personal data (age, weight, etc.) entered by the user:

Heart rate
Perspiration rate
Calories burned
Exercise efficiency (percent of capacity)
The chip is able to continuously send this information to a host device as far away as 15 miles. The clothes are also GPS enabled and able to track routes, distances and elevations. Management prides itself on being on the cutting edge. The company expects to conduct an IPO within a year or two.

TechWear recently retained your firm as its auditors, largely because of your commitment to conduct a highly efficient, technology-enabled audit.

It is now February 6, 2017, and you are ready to begin the year-end audit procedures for the 2016 audit. The client has provided the 2016 data that you requested (Analytics_mindset_case_studies_Techwear_P4.xls) so you can begin your work. Procedures have already been performed by your team to ensure that the data you received is complete and accurate.

The data file includes data on two tabs - 2016 AR data and 2016 inventory relief data.
2016 AR data tab

The data fields are the same as what you received for 2015, with the exclusion of the ship date. Additionally, the "Type" field includes transaction information for the opening balance (Opening Balance), which reconciles with the 2015 ending balance of $684,491.19 and the unapplied cash receipts (Unapplied Receipts).

2016 inventory relief data tab
This data includes the following fields:
ShipNum: this is the shipping number. This number becomes the sales transaction number when the invoice is created, which is the transaction number field on the 2016 AR data tab.
FedExID: this is the FedEx identification number. All items shipped on a given day will have the same number.
CustNum: this is a unique customer number to identify the customer (same field that is on the 2016 AR data tab).
CustName: this is the customer name (same field that is on the 2016 AR data tab).
InvoiceDate: this is the date the sale was invoiced (billed) (same field that is on the 2016 AR data tab).
ShipDate: this is the date the goods were shipped.
InvCostReliefAmount: this is the inventory cost relief amount, or the cost of sales.

Flag question: Question 9
Question 99 pts
(Remember, use the dataset Final_Part 3_TechWear_Q1-Q6_SU.xlsxOpen this document with ReadSpeaker docReader Download Final_Part 3_TechWear_Q1-Q6_SU.xlsxOpen this document with ReadSpeaker docReader for Questions P3Q1-P3-Q6)

P3Q1-Are there any sales transactions that do not have corresponding Shipping numbers? If yes, How many are there?

Flag question: Question 10
Question 109 pts
P3Q2-When did the earliest Transaction take place?

Flag question: Question 11
Question 119 pts
P3Q3-Which customer had the highest dollar amount of sales transactions in the month of May 2016? And what was the amount?

Flag question: Question 12
Question 129 pts
P3Q4-Identify and quantify any sales and the cost of sales for amounts recorded in the improper period for customer Oakland Athletics. How many transactions were there? And what was the total Inventory cost relief amount?

Flag question: Question 13
Question 139 pts
P3Q5-How many shipments were there during the month of June 2016 for Cool Thread? And what was the total cost of sales of those shipments?

Flag question: Question 14
Question 149 pts
P3Q6-Which customer had the highest number of shipments in 2017? What was the total transaction amount of those shipments?

Flag question: Question 15
Question 159 pts
Now change to the dataset to Final_Part 3_TechWear_Q7-Q9_SU.xlsx Open this document with ReadSpeaker docReader Download Final_Part 3_TechWear_Q7-Q9_SU.xlsxOpen this document with ReadSpeaker docReaderfor questions P3Q7-P3Q9

P3Q7-what is the total sales value for Fast N Fit?

Flag question: Question 16
Question 169 pts
P3Q8-What is the Total Cash Receipts for 2016 for all customers?

Flag question: Question 17
Question 179 pts
P3Q9-You first need to develop an aging analysis of accounts receivable at December 31, 2016, after reflecting about any proposed audit adjustments. Use the following aging categories (0-30 days, 31-60 days, 61-90 days, > 90 days and unapplied cash).

Question: What is the Grand Total for the category 61-90 days?
Hint: For the Aging Analysis, you need to use the following equations:
Age:

DATEDIFF("day",[Invoice Date],#2016-12-31#,"Sunday")

Aging Categories

IF [Age]<=30 THEN "0-30"
ELSEIF [Age]<=60 THEN "31-60"
ELSEIF [Age]<=90 THEN "61-90"
ELSEIF [Age]>90 THEN ">90"
ELSE "Unpaid Cash"
END

Question 18
Part 4 - Submit TechWear Tableau

LASTNAME_FIRSTNAME-Part 3-Techwear-Q1-Q3

Flag question: Question 19
Upload the tableau file you created for Part 3-TechWear-Q4-Q6. Please make sure it is in TWBX format. (2 pts)

Part 3-Techwear-Q4-Q6

Flag question: Question 20
Question 202 pts
Upload the tableau file you created for Part 3-TechWear-Q7-Q9. Please make sure it is in TWBX format. (2 pts)

-Part 3-Techwear-Q7-Q9
Thanks for waiting! I've now processed all the uploaded images. Here's a clean list of all the multiple choice questions (21 through 39), including the tables, dropdowns, and answer options where applicable:

Question 21

Which of the following is NOT true about Data Analytics?
a. Data Analytics is the evaluation of data with technology tools
b. Data analytics is a means of extracting value from data
c. It can tell a story about the data
d. It is always used as an exploratory technique
e. All of the above

Question 22

An analytics mindset is the ability to do all the following EXCEPT:
a. Ask the right questions
b. Hire people to run the analytics
c. Apply appropriate data analytics techniques
d. Interpret and share the results with stakeholders
e. None of the above (all are abilities)

Question 23

Which of the following is NOT a quality suitable for a data analytics software?
a. Able to import a variety of different file types
b. Able to handle large volumes of data
c. Logs the procedures performed on the data
d. Requires you call tech support frequently
e. None of the above (all are necessary qualities)

Question 24

Match the correct examples for 4Vs (Volume, Variety, Velocity, & Veracity) of BIG DATA.

V Answer
Volume Zettabytes of data
Variety Unstructured data
Velocity Data in motion
Veracity Data uncertainty

Question 25

A __________ is a convenient tool in Excel for summarizing data and computing basic statistics.
a. Pivot table
b. Filter
c. Formula
d. Format painter

Question 26

Which is the correct syntax for selecting the number 34?

Table:

Month Beads Sold Revenue
Jan 10 8
Feb 17 15
March 20 21
April 19 20
May 28 34
a. =INDEX(B3:B7,7)
b. =INDEX(D3:D7,5)
c. =INDEX(B3:D7,5)
d. =INDEX(D3:D7,7)
e. =INDEX(B3:D7,7)

Question 27

Which is the correct syntax for selecting the words "Month"?
a. =INDEX(A2:D2,1)
b. =INDEX(B2:D2,0)
c. =INDEX(D2:D7,1)
d. =INDEX(B2:D7,2)
e. =INDEX(B2:D2,1)

Question 28

What will be the output of the following formula:
=MATCH(G2,B2:B7,0)
(Assume G2 contains "March")
a. 1
b. 2
c. 3
d. 4
e. 5

Question 29

Which is NOT a likely benefit of RPA?
a. enhanced service quality
b. more perfect audit trails
c. decreased morale
d. improved security
e. reducing time spent within highly repetitive processes

Question 30

Which task attribute does NOT support RPA automation?
a. well-defined
b. high volume
c. rules-based
d. repeated
e. subjective

Question 31
Here are the questions and multiple-choice options (without answers) from Questions 31 to 39, based on the images you provided:

Question 31
In any organizations, RPA first replaces tasks that are:
a. perfunctory
b. well-defined
c. poorly documented
d. Both b and c
e. Both a and b

Question 32
True or False: RPA robots complete work the same way that humans do, through the software presentation layer.
? True
? False

Question 33
RPA can be helpful in which of the following situation(s)? (Select all that apply)
? Solve Captcha problems
? ETL
? Fill out forms
? Interpret the results of data analytics
? Provide individualized and customized feedback
? Create alert if difference exceeds materiality level

Question 34
In IDEA, in the Accounts Receivable Audit, what type of test is "Compare to accounts payable for possible contra accounts"?
a. matching and comparison test
b. exception test - existence and valuations
c. gaps and duplicates
d. sampling
e. mechanical accuracy and valuation

Question 35
In the Accounts Receivable Audit, what is the implication associated with the risk "credit is granted to customers that are likely to default"?
a. the company may be deemed to be economically dependent on just a few large customers, and this may need to be noted on the financial statements
b. the business will sell goods to parties from which they will not be able to collect cash
c. accounts could be partly or entirely invalid and fictitious accounts could be due to fraud
d. could affect the aging of Accounts Receivable and this would affect management's ability to determine an effective course of action for handling the customers' accounts

Question 36
What data would you need to conduct an accounts payable audit? (Select all that apply)
? AP data
? AR Data
? Customer Masterfile
? HR Masterfile
? Inventory
? Sales data
? Supplier Masterfile

Question 37
In the Accounts Payable Audit, what is the risk associated with the implication "Since Accounts Payable are often not authorized for payment until there is a three-way match of purchase order, receiving document and supplier invoice, missing documents could result in Accounts Payable being understated"?
a. invoices are paid on irregular dates
b. payments are made to individuals or employees
c. payments are made in a way to be undetected by audits
d. items (e.g., purchase orders, checks) are missing

Question 38
Which statement is true about Control Total?
a. Is always calculated based on the same preset variable
b. Can work with dates
c. Provides Statistics
d. None of the above are true (all are False)

Question 39
What does the function @IsIni in IDEA do?
a. Removes all spaces, punctuation and control characters from a text field leaving a string of characters and digits.
b. Breaks a character string into segments by a specified character or character string and returns a specified segment.
c. Searches for specified case-sensitive text within a Character field, Date field, or string expression.
d. None of the above

Attachment:- audit analytics quetions.rar