Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Commerce 3FD3: Financial Modelling

ASSIGNMENT 1

Assume that you are about to graduate with a Bachelor of Commerce from the DeGroote School of Business, McMaster University. Your job search was successful, and you have hired on as a financial analyst at the Royal Bank of Canada. Your starting salary will be 55K/year, and you will start your new job a month after you graduate.

To get ahead, you have decided to keep your part time gig at a well-known bar/restaurant. You intend to replace your existing car with a new one and expect that the money earned from this part time gig will cover the lease on a Mini 5-Door, made by BMW. You hope to sell your existing car for $2,500 and use the money as a downpayment on the lease. The part time gig pays minimum wage, and averages around 8 hours/week. https://mini.ca/en/shopping/buildandprice

The Mini 5-Door will be a Mini Cooper Premier 2; Island Blue Metallic, White Roof and Mirror Caps, White Bonnet Stripes, 16” Revolite Spoke Wheel Package, Carbon Black Punch Leatherette, 7-Speed Double Clutch Transmission, with Roof Rails.

The Price Car Configurator assumes a 48-month lease, 8K km/year, and zero downpayment. You intend to put up the sales proceeds of the old car as a downpayment and increase milage to 20K km/year.   

The Price Car Configurator assumes an 84-month loan, at 6.99%, and zero downpayment. You intend to put up the sales proceeds of the old car as a downpayment and reduce the loan term to 72 months.  

 Questions:

1. What is the impact on the lease payment, if the downpayment and mileage extension to 20K km/year is added? What is the impact on the bi-weekly loan payment, if the downpayment and term reduction is added?

2. What is the future value of this Mini Cooper Premier 2 at the end of the 48-month lease? Build a table to prove that the Price Car Configurator payment on the 48-month lease is accurate.

3. If the resale value of this Mini Cooper Premier 2 at the end of the 72-month loan term was the same as the loan future value, would you get your original down payment back? Would this still be the case if inflation were increasing the value of your downpayment at 3.5% per year during the 6 years you were driving this Mini Cooper Premier 2?

4. Assume you have a tax rate of 29.65%. How many hours per week would you need to work at this part-time job in order to pay the monthly cost of this Mini Cooper Premier 2, on a 4-year lease, with a $2,500 down payment, and 20K km/year? Were you to change gigs and earn an additional 25% in tips, would that change your decision?

5. Name two other factors that might influence your decision, and why.

----- *** ----- 

What do you need to do? 

1. Establish your needs.

o Estimated take-home pay of the part-time gig, before and after tips. 

o Navigation of the mini.ca website, and the Price Car Configurator.

o Payment is your difference in take home pay, versus what you would have got.

2. Collect the inputs.

o Re-use the minimum wage, CPP, EI, tax information in the maximum student loan exercise. Assume that tips are subject to CPP, EI, and tax deductions.

o Follow the assignment specifications and print out the lease and loan payments for different configurations.

3. Design/build your model.

o Calculate what the part time gig take-home pay is, with/without tips.

o Use the finance functions to determine/prove the various asks.  

o Use the results to answer the questions.

  ----- *** ----- 

What does the grader need to see?

For Questions 1-4. The answer to the question plus an MS Excel Worksheet supporting the numbers that you have come up with.

For Question 5. At least two different factors, and a valid explanation for each.