QS1 Part 1 Objective Questions
Please review the Data files in Tableau. After analyzing the data & the screenshots & the word document full of questions, please type up a One-Page Report summarizing all the data and questions.
QS1 Part 1 Objective Questions
OQ1: What value would be recorded for total sales revenue on the income statement for 2020? Answer: $5,103,763 is the total sales revenue generated for the year 2020. OQ2: Which month recorded the highest sales revenue? Answer: September 2022, with $ 477,291 sales revenue OQ3: What value would be recorded for total accounts receivable on the balance sheet for 2020, excluding the allowance for doubtful accounts? Answer: $4,030,533 is the total accounts receivables for the year 2020. OQ4: If management estimates about 5% sales to be written-off each month, which month exceeded the estimate? Answer: February & July 2020 OQ5: If management estimates about 3% of accounts receivables to be written off each month, what would be the adjustment amount for bad debts expense at the end of the year? Answer: $ 120,916 OQ6: Which months accounts receivable balance is most likely to be written off? Answer: August 2020
QS1 Part 1 Analysis Questions
AQ1: Why do you think there are no Write-offs in November & December? Answer: Because, the invoices were issued recently and there is still time in due dates of the invoices. AQ2: How could management estimate the write-offs for November & December? Answer: Management can take the average of write-off percentages for all the previous months or if we have the data from the last year, we can calculate the last year’s percentages according to the sales. AQ3: How could analytics provide additional insight into financial information beyond calculating balances? Answer: Analytics can helps gather the financial information needed to acquire a clear view of key performance indicators (KPI). For instance, revenue generated, net income, payroll costs. Analytics allows finance teams to scrutinize and comprehend vital metrics and detect fraud in revenue turnover.
QS1 Part 2 Objective Questions
OQ1: What are the total Sales Orders for November? Answer: 540 with Sales $ 416,563.
OQ2: Which day had the highest sales order amount in November? Answer: November 13th, 2020. Sales were $32,661 OQ3: Which Customer did the company sell the most in November? Answer: Thaddeus Patrick OQ4: How much did the company sell to that customer in November? Answer: $ 1,131 OQ5: What is the value of the oldest outstanding invoice in November? Answer: 1,196 OQ6: What is the age of the oldest outstanding invoice? Answer: 31 Days
OQ7: What is the current days sales outstanding KPI value for 2020? Answer: 30 Days
QS1 Part 2 Analysis Questions
AQ1: Why are some dates missing in the sales by date visualization? Answer: It’s because the invoices were issued before the Sales. AQ2: Some of the accounts may have a negative age. Is this an error? What might explain this? Answer: No, this is good for business. They paid before the due date. AQ3: What does the days sales outstanding KPI tell managers in general terms? Answer: It tells about daily productivity of the sales.
AQ4: What risks are present if you take too long to collect accounts receivable? Answer: They will be converted to Bad debts.
AQ5: What are some analyses you could perform that would provide insight into how efficiently your company is collecting cash from customers? Are there any KPIs that would be appropriate here? Answer: Improving Order to Cash Ratio, Timely collection of debts etc.
AQ6: In your opinion, what would be an appropriate benchmark for the average number of days sales outstanding (i.e., Accounts receivables/Sales x 365)? Would Management want this number higher or lower? Answer: The lower percentage is better for business, but it varies for business to business. The Accounts Receivable to Sales Ratio is a business liquidity ratio that measures how much of a company's sales occur on credit. When a company has a larger percentage of its sales happening on a credit basis, it may run into short-term liquidity problems.
QS1 Part 3 Objective Questions
OQ1: Which orders were created after shipment? Answer: There were 3 orders which were shipped before the order creation. One was created 6 days and 2 were created 4 days after the shipment. OQ2: How many orders were shipped but not yet invoiced? Answer: Zero Orders
QS1 Part 3 Analysis Questions
AQ1: How do you know some orders were created after shipment? Answer: The difference between ship days & Order days was negative. AQ2: Of the shipped orders without an invoice, which one is most problematic & why? Answer: Invoices serve as legally enforceable agreements between a business and its clients, as they provide documentation of services rendered and payment owed. Invoices also help businesses track their sales and manage their finances. AQ3: Why aren’t the other shipped orders without an invoice suspicious? Answer:
AQ4: What are some additional analyses that you could perform to understand whether the processes are being followed or controls are functioning properly? Answer: Procedures the auditor performs to test operating effectiveness include a mix of inquiry of appropriate personnel, observation of the company's operations, inspection of relevant documentation, and re-performance of the control.
AQ5: Under what circumstances might a delivery take place before a sales order? Should this happen? Answer: No, it should not happen. It might be possible if drawing the orders for personal use by business owners.
AQ6: What types of control would prevent the system from skipping a process or step? Answer: Internal Audits and preventive controls can help in prevention of missing a process.
QS1 Part 4 Analysis Questions
AQ1: What is the Monthly Average time for Orders Completion? Answer: Monthly Average time ranges from 1.79 days to 1.99 days. In the month of December 2020, orders were completed most efficiently at an average of 1.79 days whereas in January 2020 the order completion time was highest i.e., 1.99 days. AQ2: Do your customers pay on time? Answer: If we take out the December & November Orders, customers took extra 26 days after invoice due date to make a payment. Including the numbers of November & December, customers take 18 days on average to make the payment after due date has passed. AQ3: What is the trend on monthly total sales? Answer: There is no proper trend on monthly figures, but the sales in the month of March $387 K is relatively lower than other months. Sales are ranging from $413K to $488 K for all other months. AQ4: What is the Average time for every phase in O2C process? Answer: For November, the OTC process took 26 days, whereas for rest of the months excluding December the average days were 58. Invoice to Cash is the phase which took a lot of time in all the phases. AQ5: How early can an invoice be converted into receipt? Answer: In December 2020, Customers Paid 10 days before the invoice due date, whereas in January 2020 customers paid 60 days after the invoice due date.
,
Data/TableauTemp/#TableauTemp_1qjcmr90z0f8t0153f7on0jws8f2.hyper
Final File.twb
0 [Customer_Master] Count true 0 "A1:N3753:no:A1:N3753:0" true 2 0 [Sales_Orders] Count true 18 "A1:M6584:no:A1:M6584:0" true 2 0 [Invoices_Generated] Count true 22 "A1:H6572:no:A1:H6572:0" true 6 0 [AR_Adjustments] Count true 22 "A1:N297:no:A1:N297:0" true 6 0 [Cash_Received] Count true 6 "A1:I5009:no:A1:I5009:0" true 6 0 [Shipments_Made] Count true 14 "A1:I6563:no:A1:I6563:0" true 6 Customer_Account_ID 20 [Customer_Account_ID] [Customer_Master] Customer_Account_ID 0 integer Sum true "I8" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Customer_Account_Name 130 [Customer_Account_Name] [Customer_Master] Customer_Account_Name 1 string Count true "WSTR" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Customer_Physical_Street_Address1 130 [Customer_Physical_Street_Address1] [Customer_Master] Customer_Physical_Street_Address1 2 string Count true "WSTR" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Customer_Physical_Street_Address2 130 [Customer_Physical_Street_Address2] [Customer_Master] Customer_Physical_Street_Address2 3 string Count true "WSTR" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Customer_Physical_City 130 [Customer_Physical_City] [Customer_Master] Customer_Physical_City 4 string Count true "WSTR" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Customer_Physical_State_Province 130 [Customer_Physical_State_Province] [Customer_Master] Customer_Physical_State_Province 5 string Count true "WSTR" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Customer_Physical_ZipPostalCode 20 [Customer_Physical_ZipPostalCode] [Customer_Master] Customer_Physical_ZipPostalCode 6 integer Sum true "I8" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Active_Date 7 [Active_Date] [Customer_Master] Active_Date 7 date Year true "DATE" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Inactive_Date 7 [Inactive_Date] [Customer_Master] Inactive_Date 8 date Year true "DATE" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Transaction_Credit_Limit 20 [Transaction_Credit_Limit] [Customer_Master] Transaction_Credit_Limit 9 integer Sum true "I8" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Overall_Credit_Limit 20 [Overall_Credit_Limit] [Customer_Master] Overall_Credit_Limit 10 integer Sum true "I8" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Terms_Discount_Percentage 5 [Terms_Discount_Percentage] [Customer_Master] Terms_Discount_Percentage 11 real Sum 15 true "R8" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Terms_Discount_Days 20 [Terms_Discount_Days] [Customer_Master] Terms_Discount_Days 12 integer Sum true "I8" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Terms_Due_Days 20 [Terms_Due_Days] [Customer_Master] Terms_Due_Days 13 integer Sum true "I8" [Customer!Master_EF9A85C0D4CF4ADD9DF63A28AAF0BF8B] Sales_Order_ID 20 [Sales_Order_ID] [Sales_Orders] Sales_Order_ID 14 integer Sum true "I8" [Sales!Orders_1F185952615742729A875B0BC051F641] Sales_Order_Date 7 [Sales_Order_Date] [Sales_Orders] Sales_Order_Date 15 date Year true "DATE" [Sales!Orders_1F185952615742729A875B0BC051F641] Customer_Account_ID 20 [Customer_Account_ID (Sales!Orders)] [Sales_Orders] Customer_Account_ID 16 integer Sum true "I8" [Sales!Orders_1F185952615742729A875B0BC051F641] Sales_Order_Amount 5 [Sales_Order_Amount] [Sales_Orders] Sales_Order_Amount 17 real Sum 15 true "R8" [Sales!Orders_1F185952615742729A875B0BC051F641] Entered_By 20 [Entered_By] [Sales_Orders] Entered_By 18 integer Sum true "I8" [Sales!Orders_1F185952615742729A875B0BC051F641] Entered_Date 7 [Entered_Date] [Sales_Orders] Entered_Date 19 date Year true "DATE" [Sales!Orders_1F185952615742729A875B0BC051F641] Entered_Time 7 [Entered_Time] [Sales_Orders] Entered_Time 20 datetime Year true "DATE" [Sales!Orders_1F185952615742729A875B0BC051F641] Approved_By 20 [Approved_By] [Sales_Orders] Approved_By 21 integer Sum true "I8" [Sales!Orders_1F185952615742729A875B0BC051F641] Approved_Date 7 [Approved_Date] [Sales_Orders] Approved_Date 22 date Year true "DATE" [Sales!Orders_1F185952615742729A875B0BC051F641] Approved_Time 7 [Approved_Time] [Sales_Orders] Approved_Time 23 datetime Year true "DATE" [Sales!Orders_1F185952615742729A875B0BC051F641] Last_Modified_By 20 [Last_Modified_By] [Sales_Orders] Last_Modified_By 24 integer Sum true "I8" [Sales!Orders_1F185952615742729A875B0BC051F641] Last_Modified_Date 7 [Last_Modified_Date] [Sales_Orders] Last_Modified_Date 25 date Year true "DATE" [Sales!Orders_1F185952615742729A875B0BC051F641] Last_Modified_Time 7 [Last_Modified_Time] [Sales_Orders] Last_Modified_Time 26 datetime Year true "DATE" [Sales!Orders_1F185952615742729A875B0BC051F641] Invoice_ID 20 [Invoice_ID] [Invoices_Generated] Invoice_ID 27 integer Sum true "I8" [Invoices!Generated_9E39A2EA9BE547C5AA707EAC4ADCF7EB] Invoice_Date 7 [Invoice_Date] [Invoices_Generated] Invoice_Date 28 date Year true "DATE" [Invoices!Generated_9E39A2EA9BE547C5AA707EAC4ADCF7EB] Invoice_Due_Date 7 [Invoice_Due_Date] [Invoices_Generated] Invoice_Due_Date 29 date Year true "DATE" [Invoices!Generated_9E39A2EA9BE547C5AA707EAC4ADCF7EB] Sales_Order_ID 20 [Sales_Order_ID (Invoices!Generated)] [Invoices_Generated] Sales_Order_ID 30 integer Sum true "I8" [Invoices!Generated_9E39A2EA9BE547C5AA707EAC4ADCF7EB] Entered_By 20 [Entered_By (Invoices!Generated)] [Invoices_Generated] Entered_By 31 integer Sum true "I8" [Invoices!Generated_9E39A2EA9BE547C5AA707EAC4ADCF7EB] Entered_Date 7 [Entered_Date (Invoices!Generated)] [Invoices_Generated] Entered_Date 32 date Year true "DATE" [Invoices!Generated_9E39A2EA9BE547C5AA707EAC4ADCF7EB] Entered_Time 7 [Entered_Time (Invoices!Generated)] [Invoices_Generated] Entered_Time 33 datetime Year true "DATE" [Invoices!Generated_9E39A2EA9BE547C5AA707EAC4ADCF7EB] Invoice_Amount 5 [Invoice_Amount] [Invoices_Generated] Invoice_Amount 34 real Sum 15 true "R8" [Invoices!Generated_9E39A2EA9BE547C5AA707EAC4ADCF7EB] Adjustment_ID 20 [Adjustment_ID] [AR_Adjustments] Adjustment_ID 35 integer Sum true "I8" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Adjustment_Type 130 [Adjustment_Type] [AR_Adjustments] Adjustment_Type 36 string Count true "WSTR" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Invoice_ID 20 [Invoice_ID (AR!Adjustments)] [AR_Adjustments] Invoice_ID 37 integer Sum true "I8" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Adjustment_Date 7 [Adjustment_Date] [AR_Adjustments] Adjustment_Date 38 date Year true "DATE" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Adjustment_Amount 5 [Adjustment_Amount] [AR_Adjustments] Adjustment_Amount 39 real Sum 15 true "R8" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Entered_By 20 [Entered_By (AR!Adjustments)] [AR_Adjustments] Entered_By 40 integer Sum true "I8" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Entered_Date 7 [Entered_Date (AR!Adjustments)] [AR_Adjustments] Entered_Date 41 date Year true "DATE" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Entered_Time 5 [Entered_Time (AR!Adjustments)] [AR_Adjustments] Entered_Time 42 real Sum 15 true "R8" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Approved_By 20 [Approved_By (AR!Adjustments)] [AR_Adjustments] Approved_By 43 integer Sum true "I8" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Approved_Date 7 [Approved_Date (AR!Adjustments)] [AR_Adjustments] Approved_Date 44 date Year true "DATE" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Approved_Time 7 [Approved_Time (AR!Adjustments)] [AR_Adjustments] Approved_Time 45 datetime Year true "DATE" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Last_Modified_By 20 [Last_Modified_By (AR!Adjustments)] [AR_Adjustments] Last_Modified_By 46 integer Sum true "I8" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Last_Modified_Date 7 [Last_Modified_Date (AR!Adjustments)] [AR_Adjustments] Last_Modified_Date 47 date Year true "DATE" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Last_Modified_Time 7 [Last_Modified_Time (AR!Adjustments)] [AR_Adjustments] Last_Modified_Time 48 datetime Year true "DATE" [AR!Adjustments_3BD415A16F87401EA791B2780B252BC7] Receipt_ID 20 [Receipt_ID] [Cash_Received] Receipt_ID 49 integer Sum true "I8" [Cash!Received_BF6A53616ED7469EA2A0BB08F398654B] Receipt_Date 7 [Receipt_Date] [Cash_Received] Receipt_Date 50 date Year true "DATE" [Cash!Received_BF6A53616ED7469EA2A0BB08F398654B] Invoice_ID 20 [Invoice_ID (Cash!Received)] [Cash_Received] Invoice_ID 51 integer Sum true "I8" [Cash!Received_BF6A53616ED7469EA2A0BB08F398654B] Receipt_Type 130 [Receipt_Type] [Cash_Received] Receipt_Type 52 string Count true "WSTR" [Cash!Received_BF6A53616ED7469EA2A0BB08F398654B] Receipt_Amount 5 [Receipt_Amount] [Cash_Received] Receipt_Amount 53 real Sum 15 true "R8" [Cash!Received_BF6A53616ED7469EA2A0BB08F398654B] Posting_Status 130 [Posting_Status] [Cash_Received] Posting_Status 54 string Count true "WSTR" [Cash!Received_BF6A53616ED7469EA2A0BB08F398654B] Entered_By 20 [Entered_By (Cash!Received)] [Cash_Received] Entered_By 55 integer Sum true "I8" [Cash!Received_BF6A53616ED7469EA2A0BB08F398654B] Entered_Date 7 [Entered_Date (Cash!Received)] [Cash_Received] Entered_Date 56 date Year true "DATE"
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
