At the close of business on Wednesday, Gene Wandling was reviewing whether Hawkeye National Bank was successful in meeting its legal reserve requirements at the Federal Reserve

At the close of business on Wednesday, Gene Wandling was reviewing whether Hawkeye National Bank was successful in meeting its legal reserve requirements at the Federal Reserve over the past two week maintenance period.  During the maintenance period the bank held $232 million in reserve deposits with the Fed.  The bank had a daily average reserve deficiency during the previous maintenance period of $7.8 million, which was within the allowable four percent limit.

Hawkeye National Bank’s daily average transactions accounts for the base computation periods along with other selected information are listed below:

        Daily Average (millions of dollars):
            Transactions accounts                3,257.9
            Balances due from U.S. depositories           365.5
            C.I.P.C.                           181.5
            Vault Cash                             31

a.  Calculate the bank’s Gross Reserve Requirement (using 1/16/2020 Fed     
    requirements).

    Net Transactions Accts.  =  3,257.9 – 365.5 – 181.5  =  2,710.9



        











b.  Calculate the bank’s Net Reserve Requirement.

        Net Reserve Req.  =  257.654  -  31  =  226.654






c.  Did Hawkeye National Bank meet its required reserve target?  Explain.


    No:        Needed    226.654  +  7.8  =  234.454
            Actual Balance                 232.0


            Deficient by 2.454





d.  If the bank had carried forward a reserve surplus of $2.1 million (instead of the deficiency, would the bank have met its target?  Explain.


    Yes        Needed    226.654  -  2.1  =  224.554
            Actual Balance                232.0

        
            Surplus of 7.446

On November 1, 2017, the account balances of Hamm Equipment Repair were as follows:

 On November 1, 2017, the account balances of Hamm Equipment Repair were as follows:

 During November, the following summary transactions were completed.

Nov. 8     Paid $1,700 for salaries due employees, of which $700 is for October salaries.
10     Received $3,620 cash from customers on account.
12     Received $3,100 cash for services performed in November.
15     Purchased equipment on account $2,000.
17     Purchased supplies on account $700.
20     Paid creditors on account $2,700.
22     Paid November rent $400.
25     Paid salaries $1,700.
27     Performed services on account and billed customers for these services $2,200.
29     Received $600 from customers for future service.

Adjustment data consist of:

1. Supplies on hand $1,400.
2. Accrued salaries payable $350.
3. Depreciation for the month is $200.
4. Services related to unearned service revenue of $1,220 were performed.

Instructions:
(a) Enter the November 1 balances in the ledger accounts.

(b) Journalize the November transactions.

(c) Post to the ledger accounts. 

(d) Prepare a trial balance at November 30.
(e) Journalize and post adjusting entries.
(f) Prepare an adjusted trial balance.
(g) Prepare an income statement and an owner’s equity statement for November and a balance sheet at November 30.

Thanks

In this lab, you will: Required: 1. Create the Days Past Due measure. 2. Create the 30-day bins based on the Days Past Due Measure. 3. Detail the receivables from the 151–180 days bucket. Ask the Question: How can we age receivables using Excel pivottables? Master the Data: Apply the same steps as Lab 6-2 in Tableau, this time using the Lab 6-2 Alt Data.xlsx dataset.

 In this lab, you will:

Required:

1. Create the Days Past Due measure.

2. Create the 30-day bins based on the Days Past Due Measure.

3. Detail the receivables from the 151–180 days bucket.

 

Ask the Question: How can we age receivables using Excel pivottables?

 

Master the Data: Apply the same steps as Lab 6-2 in Tableau, this time using the Lab 6-2 Alt Data.xlsx dataset.

 

Software needed

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

 
Data: Lab 6-2 Alt Data.xlsx.


Perform the Analysis: Refer to Lab 6-2 Alternate in the text for instructions and Lab 6-2 steps for each the of lab parts.


Share the Story: Now that you have aged the receivables, the company is better able to estimate its allowance for doubtful accounts (the contra receivable accounts that reduces gross accounts receivable into net accounts receivable). As a rule, as accounts receivable age, they are less likely to be collected.

 
Required:

1. What is the total amount One in a Million has due in the 31-60 day bin?

    2000.86
    1523.00
    406.68
    1952.30 

Answer

1952.30

2. In the 31-60 day bin, One in a Million has an invoice that was due on 11/3/2021.  How much was it for?

    487.24
    406.68
    108.87
    684.50 

Answer

 406.68

3. How many open invoices does One in a Million have in the 31-60 day bin?

    5
    2
    3
    4
Answer

5

4. How many invoices are due in the 61-90 bin? (Hint, view the Full Data to see a count of rows)

    37
    47
    40
    1

 Answer

37

5. Which bin has the lowest amount due?

    31-60
    1-30
    91-120
    151-180
    61-90
    121-150
Answer

121-150

 

 

What will Microsoft’s quarterly sales and earnings for 2018 and 2019 be given times series (past quarterly sales and earnings from 2010–2017)?

 In this lab, you will:
Required:
1. Using Excel’s Forecast Sheet, forecast future sales for Microsoft.
2. Using Excel’s Forecast Sheet, forecast future income before extraordinary items for Microsoft.
 
Ask the Question: What will Microsoft’s quarterly sales and earnings for 2018 and 2019 be given times series (past quarterly sales and earnings from 2010–2017)?
 
Master the Data: Looking at Microsoft’s financial statements, we accumulate the values of quarterly sales and earnings from 2010–2017.
 
Software needed
•    Excel
•    Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4)
 
Data: Excel File Lab 8-6 Alt Data.xlsx.
 
Perform the Analysis: Refer to Lab 8-6 Alternate in the text for instructions and Lab 8-6 steps for each of the lab parts.
 
Share the Story: We have now developed a forecast for sales and income before extraordinary items.
 

Required:
1. Which has a greater distance between the lower and upper confidence bound for Microsoft?
 
multiple choice 1
•     Sales
•     Income before extraordinary items 

 
 
2. For Microsoft’s income before extraordinary items and the quarter 7/1/2020, what is the distance between the lower and upper confidence bound?
 
multiple choice 2
•     11,611.28
•     365.50
•     10,880.30
•     No difference between lower and upper confidence bound
 
3. What is the forecasted amount of Microsoft’s income from extraordinary items for the 10/1/2019 time period?
 
multiple choice 3
•     5,364.64
•     31,546.71
•     5,440.15
•     5,326.88
 
4. What is the forecasted amount of Microsoft’s sales for the 10/1/2019 time period?
 
multiple choice 4
•     31,546.71
•     5,326.88
•     27,744.51
•     29,440.27
 
5. Looking at the past trend of sales or income before extraordinary items for Microsoft, which one was less volatile, sales or income before extraordinary items?
 
multiple choice 5
•     Income Before Extraordinary Items
•     Sales

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