final excess case . it might take long but i need 100% on it

Your answer sheet should use a professional memo format and incorporate any relevant data via tables or charts. Your memo and supporting data must address all questions and will be graded for both content accuracy and professionalism.

PART A: You will use the Excel file called “Employee Data” and will need to have the Analysis Toolpak add-in installed in Excel. It can be found under the Tools tab. If it does not appear, select Excel Add-ins under the Tools tab, and then check Analysis Toolpak.

1)From the Excel ribbon, select Data and then Data Analysis. From the pop-up window, choose Descriptive Statistics, then click OK. Select the salary column as the input range and check the box for labels in first row. Choose “New Worksheet” as the output option, click summary statistics, and click OK. Report the following:

• Mean (average) salary
• Median salary
• Minimum salary
• Maximum salary
• Number of salary observations in the database

• Place your cursor anywhere in the table of data. From the Excel ribbon, select Insert and then Pivot Table. The entire table should be selected automatically along with the choice to output the PivotTable to a new worksheet. Select OK. From the PivotTable Fields section, select and drag “Gender” and “Minority” to the “Rows” box below. Select and drag the variable “Education” to the “Columns” box. Select and drag the variable “Salary” to the “Values” box. Change the sum of salary to the average of salary by clicking the “i” icon to the right of the “Sum of Salary,” choosing “Average,” then clicking OK. Report the following:
• From the Excel ribbon, select Data and then Data Analysis. From the pop-up window, choose “Regression,” then click OK. Select values in the “Salary” column as the Input Y Range and values in the columns for “Gender” through “Education” for the Input X Range. Choose “New Worksheet” as the output option, then click OK. Report the following:

• Does additional education appear to be associated with a higher average salary?
• Do males (1) or females (0) appear to earn higher average salaries?
• Do minorities (1) or nonminorities (0) appear to earn higher average salaries? Does this hold for both genders?
• What is the average salary of the entire population? What is the average salary of the entire population of males? What is the average salary of the entire population of females? What is the average salary of the entire population of male minorities? What is the average salary of the entire population of female minorities?

• A measure on how well the independent variables gender, minority, and education are able to explain the variation in average salary is the adjusted R Squared. What percentage of the variation in average salaries is described by these variables?
• The t Stat is a measure of how an individual independent variable explains variation in the dependent variable average salary. An absolute value greater than 2 is generally considered a significant value in explaining variation. What do the t Stats tell us about the ability of the variables gender, minority, and education to explain average salary?

PART B: You will use the Excel file called “Warranty Data” and the National Highway Traffic Safety Administration (NHTSA) of the United States Department of Transportation website about safety recall information https://www.nhtsa.gov/recalls#vehicle to answer some questions related to warranty expenses in the auto industry. The file consists of sales and warranty expense data for 16 car models from 2004 to 2018 (note that the time periods vary by car model).

Most if not all automobiles and trucks sold are subject to some kind of manufacturer warranty, which covers manufacturing defects in factory-supplied materials or factory workmanship. According to recent estimates, global warranty spending in the automotive industry alone surpasses $25 billion annually.

Warranty expense is recognized in the same period as the sales for the products that were sold. Warranty costs can be wildly unpredictable, and thus, GAAP allows warranty issuers to make reasonable estimates of the costs of meeting product warranties. A company may determine the historical percentage of warranty expense to sales for the same types of vehicles, apply the same percentage to the sales for the current accounting period to derive the warranty expense to be accrued. If there’s no change in product quality, the percentage rate should remain relatively unchanged and then warranty accruals should remain proportional to sales. No sale, no warranty. However, this amount may be adjusted to account for unusual factors related to the vehicles that were sold, such as initial indications that a recent model has an unusually high repair cost.

When a company initially underestimates its warranty costs, it has to later adjust its estimates and add more funds to cover the increased cost of warranty work. Most of the time, these adjustments are minor, but during a crisis they can become as large as the regular amounts. Customer complaints, recall, high claim costs are all indictors that the manufacturers may need to adjust the account to avoid underestimation of warranty expense.

Use the “Warranty Data” Excel file to answer questions 1 to 4. Use both the Excel file and the NHTSA website to answer question 5.

1)Compare by company/brand (e.g., Ford vs. GM), model, and country across years and identify which has the highest and lowest current year sales, current year warranty expenses, and current year warranty claims. Note that you have to ensure the comparison is meaningful.

2)Do you see any correlation between current year sales and warranty expense? Please explain.

3)How much is current year warranty expense as a percentage of current year sale by company, model, and country? Identify the lowest and highest.

4)Do you see any trend for warranty expenses and warranty claims? Do you observe any obvious patterns? What conclusion can you draw based on the data from 2004 to 2018?

5)Go to the NHTSA website. Identify the number of recall and complaints for each car model. Do the number of complaints and recalls justify the amount of current year warranty expenses related to each model? Did the company underestimate or overestimated the amount of current year warranty expenses or is it just about right?

6)Many car manufacturers sell extended warranties. For example, Toyota allows car owners to buy an extended warranty up to eight years or 125,000 miles. How should we recognize these transactions and how do these transactions affect our warranty liabilities or claim amounts?

Use the “Warranty Data” Excel file to create Pivot Tables for these five accounts – sales, warranty expense current year, warranty expense prior year, warranty claims current year, and warranty claims prior year – to answer questions 7 to 9.

7)Provide separate tables and graphics to show total sales, warranty expense current year, warranty expense prior year, warranty claims current year, and warranty claims prior year by company, model, country and year (Use “summarize values by sum”).

8)Provide separate tables and graphics to show average sales, warranty expense current year, warranty expense prior year, warranty claims current year, and warranty claims prior year by company, model, country and year (Use “summarize values by average”).

9)Provide separate tables and graphics to show the variance of sales, warranty expense current year, warranty expense prior year, warranty claims current year, and warranty claims prior year by company, model, country and year (Use “summarize values by var”).