Recent Question/Assignment

I need this case study done .Could please show all the step and answers the question on the word document and excel sheet.Please do an analysis APA Format
To complete the project, please follow the STEP BY STEP INSTRUCTIONS on page 3-5. Instructional videos are available for most steps. The team picks its industry to analyze. In your written analysis, please include:
I have chosen this industry SIC 50 - Wholesale Trade - Durable Goods
• Why you select this industry?
• Data cleaning process (e.g., beginning sample, ending sample)
• Report and discuss your results of step 5, step 7, step 8 and step 9
Step 1. Select an industry and limit the whole sample to 2-digit SIC based on the industry you selected
(Please do not select SIC 29 - Petroleum Refining and Related Industries)
Watch instructional video: Limiting Sample by 2 Digit SIC codes
Search SIC codes by industry:
Step 2. Delete all observations with missing pretax income and income taxes – total.
Watch instructional video: Eliminating Observations with Missing Data
Step 3. Created correct tax year.
Watch instructional video: NEW METHOD Creating Tax Year
Step 4. Calculate variables
a. Effective Tax Rate (ETR)
b. Capped Effective Tax Rate (CETR)
c. Fed%; St%; Foreign%
Watch instructional video: Creating the ETR variable
Variable Calculation Issues

Step 5. Calculate basic statistics: ETR, CETR, Fed%, St% and Foreign%:
a. mean
b. median
c. standard deviation
d. minimum and maximum
e. 1st and 99th percentiles
f. 1st and 2nd deciles
g. 1st and 3rd Quartiles
Watch instructional video: Calculating Variable Statistics

Step 6. Create pivot tables
a. Average Annual ETR and Average Annual CETR
b. Average Annual Net Income and Average Annual Pre-Tax Income
c. Average Annual Percentage of Tax Expense from Federal, State and Foreign Taxes
Watch instructional video: Table of Mean ETR by Year
Watch instructional video: Creating Federal%, State%, and Foreign% Annual Table
Step 7. Use pivot table to evaluate (hint: average annual ETR by Ticker Symbol)
a. Which firm paid the highest annual ETR during the sample?
Which firm paid the lowest annual ETR during the sample?
(search the firm name on Google based on its Ticker Symbol)
b. Look at the firm’s 10-K for explanation. (bonus credits)

Step 8. Plot your findings and analyze the graphs you plotted
a. Average Annual ETR and Average Annual CETR
Insert a trend line in both.
b. Average Annual Net Income and Average Annual Pre-Tax Income
c. Using the dual-axis function, create a figure which overlays the Average Annual ETR (or CETR) and Average Annual Pre-Tax Income.
d. Average Percentage of Tax Expense from Federal, State and Foreign Taxes
All three variables plotted on the same, single figure.
Watch instructional video: Figures with Trendlines
Watch instructional video: Creating Figures with a Right Axis and a Left Axis

Step 9: HYPOTHESIZE and Estimate Regressions:
What factors influence your industry’s ETR? Is it a positive effect or negative effect?
a. Select a minimum of 5 Independent Variables to test (e.g. Capital Expenditures, Assets, Long-term Debt etc.)
b. Provide BASIC STATISTICS for your new variables.
c. Present your regression findings
Dependent Variable = ETR and CETR
Independent Variables = Your 5 new variables that may influence the ETRs