conditional summary functions,

Problem 1: Julianne Zaslow just inherited $100,000 from her great aunt Abigail. Julianne and
her partner are thrilled beyond words. They have lived in their current home for just over 13
years. While the Zaslows still love their 2-bedroom home, it is now too crowded for their family of
five. The Zaslows want to use part or all of the inheritance to buy their dream home; a five
bedroom home two blocks from their current house.
The Zaslow’s took out a 30-year mortgage loan for $275,000, with an APR of 4.25% just over
13 years ago. They have just made their 160th payment.
a. How much do the Zaslows owe on the loan of their current home, right after making the
160th payment?
b. The Zaslow’s have accepted a $375,000 offer for their current home. After the sale,
how much will the Zaslows have available to be used as down payment for the new
home, including the inheritance money. Assume the sale occurs immediately after the
Zaslows make the 160th payment.
c. Their dream home is listed for $700,000. The Zaslow’s plan to make a full listing-price
offer. The Zaslow’s can finance the new home with their current bank. The bank has
given them two financing options:
! A 15-Year Mortgage with an APR of 4.75%, with a minimum 20% down payment.
! A 30-Year Mortgage with an APR of 5.75%, with a minimum 20% down payment.
What’s the monthly payment associated with each of the two mortgages?
d. What’s the total amount of interest the Zaslows will pay under each mortgage listed in part c?
e. The Zaslow’s would like to have a monthly payment of $3,500. Because of the
difference in the amount of interest paid, the Zaslows like the idea of having a 15-year
mortgage. Could they afford such a mortgage?
! In other words, what would they need to put as a down payment on the 15-
year mortgage to make the monthly payment affordable?
! How much money will they have left, if any?
Paste a copy of the filled-in Goal Seek dialog box on the spreadsheet.
Problem 2: Using conditional summary functions, answer the following
questions: For this problem, use the data available in the Problem 2
worksheet.
a. Esperanza House wants to compute the Average Daily Rate charged for each State,
as well as the Total Charges ($) associated with each state. Place your answers in the
table located in cells Q3:S7. Use conditional summary functions.
b. How many patients who have stayed at Esperanza House, from each of the given
states, have been treated at UIH, Rush and at the UofC hospitals? Place your answers
in the table located in Q11:U15. Use conditional summary functions.
You should place one formula in cell R13 that can be copied across and down the table.
c. Based on the state selected in U19 and the length of stay in days (given in columns R
and S), compute the number of patients who had that stay length. Place your formula
in cell T21. Copy it down the column.
d. Based on the state selected in U19 and the length of stay in days (given in columns R
and S), compute the percentage of total charges paid — Paid Amount ($)/Total
Charges ($) in cell U21. Copy your cell down the column.
! Be sure to error check your formula, so that #DIV/0! shows as a zero.
e. Find the average length of stay and the average total charges for patients referred by
the Referral Agent listed in R29 from the Hospital/Assoc listed in Q29. Place
answers in S29 and T29, respectively.
Problem 3: As the new analytics hire at First Band & Trust, you have been asked to conduct an
analysis of the bank’s mortgage loans. The data includes the customer’s name, the loan date,
loan amount, loan duration (in years), Annual Percentage Rate (APR), number of completed
payments by the customer (all loans are paid monthly), and the date in which the loan will be
fully paid. Note that if the date is in the past, e.g., in a previous year, it means the loan has been
paid off.
a. For each loan, calculate the amount of interest paid to date. Place your answer in
cell H5. Auto-fill the column. You should not hard-code any values in your formula.
b. For each loan, calculate the amount of principal paid to date. Place your answer
in cell I5. Auto-fill the column. You should not hard-code any values in your
formula.
c. Calculate the loan balance. Place your answer in cell J5.
The bank is considering making refinancing offers to customers with outstanding loans, e.g.,
loans that have not been fully paid yet. The messages will focus on two themes:
1. Offering a 1% drop on the customer’s Loan APR
2. Suggesting that customers pay an additional $150 monthly
d. Create a refinancing message that states “We can offer you a new loan with a 1%
drop in interest. Your new loan will have payments of: new loan payment over
loan term years.”
! Calculate the new loan payment.
If you do not know how to calculate the new loan payment, calculate the payment
based on the old loan, and display that in the parts below.
! Using Concatenation and the Text function construct the statement for each
customer. Note that the loan term is not the same for every customer, and that
the bank is assuming customers will refinance the new loan for the same
duration as the original loan.
! The message should only display for customers who have not finished paying
off their loans.
e. Create a refinancing message that states “If you pay an additional $150 each month,
you will reduce your repayment schedule by: reduction in repayment schedule. ”
! Calculate the reduction in repayment schedule.
If you do not know how to calculate reduction in payment schedule, calculate the
number of periods to repay the old loan with an additional $150, and display that
in the parts below.
! Using Concatenation and the Text function construct the statement for each
customer. Note that the loan term is not the same for every customer, and that
the bank is assuming customers will refinance the new loan for the same
duration as the original loan.
! The message should only display for customers who have not finished paying
off their loans.
Problem 4: You have been asked to create a worksheet to analyze inventory for a sporting
goods store. The inventory-related data is available in the Problem 4 worksheet.
Inventory Analysis
a. What is the total dollar value for all items Hank’s has inventory? Place answer in K2.
b. How many items in inventory include the word Shoes in their Item Description and are
the Color Red? Use Conditional Summary Functions. You may want to use the wildcard
(*) as well. Place answer in K3.
What is the average selling prices for items in inventory that include the word Shoes in
their Item Description and are the Color Red? Use Conditional Summary Functions.
Place answer in K4.
Inventory Lookup
d. Select a category in cell K7. Find the average price for all items in the selected
category. Place answer in K8. Use Conditional Summary Functions
e. Create the label shown in cell J10. That is, your label should reflect the category shown in K7
and the average price found in cell K8.
f. Return the number of Items that belong to the category selected in K7. Place answer inK10. Use
Conditional Summary Functions
Price Lookup
g. Based on the Item Inventory Number selected in cell K13:
! Find the Item Description.
! Find the quantity in Stock for the item.
h. Choose a metric in cell J16 (Color, Manufacturer, Size, Cost, Selling Price or Stock),
and display the corresponding value of the metric in cell K16. Note: If the metric is
currency related (Selling Price, or Cost), display the result as currency. Otherwise,
display using the General format.
⎝ ⎠
Problem 5: Jason Stuart needs some help getting his debt under control. After hearing that
you’re taking a spreadsheet modeling course, and because you’ve been friends since college,
he’s asked for your help.
You will need to fill in the gray cells in the worksheet.
i. Jason is considering rolling over his students loans to Wells Fargo. The bank offers
an APR of 3.50% (compounded monthly), with a 10 year repayment schedule.
Calculate the monthly payment required to pay back the loan. Place the formula/
result in cell D6.
In an attempt to pay off his $6,952.25 in debt across three credit cards, Jason has
been making payments of $120, $40, and $185, respectively, to his Visa, Master Card,
and American Express Cards.
j. If he continues to make these monthly payments, how many months will it take for him to
finish paying his credit cards? Place your answers in E3:E5.
k. How much interest will Jason pay to each credit card company? Place your answers in
cells G3:G5. Add the overall interest total to cell G7. Hint: You do not need Excel
Financial functions to do this part. Here’s how to think about it: If you borrow $100 from a
classmate, and he asks that you pay him back with four (4) payments of $30, how much
will you pay in interest?
You suggest to Jason that he should have a more focused strategy in paying off his cards. While
this may not save him a significant amount in interest, it will be more emotionally satisfying to see
“progress” in paying off his debt. Specifically, you tell him that he should pay as much as possible
to the credit card with the highest interest rate first, while just paying the minimum in the other
cards. Then, he should move to the card with the next highest interest rate, and so on. Thus, you
agree to tackle the debt in three stages.
! Stage 1: Pay as much as possible towards the American Express card (the card with
the highest APR), and pay minimum amounts to the Chase Visa and Bank of America
MasterCard.
o Jason will keep his total monthly expenditures fixed at $468.61, and will
continue to pay for his student loan payment.
o Payments towards the Visa and MasterCard credit cards will be: ▪ MAX
⎛ APR
#Card Balance,$20


12

▪ Payments will stay constant throughout the stage.
l. How much will the new monthly payments for each card be? Place your answers
in cells E12:E14.
m. How many months will it take for Jason to pay off his American Express credit card,
assuming he does not charge any other amounts to it? Place your answer in cell F14.
n. What will he owe (ending balance) for each credit card at the end of stage 1. Hint:
Use the FV function.
o. How much interest was paid to each credit card? Place your answers in cells H12:H14.
Stage 2: Update the balances for each credit card, based on your answer to part e above.
Re- calculate the new monthly payments for the Visa and MasterCard credit cards.
p. How many months will it take for Jason to pay off his Chase Visa credit card,
assuming he does not charge any other amounts to it? Place your answer in cell F21.
q. What will he owe (ending balance) for each credit card at the end of stage 2.
Stage 3: Update the balances for the MasterCard credit card, based on your answer to
part i above. Re-calculate the new monthly payment for the MasterCard credit card.
r. How many months will it take for Jason to pay off his Bank of America MC, assuming he
does not charge any other amounts to it. Place your answer in cell F31.
By following this strategy to pay off his debt, how much will Jason save in interest?

Leave a Comment

Your email address will not be published. Required fields are marked *

+1 587-331-9072
WHAT'SAPP US, WE'LL RESPOND
AustralianEssayHelp
We will write your work from scratch and ensure that it is plagiarism FREE, you just submit the completed work.