Recent Question/Assignment
Hello,
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
Description
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
https://edtech.msl.duq.edu/Mediasite/Channel/etr/watch/71173baa09694e9497383e10b16ab77f1d
Search SIC codes by industry:
https://www.naics.com/sic-codes-industry-drilldown/
Step 2. Delete all observations with missing pretax income and income taxes – total.
Watch instructional video: Eliminating Observations with Missing Data
https://edtech.msl.duq.edu/Mediasite/Channel/etr/watch/1a1b508c78694722bef4b3b236c7a0f61d
Step 3. Created correct tax year.
Watch instructional video: NEW METHOD Creating Tax Year
https://edtech.msl.duq.edu/Mediasite/Channel/etr/watch/30b52284adbc489cb449c043f4b8bb541d
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
https://edtech.msl.duq.edu/Mediasite/Channel/etr/watch/e3e1e7fd09404d1fa7d72b7db52ff23b1d
Variable Calculation Issues
https://edtech.msl.duq.edu/Mediasite/Channel/etr/watch/44fa3af618614c9f8c75bd4db8cf522d1d
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
https://edtech.msl.duq.edu/Mediasite/Channel/etr/watch/bf397709e791451db3003b605832b4ce1d
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
https://edtech.msl.duq.edu/Mediasite/Channel/etr/watch/29d93d28992d40b2a7a37a9380907eea1d
Watch instructional video: Creating Federal%, State%, and Foreign% Annual Table
https://edtech.msl.duq.edu/Mediasite/Channel/etr/watch/cd1c90f3f5a346a491ad2130c106ed381d
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
https://edtech.msl.duq.edu/Mediasite/Channel/etr/watch/831f901d5577428f852f18e6770961641d
Watch instructional video: Creating Figures with a Right Axis and a Left Axis
https://edtech.msl.duq.edu/Mediasite/Channel/etr/watch/1c895c5d297a4e4a8f6463e809856ed11d
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