Category: Excel

Your analysis of the case study bank will take a giant leap forward with Part II

Your analysis of the case study bank will take a giant leap forward with Part II. This part will
concentrate on regulatory attributes of soundness and liquidity for your assigned bank.
Banks don’t necessarily provide all info required for an outsider to run these calculations. Some
assumptions must be made. For example, a 30-day cash out flow can be assumed to be some credit
line funding, 1/12 of the total of CDs maturing this FY, and a proportion (5-10% runoff) of
deposits. (To get the amount of credit line fundings, check the bank’s OBS liabilities. For CD
totals, check notes for Deposits.)
Purpose:
Develop an understanding of the criteria of banking regulations, and the areas of particular interest
to regulators.
Instructions:
Use the bank data you compiled when you were doing Part I. Use the amended cover sheet
provided in this week’s files. Copy and paste the format to your existing cover sheet. After
completion of the calculations, determine if you believe the bank is in compliance. (They probably
are). Upon completion, upload your file to canvas. 50 points total.
Calculations Required (see PowerPoint “Global Banking Regulations and Standards”):
Capital Adequacy Ratio (CAR)
Liquidity Coverage Ratio
Net Stable Funding Ratio
Attached is calculations from Part 1 for assigned bank which is NBT bancorp.
Also add cover page to excel that looks like the following attached form

Hi. I already have the excel done I just need help fixing the formulas. The bala

Hi. I already have the excel done I just need help fixing the formulas. The balance sheet on the right in the excel the not highlighted one needs to have the exact numbers as the pictures I have below. EXACTLY the same. With formulas do not just copy paste. ASK IF YOU HAVE QUESTIONS. And also fix the chart

Your analysis of the case study bank will take a giant leap forward with Part II

Your analysis of the case study bank will take a giant leap forward with Part II. This part will
concentrate on regulatory attributes of soundness and liquidity for your assigned bank.
Banks don’t necessarily provide all info required for an outsider to run these calculations. Some
assumptions must be made. For example, a 30-day cash out flow can be assumed to be some credit
line funding, 1/12 of the total of CDs maturing this FY, and a proportion (5-10% runoff) of
deposits. (To get the amount of credit line fundings, check the bank’s OBS liabilities. For CD
totals, check notes for Deposits.)
Purpose:
Develop an understanding of the criteria of banking regulations, and the areas of particular interest
to regulators.
Instructions:
Use the bank data you compiled when you were doing Part I. Use the amended cover sheet
provided in this week’s files. Copy and paste the format to your existing cover sheet. After
completion of the calculations, determine if you believe the bank is in compliance. (They probably
are). Upon completion, upload your file to canvas. 50 points total.
Calculations Required (see PowerPoint “Global Banking Regulations and Standards”):
Capital Adequacy Ratio (CAR)
Liquidity Coverage Ratio
Net Stable Funding Ratio
Attached is calculations from Part 1 for assigned bank which is NBT bancorp.
Also add cover page to excel that looks like the following attached form

Please provide a commission calculation worksheet for the following transaction.

Please provide a commission calculation worksheet for the following transaction. The worksheet should include both the annual amount of commissions paid and the total commission the landlord will pay all brokers.
The terms of the lease are as follows:
Property – 1700 Pennsylvania Avenue, Washington DC
Tenant – ABC Company, a technology relocating from 1400 K Street, NW
Landlord – BIG Real Estate Company, a National REIT which recently purchased the building
Lease Type – Full Service Lease (including a base year for operating expenses and real estate taxes)
Area – 25,000 sq. ft on the 3rd floor of the Property.
Lease Commencement Date – July 1st, 2024
Rent Commencement Date – January 1, 2025
Lease Term – 15 Years from the Lease Commencement Date
Base Rent – $50.00 per sq. ft.
Annual Base Rent Escalation – 2.5% per year
Rent Increase – $3.00 rent increase in years 6 and 11 in lieu of the Annual Base Rent Escalation
Rental Abatement – 6 months of free rent in both year 1 and year 2
Tenant Broker’s Commission Rate – 3%
Landlord Broker’s Commission Rate – 2%
Payment Terms – The 1st half of the commission will be paid upon lease signing and the 2nd half will be paid upon the Lease Commencement Date.

Please provide a commission calculation worksheet for the following transaction.

Please provide a commission calculation worksheet for the following transaction. The worksheet should include both the annual amount of commissions paid and the total commission the landlord will pay all brokers.
The terms of the lease are as follows:
Property – 1700 Pennsylvania Avenue, Washington DC
Tenant – ABC Company, a technology relocating from 1400 K Street, NW
Landlord – BIG Real Estate Company, a National REIT which recently purchased the building
Lease Type – Full Service Lease (including a base year for operating expenses and real estate taxes)
Area – 25,000 sq. ft on the 3rd floor of the Property.
Lease Commencement Date – July 1st, 2024
Rent Commencement Date – January 1, 2025
Lease Term – 15 Years from the Lease Commencement Date
Base Rent – $50.00 per sq. ft.
Annual Base Rent Escalation – 2.5% per year
Rent Increase – $3.00 rent increase in years 6 and 11 in lieu of the Annual Base Rent Escalation
Rental Abatement – 6 months of free rent in both year 1 and year 2
Tenant Broker’s Commission Rate – 3%
Landlord Broker’s Commission Rate – 2%
Payment Terms – The 1st half of the commission will be paid upon lease signing and the 2nd half will be paid upon the Lease Commencement Date.

Hi. I already have the excel done I just need help fixing the formulas. The bala

Hi. I already have the excel done I just need help fixing the formulas. The balance sheet on the right in the excel the not highlighted one needs to have the exact numbers as the pictures I have below. EXACTLY the same. With formulas do not just copy paste. ASK IF YOU HAVE QUESTIONS. And also fix the chart

Your analysis of the case study bank will take a giant leap forward with Part II

Your analysis of the case study bank will take a giant leap forward with Part II. This part will
concentrate on regulatory attributes of soundness and liquidity for your assigned bank.
Banks don’t necessarily provide all info required for an outsider to run these calculations. Some
assumptions must be made. For example, a 30-day cash out flow can be assumed to be some credit
line funding, 1/12 of the total of CDs maturing this FY, and a proportion (5-10% runoff) of
deposits. (To get the amount of credit line fundings, check the bank’s OBS liabilities. For CD
totals, check notes for Deposits.)
Purpose:
Develop an understanding of the criteria of banking regulations, and the areas of particular interest
to regulators.
Instructions:
Use the bank data you compiled when you were doing Part I. Use the amended cover sheet
provided in this week’s files. Copy and paste the format to your existing cover sheet. After
completion of the calculations, determine if you believe the bank is in compliance. (They probably
are). Upon completion, upload your file to canvas. 50 points total.
Calculations Required (see PowerPoint “Global Banking Regulations and Standards”):
Capital Adequacy Ratio (CAR)
Liquidity Coverage Ratio
Net Stable Funding Ratio
Attached is calculations from Part 1 for assigned bank which is NBT bancorp.
Also add cover page to excel that looks like the following attached form

You need to submit the Excel workbook with all calculations, outputs and answers

You need to submit the Excel workbook with all calculations, outputs and answers to questions. The questions are in the business case document and answers must be applied to the attached excel file (TPW Forecast).

All the instructions are attached in the excel template, also use the specificat

All the instructions are attached in the excel template, also use the specifications that are listed in the google docs attachment to fill out the Quality Function Deployment template.

Starbucks in China
Starbucks (SBUX) started expanding in China in 1999 and curre

Starbucks in China
Starbucks (SBUX) started expanding in China in 1999 and currently have nearly 7,000 stores across the country. But if you look closer at their Chinese operations, you see that they have been struggling since 2020 due to China’s massive COVID lockdowns and the rebuilding of Chinese chain Luckin Coffee. Even so, Starbucks till plans to aggressively open new stores. You will be doing an analysis relating to new stores in China.
Approximate cost of opening a Starbucks shop is 6,500,000 CNY.
Assume your shop will serve 60,000 orders in year 1 Starbucks projects customer growth of 4% per year until year 5.
Assume the average price per order is 60 CNY for year 1. Assume the average price per order will grow by 3% per year.
Variable costs are expected to be 75% of revenues.
Depreciation is 4% of capital investment costs.
The corporate tax rate in China is 25%. There is currently no tax on remittances or no limits on remittances. Assume Starbucks will remit 100% of cash flows.
Assume they will not pay additional tax to U.S for remitted funds either.
Assume the terminal value of the operation (value if they planned to sell) is 8M CNY after-tax, net of book value.
The April 2024 exchange rate was CNY/USD 0.13826.
Using 5 years of cash flows and the current exchange rate for 5 years, calculate the NPV and IRR of the project using a 12% rate.
Customer growth rate is based on the historical China growth rate; however, future customer growth could be much lower due to added competition. Assume there is a 60% chance it will be 4%, but a 40% chance it will actually fall to -3%. (Keep the price growth rate at 3%).
Salvage value is also uncertain. Assume there is a 65% chance the salvage value will be 8M CNY and a 35% chance it will only be 6M CNY.
What is the weighted NPV for your business? Should they invest in the business?