Lab 10-1

.docx

School

University of Maryland Global Campus (UMGC) *

*We aren’t endorsed by this school

Course

628

Subject

Industrial Engineering

Date

Apr 3, 2024

Type

docx

Pages

2

Uploaded by Fbidbid on coursehero.com

Build a new dashboard (Tableau) or page (Power BI) called Financial that includes the following: 1. Create a new workbook, connect to 10-1 O2C Data.xIsx, and import all seven tables. Double-check the data model to ensure relationships are correctly defined as shown in Exhibit 10-1. 2. Add a table to your worksheet or page called Sales and Receivables that shows the invoice month in each row and the invoice amount, receipt amount, adjustment amount, AR balance, and write-off percentage in the columns. Tableau Hint: Use Measure Names in the columns and Measure Values in the marks to create your table. Then once your table is complete, use Analytics > Summarize > Totals to calculate column totals. a. You will need to create a new measure or calculated field showing the account AR Balance, or the total invoice amount minus the total receipt amount minus the total adjustment amount. Tableau Hint: To minimize erroneous values from appearing in Tableau due to blank or missing values, use the IFNULL() function to replace blank values with O, for example, IFNULL([Receipt Amount],0). b. You will need to calculate the write-off percentage as the total AR adjustment divided by total invoice amount. Hint: Format the write-off percentage as a percent or to four decimals. c. Filter this visual to show only values from January 2020 to December 2020. 3. Add a new bar chart called Bad Debts that shows the invoice amount and adjustment amount along with a tooltip for write-off percentage. Tableau Hint: Choose Dual Axis and Synchronize Axis to combine the two values. a. Filter this visual to show only values from January 2020 to December 2020. b. You will need to calculate the write-off percentage as the total AR adjustment divided by total invoice amount. Hint: Format the write-off percentage as a percent or to four decimals. c. Filter this visual to show only values from January 2020 to December 2020. 3. Add a new bar chart called Bad Debts that shows the invoice amount and adjustment amount along with a tooltip for write-off percentage. Tableau Hint: Choose Dual Axis and Synchronize Axis to combine the two values. a. Filter this visual to show only values from January 2020 to December 2020. 4. Clean up the formatting and titles of your visuals and combine them into a single dashboard or page labeled Financial. 5. [0i Take a screenshot of your dashboard showing the account balances (label it 10-1A). 6. Save your workbook as 10-1 O2C Analysis, answer the lab questions, then continue to Part 2. Build a dashboard (Tableau) or page (Power BI) called Management with the following: 1. Add a filter to show only sales orders from November. 2. Add a table to your page called Total Sales by Day that shows the total sales order amount by sales order date. Power Bl Hint: Use the date hierarchy to drill down to specific days of the month. Tableau Hint: Set the sales order date to DAY() and place the total sales order amount as a text mark. 3. Add a bar chart to your page called Sales by Customer that shows the total sales order amount by customer account name | descending order. 4. Add a new matrix table to your page called AR by Customer that shows the customer and invoices in rows, and earliest invoice due date, age, and balance as values. a. Create a parameter showing the Report Date as 12/31/2020. Power Bl Hint: Create a new column and use the DATE() function. b. Create a new measure showing the Age as the difference between the Invoice Due Date and the Report Date. Power Bl Hint: Use the DATEDIFF() function to calculate the age and the MIN() function on the date fields to load specific dates. c. Use the AR Balance you created in Part 1. d. Filter the table to show only outstanding balances that are greater than O. 5. Add a new card to your page called Days Sales Outstanding to show the current KPI value. Hint: Create a new measure showing the DSO as the accounts receivable balance divided by the total sales amount multiplied by 30 days. 6. In Tableau, combine all of these visuals into one dashboard. 7. 101 Take a screenshot of your dashboard (label it 10-1B).
Build a new dashboard (in Tableau) or page (in Power Bl) called Audit that includes the following: 1. Add a table to your page called Exceptions to identify any shipments that occurred before the order was placed. It should show the Sales Order ID and the number of days to ship in ascending order. a. Create a new measure called Order To Ship Days that calculates the difference between the sales order date and the shipment date. Power Bl Hint: Use the DATEDIFF() function to calculate the difference and the MIN() function on the date fields to load specific dates. b. Filter this visual on order to ship days to show only negative values. 2. Add a new matrix table called Missing Invoice to determine whether any orders have shipped but have not yet been invoiced. It should list the sales orders, earliest (minimum) shipment date, minimum shipment ID, and minimum invoice ID. a. Filter this visual on invoice ID to show only missing (blank) values. 3. You should find at least one exception here. If you don’t see any exceptions, try selecting different months in the sales order date month filter. 4. [01 Take a screenshot of your dashboard showing exceptions and missing invoices (label it 10-1C).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help