Use VLOOKUP to pull in descriptive attributes from other tables into the transaction table. Ask the Question: How can we pull in descriptive attributes from separate tables into the transaction table that we wish to use for analysis?

 In this lab, you will:
Required:
Use VLOOKUP to pull in descriptive attributes from other tables into the transaction table.
 
Ask the Question: How can we pull in descriptive attributes from separate tables into the transaction table that we wish to use for analysis?

Master the Data: The dataset contains information about sales transactions to different customers. There are 132 unique transactions in the Sales_Transactions table, but 149 rows—this is because some of the transactions had multiple products on them.
This data is similar to the data used in several of the labs in Chapter 3; however, in Chapter 3, the data had already been transformed. In this lab, we will pull in fields from the Customers and Products table to make the analysis in the transaction table more interesting.
 
Software needed
•    Excel
•    Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4)

 Required:
1. Which product was on Sales Order 20001?
 

•     Stout
•     Imperial IPA
•     Pale Ale
•     Imperial Stout 

Answer

Pale Ale 

2. How many Sales Orders is Imperial IPA on?
 

•     149
•     10
•     61
•     17 

Answer

10
 
3. What is the quantity sold of Imperial IPA?
 
 
•     149
•     10
•     61
•     17 

Answer

61
 
4. When creating the VLOOKUP for this lab, which variable has the matching key between the Sales_Transactions and Products tables?
 
 
•     Customer_ID
•     Product_Code
•     Sales_Order_ID
•     Product_Description 

Answer
 Customer_ID 

5. If you replaced the number 2 for the column_num with a 3 (the third argument In the VLOOKUP function), what descriptive information would return instead of Product_Description?
 

•     Sales_Employee_ID
•     Product_Sale_Price
•     Sales_Order_ID
•     Product_Code

Answer

Product_Sale_Price

Thanks

To begin, we have a list of 200 receivables that are all past their due date as of today’s date of 12/31/2021. This is shown in the Excel file Lab 2-2 Data.xlsx, which will be imported into Tableau.

 In this lab, you will:

Required:

1. Create a calculated field to calculate the Days Past Due for each invoice.

2. Summarize the accounts receivable by customer.

3. Get the detailed receivables for the customer “Taste of Thai”.

 

Ask the Question: How can we use Tableau on the total detailed late accounts receivables balance to get specific detail on invoices due by customer?

 

Master the Data: To begin, we have a list of 200 receivables that are all past their due date as of today’s date of 12/31/2021. This is shown in the Excel file Lab 2-2 Data.xlsx, which will be imported into Tableau.

 

Software needed

Tableau
Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4)

 Required:

1. What is the grand total amount owed in past due invoices for Brewed Miracles?

 

multiple choice 1
5,078
4,805 Correct
1,393
187

 

2. How many days late is Grandma’s Greasy Spoon’s oldest invoice (most days past due)?

 

multiple choice 2
419
159 Correct
84
720

 

3. How many invoices does Pita Pan have past due?

 

multiple choice 3
18 Correct
5
4
10

 

4. Which customer has the highest balance past due?

 

multiple choice 4
Grandma’s Greasy Spoon
One in a Million Correct
Pita Pan
Planet of the Grapes

 

5. Which customer owes $5,156?

 

multiple choice 5
Tequila Taste Correct
We Fry it All
The Godfather
Thai Tap

Here

Let’s suppose you would like to buy a home for $250,000. But like most U.S. citizens, you don’t have enough cash on hand to pay for the full house. But we’re in luck! Signature Bank has agreed to offer you a 30-year mortgage loan, but requires that you pay 20 percent down ($50,000 = 20% of $250,000) to qualify for their mortgage loan of $200,000 in this way:

 Let’s suppose you would like to buy a home for $250,000. But like most U.S. citizens, you don’t have enough cash on hand to pay for the full house. But we’re in luck! Signature Bank has agreed to offer you a 30-year mortgage loan, but requires that you pay 20 percent down ($50,000 = 20% of $250,000) to qualify for their mortgage loan of $200,000 in this way:
 
$250,000     Cost of home
50,000     Required 20% down payment ($50,000 = 20% of $250,000) (The
      cash you need to have available to pay when closing on the home)
$200,000     Amount of the bank loan

 

Software needed

    Excel
    Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4)

 

Perform the Analysis: Refer to Lab 1-3 Alternative in the text for instructions and Lab 1-3 steps for each of the lab parts.

 

Share the Story: Accountants need to know how much of each monthly mortgage (or bond) payment goes toward interest.

 

 

Required:

1. What would be the monthly payment for 180 months, 6% annual interest and a $200,000 loan?

 

multiple choice 1

    $1,687.71 Correct
    $3,314.58
    $1,199.10
    $1,064.48

 

2. What would be the monthly payment for 72 months, 6% annual interest and a $200,000 loan?

 

multiple choice 2

    $1,199.10
    $3,314.58 Correct
    $1,687.71
    $1,064.48

 

3. For the 180-month mortgage, what is the amount that goes toward paying down principal in monthly payment number 20?

 

multiple choice 3

    $931.64
    $759.04
    $756.07 Correct
    $752.31

 

4. For the 72-month mortgage, what is the amount of interest expense in monthly payment number 3?

 

multiple choice 4

    $965.11
    $976.80 Correct
    $993.11
    $2,337.78

 

5. What is the total amount of interest paid over the life of the 180-month mortgage?

 

multiple choice 5

    $238,649.59
    $38,649.59
    $103,788.46 Correct
    $303,788.46