2021年6月11日星期五

FMWC Jet Hockey Case Sharing

 Website: https://fmworldcup.com/sample-cases/

My model: https://docs.google.com/spreadsheets/d/113-XbwKMb20Ig1pYz603PaAtYigCpq3ho2uEc05CdMU/edit#gid=1255741047


This case is not hard and very interesting

Jet Hockey 
(300 points available + 50 extra points) 
QUESTIONS 
Questions 1-7 deal with the base case scenario. 
1. Which team will win the tournament? – 25 Points 
a. Borea 
b. Fantastan 
c. Jetlandia 
d. Ntise 
e. Oftome 
f. Ridesia 

2. How many days will the ordinary supporters of Konka team spend in Fantastan? – 25 Points 
a. 4 
b. 5 
c. 6 
d. 7 
e. 8 
f. 9 

3. How many fans will come to see the tournament? – 25 Points 
a. 83,840 
b. 84,840 
c. 85,840 
d. 86,840 
e. 87,840
f. 88,840 

4. How many supporters (excluding fans) will come to see the tournament? – 25 Points 
a. 545,379 
b. 556,379 
c. 567,379 
d. 578,379 
e. 589,379 
f. 600,379


5. How much money will the fans spend in total over the course of the tournament? – 25 Points 
a. $315,572,624 
b. $323,012,532 
c. $343,982,655 
d. $358,725,987 
e. $365,681,678 
f. $377,654,679 

6. How many teams are there that will have more money spent by the fans compared to the ordinary supporters (not including the teams that haven’t qualified for the finals)? – 25 Points 
a. 0 
b. 1 
c. 2 
d. 3 
e. 4 
f. 8 

7. How much money will be spent in total over the course of the tournament by both fans and  
ordinary supporters? – 50 Points 

a. $1,505,304,819 
b. $1,515,304,819 
c. $1,525,304,819 
d. $1,535,304,819 
e. $1,545,304,819 
f. $1,555,304,819 

Questions 8-9 deal with the alternative scenario. Please change the seeding accordingly – see  Assumptions tab for details (1 – Zamlos, 2 – Yates, 3 – Xerxes,… 16 - Fantastan). 
8. Under the new assumptions, how much money will be spent in total over the course of the  
tournament? – 50 Points 

a. $2,000,679,542 
b. $2,100,679,542 
c. $2,200,679,542 
d. $2,300,679,542 
e. $2,400,679,542 
f. $2,500,679,542


9. Under the new assumptions, which team’s fans and supporters will spend the most money over the course of the tournament? – 50 Points 
a. Menand 
b. Queenslandia 
c. United Malaudines 
d. Vanustan 
e. Wala 
f. Zamlos 

Question 10 is a bonus question, dealing with yet another scenario. Please change the seeding accordingly – see the Assumptions tab for details. 
Under this question, the 16 strongest teams qualify for the tournament. Seed these teams yourself  in such a way that the total overall amount of money spent is maximized.  

10. How much money will be spent in total over the course of the tournament? – 50 Points 
a. Type in your answer rounded to the nearest integer (e.g., 1234567890). When uploading your model, please leave it showing the exact seeding for Q10 to get the  bonus points.


My ans:

Total3,581,112,130
SeedCountry
1Honvaroc
2Yates
3Siare Condivesi
4Vanustan
5Menand
6Turlandia
7United Malaudines
8Zamlos
9Borea
10Queenslandia
11Ridesia
12Wala
13Abragda
14Xerxes
15Greyland
16Jetlandia


Google Sheets 簡易按揭計算及自製還款表

 利用Google Sheets  內建函數, 我們可簡單地計算壓力測試及計算按揭, 所用到的函數如下:

  1. CUMIPMT: 計算總利息支出
  2. PMT: 計算每月供款 (少於人工50% 即通過壓力測試)
  3. PPMT: 計算特定期數之償還貸款額(本金)
  4. IPMT: 計算特定期數之償還貸利息
就是以上4組公式, 以下看一個例子:

樓價6,000,000
利率2.50%
按揭成數60
貸款額3,600,000
首期2,400,000
年期20


總利息支出= CUMIPMT(利率/12,年期*12,貸款額,開始期數 (即1),完結期數(即年期*12),0)

= - 978,361 (負數意思是你的支出)

月供= PMT(利率/12,年期*12,貸款額)

       = - 19,077 (壓測就是: 19077*2 = 38154)


第n期還款細明:

償還貸款= PPMT(利率/12, n , 年期*12 ,貸款額,0,0)

償還利息= IPMT(利率/12, n , 年期*12 ,貸款額,0,0)


Google 試算表網址:

https://docs.google.com/spreadsheets/d/1pckkhm6BLnEXpHmBsF-LS5pilp7HTax6miPeS_zCpEY/edit#gid=0


由計算可知, 總利息支出與年期成指數增長, 超過35年的還款年期中一半以上還款都是還利息的,實在難以想像: