Originally published: 05/07/2018 12:33
Publication number: ELQ-15649-1
View all versions & Certificate
Publication number: ELQ-15649-1
View all versions & Certificate
2017 Round 2, Section 1: Collect the Cash
Excel training/competition model from the 2017 Financial Modeling World Championships
by Full Stack ModellerTo help finance professionals transform their Excel, financial modelling, data visualisation & analytics skillsFollow 75
Description
This workbook is taken from the Second Round of the 2017 Modeloff financial modeling world chamionships.
Follow the instructions in order to complete the model:
You are preparing a daily cash flow revenue forecast for a company that has 8 customers (Company A –
Company H), all with very specific payment terms. No expense calculations are required.
The questions all relate to the 2018 calendar year, but you may need to model before/after this period to
accurately calculate the cash forecast (calculations should include the relevant days in 2017 and 2019
where the payment methodology requires it).
Do not round your answers at any stage in the model.
A weekday is defined as a Monday, Tuesday, Wednesday, Thursday or Friday. A weekend is defined as Saturday and Sunday. There are no holidays to consider.
You have been provided with an excel workbook file with the ModelOff formatting to use. This file contains
none of the inputs provided below – you will need to input these yourself.
COMPANY A
Company A pays a monthly figure of $17.3m. This is paid in 4 segments each month – 11% on day 5,
21% on day 10, 41% on day 18 and the balance on day 23. Payments are made on the days specified
even if it is a weekend.
COMPANY B
Company B has a daily charge of $0.35m that accrues daily. The accrued balance is paid monthly on the third Thursday of the month.
For example, the payment for January 2018 has accruals from the third Friday in December 2017 until the third Thursday in January 2018 (inclusive).
COMPANY C
Company has a daily charge of $0.18m per day. On weekdays the charge is paid on the same day, on the
weekends it accrues and is paid on the Monday.
COMPANY D
Company D has a daily charge of $0.23m. This is paid once per month on the 15th. The monthly payment is based on the number of days in that calendar month. Payments are made on the day specified even if it is a weekend.
COMPANY E
The total monthly charge for Company E is calculated based on the number of Tuesdays in the month at a rate of $1.96m per Tuesday. This is paid evenly across all the weekdays of that month.
Round 2 Section 1 - Case Study Information Pack
2015 Round 1 – Question Breakdown and Style Guide (120 Marks, 120 Minutes)
COMPANY F
Company F makes 3 payments per month, each of $5.34m. They are paid on the 7th, 14th and 20th of the month. However, if the payment date is a weekend, it is instead paid on the Friday before the weekend.
COMPANY G
Company G makes a single monthly payment of $9.23m. On even months (February, April etc) it is paid on the first weekday of the month. On odd months (January, March etc), it is paid on the third weekday of
the month.
COMPANY H
Company H makes a payment of $1.79m every Wednesday. However, if the payment falls on the first 10 days of the month it is instead paid 1 day earlier. If the payment falls on the last 10 days of the month, it is instead paid one day later.
For Questions 1 – 9, 11, select your answer from a multiple choice list.
For Questions 10 and 12, you are required to type in your answer.
Prepare your model and then use it to answer the given questions.
Allotted time: 40-60 Minutes
Once finished, feel free to upload your model to your own dedicated author channel!
This workbook is taken from the Second Round of the 2017 Modeloff financial modeling world chamionships.
Follow the instructions in order to complete the model:
You are preparing a daily cash flow revenue forecast for a company that has 8 customers (Company A –
Company H), all with very specific payment terms. No expense calculations are required.
The questions all relate to the 2018 calendar year, but you may need to model before/after this period to
accurately calculate the cash forecast (calculations should include the relevant days in 2017 and 2019
where the payment methodology requires it).
Do not round your answers at any stage in the model.
A weekday is defined as a Monday, Tuesday, Wednesday, Thursday or Friday. A weekend is defined as Saturday and Sunday. There are no holidays to consider.
You have been provided with an excel workbook file with the ModelOff formatting to use. This file contains
none of the inputs provided below – you will need to input these yourself.
COMPANY A
Company A pays a monthly figure of $17.3m. This is paid in 4 segments each month – 11% on day 5,
21% on day 10, 41% on day 18 and the balance on day 23. Payments are made on the days specified
even if it is a weekend.
COMPANY B
Company B has a daily charge of $0.35m that accrues daily. The accrued balance is paid monthly on the third Thursday of the month.
For example, the payment for January 2018 has accruals from the third Friday in December 2017 until the third Thursday in January 2018 (inclusive).
COMPANY C
Company has a daily charge of $0.18m per day. On weekdays the charge is paid on the same day, on the
weekends it accrues and is paid on the Monday.
COMPANY D
Company D has a daily charge of $0.23m. This is paid once per month on the 15th. The monthly payment is based on the number of days in that calendar month. Payments are made on the day specified even if it is a weekend.
COMPANY E
The total monthly charge for Company E is calculated based on the number of Tuesdays in the month at a rate of $1.96m per Tuesday. This is paid evenly across all the weekdays of that month.
Round 2 Section 1 - Case Study Information Pack
2015 Round 1 – Question Breakdown and Style Guide (120 Marks, 120 Minutes)
COMPANY F
Company F makes 3 payments per month, each of $5.34m. They are paid on the 7th, 14th and 20th of the month. However, if the payment date is a weekend, it is instead paid on the Friday before the weekend.
COMPANY G
Company G makes a single monthly payment of $9.23m. On even months (February, April etc) it is paid on the first weekday of the month. On odd months (January, March etc), it is paid on the third weekday of
the month.
COMPANY H
Company H makes a payment of $1.79m every Wednesday. However, if the payment falls on the first 10 days of the month it is instead paid 1 day earlier. If the payment falls on the last 10 days of the month, it is instead paid one day later.
For Questions 1 – 9, 11, select your answer from a multiple choice list.
For Questions 10 and 12, you are required to type in your answer.
Prepare your model and then use it to answer the given questions.
Allotted time: 40-60 Minutes
Once finished, feel free to upload your model to your own dedicated author channel!
This Best Practice includes
1 excel workbook and 1 PDF
