UTS 25624 Financial Metrics for Decision Making
General information and instructions
• This individual assignment accounts for 40% of your final grade.
• You must complete this assignment on an individual basis.
• Deadline: 13th May 2021, 5pm Sydney time
• You need to submit 2 files via Canvas:
1. written report
– .pdf format only
– The written report should be no longer than 5 pages. The appendix, refer-
ences, and cover sheet (if you use any of them) are not included in the 5-page limit.
2. Excel spreadsheet
– The spreadsheet must contain all calculations you perform for the tasks.
– Format your spreadsheet so that it can be reasonably understood (i.e., use colors, headers, etc.).
• All assignment-related queries must be posted on the Canvas discussion board. E-mail queries related to the assignment will not be entertained.
• Both the written report and Excel spreadsheet will be graded.
• 70% of the assignment grade goes towards accuracy of the content and analysis. The remaining 30% goes towards effectiveness of communication and presentation.
• Late submissions will receive zero credit unless a special consideration is filed.
• In the Assignment page on Canvas, you will find an Excel spreadsheet that contains the data you need to complete the assignment tasks.
• There are 3 worksheets in the spreadsheet.
• Perform your calculations based on the data in these spreadsheets.
1. Refer to the “Hypothesis testing” worksheet.
As a data analyst at a telecommunications company, you want to understand the determinants of customer loyalty. As a first pass, you want to compare the average “tenure” (number of months that a customer has remained with the company) across different cuts of the customer base.
You have access to a sample of customers’ demographics and their tenures. There are 3 variables in this sample. i Gender
• Male or Female
ii Senior citizen
• A dummy variable that equals one if the customer is a senior citizen, and equals zero otherwise
• Number of months that a customer has remained with the company
Perform the following tasks.
(b) [5 marks] Your coworker believes that male customers are more loyal than female customers, on average. Correspondingly, write down the null and alternative hypotheses.
(c) [5 marks] Perform a hypothesis test of (a). Given a 5% significance level, what is your conclusion?
(d) [5 marks] Another coworker thinks that age is related to customer loyalty. Specifically, he believes that male senior citizens and female senior citizens have different levels of customer loyalty, on average. Correspondingly, write down the null and alternative hypotheses.
(e) [5 marks] Perform a hypothesis test of (c). Given a 5% significance level, what is your conclusion?
2. Refer to the “Modelling” worksheet.
You are interested in either buying or leasing a car. The price of the car is $50000.
If you buy the car, you have to make a downpayment of 30% of the car price today. Thereafter, you will finance the balance with a 3-year car loan at 5% per annum.
If you lease the car, you do not have to make the downpayment. The monthly lease is $850. At the end of 3 years, you plan to purchase the car from the car-leasing firm by paying a residual value of $25000.
You have access to an investment product that yields 6% per annum.
The first loan repayment or first lease payment would occur at the end of the next period (i.e., Time 1 in the worksheet).
(a) [15 marks] Construct a financial model in the “Modelling” worksheet. Headers are provided in the worksheet to guide you, but you need not use them. Suppose you choose to lease the car, how much savings would you have at the end of the term?
(b) [5 marks] Suppose the car-leasing firm decides to raise the monthly fee to lease the car. Keeping constant all other model parameters, what is the minimum monthly lease such that buying the car is financially more attractive?
3. Refer to the “Regression analysis” worksheet.
To understand the effectiveness of its marketing strategies, ABC Co. tracks its advertisement expenditures and sales in every marketing campaign.
There are 3 variables contained in the dataset provided by ABC. i Influencer type
• Macro or Nano ii Sales ($ thousands)
• Sales from the marketing campaign iii Social media ($ thousands)
• Advertisement expenditures on social media of the marketing campaign
(a) [5 marks] Estimate a multiple linear regression model in which the dependent variable is “Sales” and the independent variables are “Influencer type” and “Social media”. What is the estimated regression model? For example, write:
Sales = 0.1 + 0.2 × Macro + 0.3 × Social Media
(b) [5 marks] Examine the estimated coefficients from the regression model in (a). Are they statistically significant at the 5% level? How do you interpret them?
(c) [5 marks] The marketing manager believes that Macro influencers provide a greater return to sales per advertisement dollar spent. Estimate a multiple linear regression model to test whether the empirical data supports his beliefs. Write down the estimated regression model.
Hint: You need to construct a new independent variable.
(d) [5 marks] Do the estimated results of the regression model in (c) support the marketing manager’s belief? Why?