Order from us for quality, customized work in due time of your choice.
Hello, here! This is the problem, which consists of four parts. Each part includes four to five questions that need to be addressed. Please carefully read each requirement and do exactly what is asked of you. It’s very straightforward, therefore I don’t think I need to explain anything further. Let me know if any questions arise. Each requirement, or question, specifies how many points are available, so it is very important that you complete this assignment correctly in order for me to receive full credit.
1. (12 points) The President’s Inn worksheet in the BAN 830 – M1 Week 2 – Homework #1 – DATA Excel file provides a list of customers, rooms they occupied, arrival and departure dates, number of occupants, and daily rate for a small bed-and-breakfast inn during one month. Room rates are the same for one or two guests; however, additional guests must pay an additional $20 per person per day for meals. Guests staying for seven days or more receive a 10% discount.
(5 points) Create a new column that displays the daily rate for each party considering the number of guests in the party.
(2 points) Create a new column that displays the number of days stayed by each party.
(5 points) Create a new column that displays the revenue generated by each party, considering the daily rate and number of days stayed you previously calculated for each party. Don’t forget about meals and discounts, as appropriate.
2. (10 points) The Credit Risk worksheet in the BAN 830 – M1 Week 2 – Homework #1 – DATA Excel file provides information about 425 bank customers who applied for loans. The data include the purpose of the loan, checking and savings account balances, number of months as a customer of the bank, months employed, gender, marital status, age, housing status, number of years at current residence, job type, and credit-risk classification by the bank.
(5 points) Add two columns (one for Checking Status, one for Savings Status) after the Savings column that classify the checking and savings account balances (separately) as LOW if the balance is less than $250, MEDIUM if between $250 but less than $2000, and HIGH otherwise.
(5 points) To the right of your Checking Status and Savings Status columns, build a simple table that uses formulas to determine (1) how many customers applied for new-car, used-car, business, education, small-appliance, and furniture loans (separately) and (2) the number of customers with checking account balances less than $500.
3. (13 points) Shinyoung is considering borrowing $10,000 for a summer trip throughout Europe. She has narrowed her financing search down to two options. If she borrows from First National Bank of America, she’ll have an annual interest rate of 5.5%, payable quarterly over six years. If she borrows from American Savings and Trust, she’ll repay the loan monthly over four years with a 6% interest rate.
. (5 points) Using the blank Loan Options worksheet in the BAN 830 – M1 Week 2 – Homework #1 – DATA Excel file, use financial modeling to determine how much interest Shinyoung will have paid in the first year using both loan options. Indicate on your spreadsheet which loan options are less expensive and how much it would save Shinyoung (in the first year) compared to the more expensive option.
(3 points) Use financial modeling to compare the two loan options for the entire payback term. Indicate on your spreadsheet which option is less expensive over the entire payback term and by how much. Explain any differences in your answers to this part and the previous part.
(5 points) For the loan option that is less expensive over the entire payback term, create an amortization table that displays the periodic payment amount, the interest portion of each payment, the principal portion of each payment, and the principal balance after each payment for the duration of the payback term. How much total interest will be paid over the payback period?
4. (15 points) Using the Vehicle Ratings worksheet in the BAN 830 – M1 Week 2 – Homework #1 – DATA Excel file, create formulas using nested IF, AND, and OR functions to implement the three rating schemes described on the spreadsheet.
(5 points) See spreadsheet for problem.
(5 points) See spreadsheet for problem.
(5 points) See spreadsheet for problem.
When finished, submit a SINGLE Excel file solution in Canvas by the assignment due date shown on the course schedule.
Order from us for quality, customized work in due time of your choice.