代写ACIS 2504、代做Java/C++编程
ACIS 2504 Copyright
ACIS 2504 Course Project
Step 1:
Each team picks a company for analysis. The company you choose must be a current, non-financial (i.e.,
the Standard Industry Classification (SIC) Code is NOT in 6000 – 6999) firm with at least 20 years of data
available from the dataset provided. You also cannot choose firms that have been analyzed last year (check
the list).
Step 2:
Please complete the data analytics project below. You need to turn in your group’s PowerPoint slides and
the Excel / Power BI files that made the tables and charts for your presentation by 11:59 PM on June 6
(Monday). Prepare for a 20-minutes in class presentation during the last week of this course. For each type
of file, only submit one file for the whole group. No individual file is accepted. Please name your file
consistently in the following way: ACIS2504_Class 1(2)_GroupX. Please write everyone’s name on the
title page of your PowerPoint slides.
The Project: Imagine you are the Vice President (VP) of a major Wall Street bank that provides US
companies syndicated loans. The bank is currently evaluating a potential borrower – the company you
picked, for a working capital loan. You are asked by the bank to perform a data analytics project to better
understand this company’s creditworthiness and aid lending decision. You are provided with a data set
pulled from Compustat North America to aid your analysis1
.
Q1. Extract-Transform-Load (ETL).
• Show me your cleaned final dataset that is used to conduct your analysis
✓ Tip 1: You only need data of the company you choose and data of companies that are in the same
industry as the company of your choice.2
✓ Tip 2: Drop Canadian firms (i.e., Currency Code = CAD).
Q2. Briefly present the company’s business and its industry standing.
• Provide a brief overview of the company’s core business, its main industry and identify major
competitors of the company (e.g., top 3).
• Do a market share analysis. Use Pivot Table to show sales by company for the most recent year in the
data (show value as both sales amount and % of grand total). Interpret your findings.
Q3. Understand the company’s key financial covenant ratios
• Financial covenant ratios are commonly used by banks to evaluate and monitor a borrower’ credit risks.
Apply information modeling algorithms to calculate the following 5 financial covenant variables:
Financial Covenant Ratio Definition
InterestCoverage EBITDA / InterestExpense
Debt-to-EBITDA (CurrentDebt+LongTermDebt)/EBITDA
1 Compustat North America provides standardized US and Canadian financial statement and market data for over
80,000 active and inactive publicly traded companies over 50 years.
2 Companies with the same SIC code are considered as belonging to the same industry.
ACIS 2504 Copyright © Liang Tan 2
Leverage (CurrentDebt+LongTermDebt)/Assets
CurrentRatio CurrentAssets/CurrentLiabilities
TangibleNetWorth Assets – Liabilities - Intangibles
• Provide descriptive statistics report for the five financial covenant variables for the company (using the
entire time-series available). Report the following statistics: Mean, Median, Standard deviation,
Kurtosis, Skewness, Min, Max, and Number of observations. Interpret your findings and provide
insights.
• Now, focus on Debt-to-EBITDA ratio only (use all data available). Provide box-and-whisker
visualization that compares the company with its major competitors and the rest of the industry.
Interpret your findings and provide insights.
• Again, focus on Debt-to-EBITDA ratio and provide visualization to identify trend of the company’s
financials over time and compare it against its major competitors (examine the most recent 6 years).
Interpret your findings and provide insights.
✓ Tip 1: Create a “category” field that contains the following values: your company, the major
competitors and “others”.
✓ Tip 2: #DIV/0! need to be dropped.
✓ Tip 3: Consider to trim the top and bottom 1%-3% of extreme values (outliers).
Q4. Predict the company’s cash flows
• A major consideration for a bank to make lending decisions is to evaluate whether the borrower has
enough cash to pay back the loan. Prepare histograms to show distributions of the company’s operating
cash flow, net income and EBITDA. Interpret your findings and provide insights.
• You were asked to use net income and EBITDA to separately predict operating cash flow for the
company.
1. Run correlation among net income, EBITDA and operating cash flow. Interpret your results
2. Run regression analysis for the following two models.
➢ Model 1: use Net Income to predict operating cash flow.
➢ Model 2: use EBITDA to predict operating cash flow.
3. Generate scatter plots with trendlines for each of the above two models. Show equation and R2
.
Interpret your results and provide insights. Which performance measure (i.e., Net Income vs. EBITDA)
do you think can better explain operating cash flow?
Q5. Understand the company’s bankruptcy risks
• Altman’s Z-Score is commonly used to gauge a company’s bankruptcy risk. In general, a score below
1.8 indicates that it’s likely the company is headed for bankruptcy, while companies with scores above
3 are not likely to go bankrupt.
Altman Z-Score = 1.2×(working capital/total assets) + 1.4×(retained earnings/total assets) +
3.3×(EBIT/total assets) + 0.6×(market value of equity/total liabilities) + (sales/total assets).
3
• Prepare descriptive statistics of Altman’s Z-Score for the company and compare it with its major
competitors and the rest of the industry (using the entire time-series). Report the following statistics:
Mean, Median, Standard deviation, Kurtosis, Skewness, Min, Max, and Number of observations.
Interpret your findings and provide insights.
• Provide visualization to show trend of Z-Score over years for the company and its major competitors.
Interpret your findings and provide insights.
3 Market value of equity can be calculated using closing price for the year multiplied by common share outstanding.
ACIS 2504 Copyright © Liang Tan 3
• Run a regression analysis using the 5 financial covenant ratios from question 3 to predict bankruptcy
risks (z-score as the dependent variable) for the company. Are these ratios good indicators for
bankruptcy? Explain your findings.
Q6. Should your bank lend money to the company based on your analysis? Explain your rationale.
• Based on your analyses, do you think your bank should lend money to this company? Imagine you are
going to present your proposal to your boss. Your boss is very busy and has only a few minutes to hear
what you have to say. Use a dashboard technique to integrate and highlight key performance indicators
and other insights you find into one visualization (You choose what to show on the dashboard. Refer
to Illustration 1.5 and 7.43 in the textbook for examples of a dashboard) and try to convince him (and
us – the audience of your presentation) of your proposal (i.e., whether lend or not lend to this firm).