Reference no: EM133793769

Project 1
Ch07_HOE_Salaries

Project Description:
You are an assistant accountant in the Human Resources (HR) Department for Home Protection, Inc., a company that sells smart home security systems to residential customers. Home Protection, Inc., with locations in Atlanta, Boston, Chicago, and Cleveland, has a manager at each location who oversees several account representatives. You have an Excel workbook that contains names, locations, titles, hire dates, and salaries for the 16 account representatives and 4 managers. To prepare for your upcoming salary analyses, you downloaded salary data from the corporate database into the workbook.
The HR director wants you to perform several tasks based on locations and job titles. You will use date functions to identify the year each employee was hired and how many years they have worked for the company. In addition, you will use logical functions to calculate annual bonus amounts. Next, you will insert math and statistical functions and a map to help analyze the data. Finally, you will review the financial aspects of automobiles purchased for each manager.

Steps to Perform:

Step Instructions Points Possible
1 Start Excel. Download and open the file named Exp22_Excel_Ch07_HOE_Salary.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 Your first task is to extract the year hired from the hire date for each employee.

In cell F7 of the 1-Date Logic worksheet, insert the YEAR function to identify the year from the date stored in cell E7. Use the fill handle to copy the function from cell F7 to the range F8:23 using the Fill Without Formatting option. 5
3 Your next task is to calculate how long each manager and representative has worked for the company.

In cell G7, insert the YEARFRAC function using the Hire Date and Last Day of the Year. Use relative and mixed references correctly. Use the fill handle to copy the function in cell G7 to the range G8:G23 using the Fill Without Formatting option. 5
4 You want to identify which day of the week each employee was hired. To do this, you will insert a WEEKDAY function to return an integer representing the weekday.

In cell H7, insert the WEEKDAY function using the Hire Date. Copy the function from cell H7 and paste to the range H8:H23 using the No Borders paste option. 5
5 You will use a custom number format to display the result as a weekday instead of an integer.

Select the range H7:H23, apply the custom number format dddd, and apply left horizontal alignment. 3
6 Column B contains the city location for each account rep and manager. However, you also want to display the regions. Atlanta is in the South, Boston is in the Northeast, and Chicago and Cleveland are both in the Midwest. You will use the SWITCH function to identify which region each city is in.

In cell C7, insert the SWITCH function to identify the city in cell B7. Switch Atlanta for South (cell C2), switch Boston for Northeast (cell C4), switch Chicago for Midwest (cell C3), switch Cleveland for Midwest (cell C3). Insert city names as text strings, and use mixed references to the cells containing the regions. Copy the function from cell C7 and paste to the range C8:C23 using the paste Formulas option. 5
7 Your next task is to calculate the annual bonus amount for each employee. The company uses a tiered bonus system that awards a specific percentage of salary based on hire date.

In cell J7, insert the IFS function to test if the Hire Date is before 1/1/2013. If so, the bonus is 8% of the salary. If the Hire Date is before 1/1/2018, the bonus is 4% of the salary. If the Hire Date is before 1/1/2023, the bonus is 2% of the salary. If none of the conditions are met, the bonus is 0. Use mixed references to the dates in the range J2:J4 and bonus percentages in the range K2:K4. Copy the function from cell J7 and paste it in the range J8:J23 using the paste Formulas option. 5
8 The HR director recommends that the company pay managers at least $80,000.

In cell K7, insert an AND function that evaluates if the the Title is Manager and if the Salary is less than $98,000. Insert the title as a text string and use a mixed reference to the $98,000 in cell G3. Nest the AND function as the logical test within an IF function. If the results of the AND function are true, display the text Due for raise. If the results are false, display the text N/A. Use the fill handle to copy the function to the range K8:K23 using the Fill Without Formatting option. 5
9 You want to calculate the number of employees in each state. You set up a worksheet with a summary section to calculate statistics by states, indicated by the state abbreviations.

Display the 2-Stats Map worksheet. In cell J3, insert the COUNTIF function to count the number of state abbreviations in the State column that match the state abbreviation in cell I3. Use mixed references to the range and relative reference to the criteria state abbreviation. Copy the function from cell J3 to the range J4:J6. 5
10 Next, you want to calculate the total payroll by summing employee salaries in each state.

In cell K3, insert the SUMIF function to total the Salary column if the State column meets the state abbreviation criteria in cell I3. Use mixed and relative references correctly. Copy the function from cell K3 to the range K4:K6. 5
11 You now want to focus on average salaries by job title. You will calculate the average account rep salary and the average manager salary.

In cell J9, insert the AVERAGEIF function to calculate the average salary if the Title column meets the title criteria in cell H9. Use mixed and relative references correctly. Copy the function from cell J9 to cell J10. 5
12 Now you want to focus on the summarizing data for Account Reps hired before 1/1/2018. Specifically, you want to calculate the total number of Account Reps.

In cell J13, insert the COUNTIFS function to calculate the number of Account Reps (cell H9) in the Title column where the date in the Hire Date column was before 1/1/2018. Use relative references for the two criteria range arguments and the title criteria argument in cell H9. Type the date comparison for the second criteria argument. 5
13 The next step is to calculate the total salary for Account Reps hired before 1/1/2018. You will use mixed references so that you can later copy the function and change the function name for other calculations.

In cell J14, insert the SUMIFS function to calculate the total amount in the Salary column where the Title contains the job title criteria Account Rep (cell H9) and where the date in the Hire Date column was before 1/1/2018. Use relative references for the sum_range argument, the two criteria range arguments, and the title criteria in cell H9. Type the date comparison for the second criteria argument. 5
14 Copy the function from cell J14 to cell J15. In cell J15, change the function name to AVERAGEIFS. If the original function was created correctly with mixed and relative cell references, the modified function should calculate the average salary for Account Reps hired before 1/1/2018. 3
15 Copy the function from cell J15 to cell J16. In cell J16, change the function name to MAXIFS. If the original function was created correctly with mixed and relative cell references, the modified function should calculate the highest salary for Account Reps hired before 1/1/2018. 3
16 Copy the function from cell J16 to cell J17. In cell J17, change the function name to MINIFS. If the original function was created correctly with mixed and relative cell references, the modified function should calculate the lowest salary for Account Reps hired before 1/1/2018. 3
17 You want to insert a map to indicate the total payroll by state.

Select the ranges I2:I6 and K2:K6 and insert a filled map. Cut the map and paste it in cell M1. 5
18 Change the map title to Payroll by State. 1
19 You want to customize the map.

Display the Format Data Series task pane and display map labels in the Best fit only position. Apply the Diverging (3-color) series color. Change the minimum lowest value fill color to Orange. Change the maximum highest value fill color to Dark Red. Close the task pane. 2
20 The location managers want new company cars. The chief financial officer has determined that the company can afford $600 monthly payments based on a 5.25% APR for three-year loans. Your first step is to calculate the loan.

Display the 3-Finance worksheet. In cell E2, insert the PV function using the periodic rate, the number of payment periods, and a negative value for the monthly payment. 3
21 Next, you want to enter formulas that will calculate the beginning balance.

In cell B8, enter a reference to cell E2. In cell B9, enter a reference to cell F8. Copy the formula from cell B9 to the range B10:B43. (The results will be correct after all other formulas and columns are completed.) 2
22 In cell C8, enter a mixed reference to B2. Copy the formula to the range C9:C43. The results should be 600 for all formula results. 2
23 The next column is designed to calculate the interest amount per payment.

In cell D8, insert the IPMT function using the periodic rate, the current payment number in the first column, the number of payments, and loan. Use relative and mixed references correctly. Make sure the result is a positive value. Copy the function to the range D9:D43. 3
24 Column E is to display the portion of the payment that goes toward paying down the principal.

In cell E8, insert the PPMT function using the same arguments that you used for the IMPT function. Copy the function to the range E9:E43. 3
25 You are now ready to calculate the ending balance after each payment. After you complete this step, the amortization table should display complete results.

In cell F8, enter a formula that subtracts the principal payment from the beginning balance. Copy the formula to the range F9:F43. 2
26 You are ready to format the amortization table.

Format the range B8:F8 with Accounting Number Format. Format the range B9:F43 with Comma Style. 2
27 Format the range C44:E44 with the Total cell style. 1
28 You decide to include a column to show the cumulative interest after each payment.

In cell H8, insert the CUMIPMT function using the periodic rate, number of payments, loan, first payment number as start period, first payment number as end period, and 0 as the Type argument. Use mixed references for all cell references except the end_period argument, which should be a relative reference. Edit the function to display a positive result. Copy the function to the range H9:H43. 4
29 Finally, you want to calculate cumulative principal after each payment.

In cell I8, insert the CUMPRINC function using the same arguments that you used for the CUMIPMT function. Copy the function to the range I9:I43. 3
30 Save and close Exp22_Excel_Ch07_HOE_Salary.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100

Project 2
Ch08_HOE_Assessment

Project Description:
You are the superintendent of schools for Banton School System, a K-12 school district in Erie, Pennsylvania. You and your team have the task of evaluating student and teacher performance across schools in your district. As part of your evaluation, you want to perform several statistical calculations based on location, age, and test scores.
First, you will assess middle school students' standardized testing performance. As part of this analysis, you will perform basic descriptive statistical calculations. You will also compare performance to attendance and test the correlation between test scores and daily turnout. You will perform a more advanced evaluation of high school students' performance using the Analysis ToolPak. Finally, you will perform trend analysis to evaluate teachers' salaries based on years of service.

Steps to Perform:

Step Instructions Points Possible
1 Start Excel. Download and open the file named Exp22_Excel_Ch08_HOE_Assessment.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 You will calculate the standard deviation and variance of the test scores within the sample.

Enter a function in cell H9 to calculate the standard deviation of the sample of text scores in the range C4:C53.

Enter a function in cell I9 that calculates the variance of the sample data in the range C4:C53.

Format the results as numbers to display no decimal points. 9
3 After calculating the standard deviation and variance to help determine the data points' distance from the mean, you theorize that there is a direct relationship between attendance and test scores. You will use the CORREL function to test the strength of the relationship.

Enter a function in cell J9 to determine the correlation of the values in the range C4:C53 and F4:F53. Format the result as a number with two decimal points. 7
4 You want to determine the frequency of student absences based on the criteria of perfect attendance. Attendance is divided into the following bins: 0 days absent, 1 to 5 days absent, and 6 to 10 days absent. To do this, you will use the FREQUENCY function.

Use the Frequency function in the range I12:I14 to determine the frequency of absences located in the range F4:F53 based on the bins located in the range H12:H14. Divide the results by the sample size in I5 in order to return the results as a percentage. Then format the results in the range I12:I14 as Percentage number format with 0 decimal places. 5
5 You will analyze SAT data across multiple high schools in the district. As part of the analysis, you use the Analysis ToolPak.

Make the High School Samples worksheet active. Ensure the Analysis ToolPak is enabled. Use the Analysis ToolPak to create a Anova: Single Factor report starting the Output Range in cell F7. Use the input range B3:D53 and ensure Labels in first row check box is selected. AutoFit the width for columns F:L. 6
6 You hypothesize that there is a negative relationship between attendance and test scores. Your next assessment is an analysis of trends between SAT scores and attendance.

Make the Combined Score Samples worksheet active. Create a Covariance report using the input range C3:D153 and the output range H3. Be sure to include the labels in the first row. Set the column width of columns I:J to 13. 6
7 Use the Analysis ToolPak to create a histogram starting in cell H8 using the input range D4:D153 and the Bin range F4:F6. Include Cumulative Percentage in the chart output. Position the chart so the upper left corner begins in cell H13. 6
8 Your data only contains data from students with up to 10 absences; however, you want to predict the impact on test scores of students with more than 10 absences. Your last task is creating a forecast sheet to predict the impact of up to 15 absences on SAT scores.

On the Combined Score Samples worksheet, select B3:D153 and sort by column D smallest to largest. Next, create a Forecast Sheet based on the range C3:D153. Use 15 as the Forecast End value. Format the newly created chart with chart Style 12. Position and resize the chart so it spans the range F3:L15. Rename the worksheet Forecast. 7
9 You have collected a sample set of data that includes the years of service and salaries of 50 teachers from the Banton School System. You want to use this data to create a trendline that can be used for forecasting. You will create a scatter plot chart of the data and add a linear trendline with equation and R-squared.

Make the Educator Assessment worksheet active. Insert a Scatter (X,Y) chart using the range C3:C52 as the X axis values and D3:D52 as the Y axis values. Add a linear trendline that displays the equation on chart and R-squared value. Position the equation in the upper-left corner of the chart and add the chart title Salary Analysis. Position and resize the chart so it spans the range F6:I19. 5
10 Excel automatically created the linear equation y = 3014.5x + 9028.2. However, you can calculate this equation manually using functions in Excel. You will use this method to verify the accuracy of the chart. Next, you will use the INTERCEPT and SLOPE functions to verify the results.

Use the Intercept function to Calculate the Intercept of the equation in cell H21. Use the range D3:D52 as the known_ys and C3:C52 as known_xs.

Use the Slope function to Calculate the slope of the equation in cell H22. Use the range D3:D52 as the known_ys and C3:C52 as known_xs. 12
11 Your next step is to test the accuracy of your trendline by using the RSQ and STEYX functions.

Type RSQ in cell G23 and Standard Error in cell G24.

Use the RSQ function to Calculate the R-squared value of the equation in cell H23. Use the range D3:D52 as the known_ys and C3:C52 as known_xs.

Use the STEYX function to Calculate the standard error value of the equation in cell H24. Use the range D3:D52 as the known_ys and C3:C52 as known_xs. 11
12 For your final task, you will use the FORECAST.LINEAR function to predict the salary of a teacher who has worked for 30 years. You will then manually check the results using the linear equation y = mx + b in which m and b represent intercept and slope, respectively.

Type Predicted Salary in cell G3. Type High in cell G4, Low in cell G5, and Check in cell J2.

Use the FORECAST.LINEAR function in cell H3 to calculate the predicted salary of an educator based on 30 years of service. Use the range D3:D52 as the known ys and C3:C52 as the known-xs.

In cell H4, calculate the upper threshold of the forecast value by adding the predicted value (H3) with the standard error (H24).

In cell H5, calculate the lower threshold of the forecast value by subtracting the predicted value (H3) with the standard error (H24).

Apply Accounting Number format to the range H3:H5 and cell K2.

In cell K2, use the linear equation to verify the value determined using the FORECAST.LINEAR function. The linear equation is y = mx + b. Use the predicted value in cell H2 multiplied by the slope in cell H22 plus the intercept in cell H21. 26
13 Save and close Exp22_Excel_Ch08_HOE_Assessment.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100

Project 3
Ch07_CumulativeAssessment_Shipping

Project Description:
You work for a company that sells cell phone accessories. The company has distribution centers in three states. You want to analyze shipping data for one week in April to determine if shipping times are too long. You will perform other analysis and insert a map. Finally, you will prepare a partial loan amortization table for a new delivery van.

Steps to Perform:

Step Instructions Points Possible
1 Start Excel. Download and open the file named Exp22_Excel_Ch07_CumulativeAssessment_Shipping.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 The Week worksheet contains data for the week of August 5.

In cell D7, insert the appropriate date function to calculate the number of days between the Date Arrived and Date Ordered. Copy the function to the range D8:D35. 5
3 You want to display the weekday for the arrival dates.

In cell E7, insert the WEEKDAY function to identify an integer representing the weekday of the Date Arrived. Copy the function to the range E8:E35. 4
4 You need to format the WEEKDAY function results with a custom number format.

Select the range E7:E35, apply the custom number format dddd, and apply center horizontal alignment. 2
5 Next, you want to display the city names that correspond with the city airport codes.

In cell G7, insert the SWITCH function to evaluate the airport code in cell F7. Include mixed cell references to the city names in the range G2:G4. Use the airport codes as text for the Value arguments: AUS for Austin, DFW for Dallas-Fort Worth, and IAH for Houston. Copy the function to the range G8:G35. 5
6 Now you want to display the standard shipping costs by city.

In cell I7, insert the IFS function to identify the shipping cost based on the airport code and the applicable shipping rates in the range H2:H4. Enter the airport codes in this sequence: AUS, DFW, IAH (with their respective costs). Use relative and mixed references correctly. Copy the function to the range I8:I35. 5
7 Finally, you want to calculate a partial shipping refund if two conditions are met.

In cell J7, insert an IF function with a nested AND function to determine shipping refunds. The AND function should ensure both conditions are met: Total Days is greater than Total Days Delivery Goal (cell C3) and Order Total is equal to or greater than Order Total Threshold (cell C2). If both conditions are met, the refund is 50% (cell C4) of the Shipping Cost. Otherwise, the refund is $0. Use mixed references as needed. Copy the function to the range J8:J35. 5
8 The Stats worksheet contains similar data. Now you want to enter summary statistics.

In cell B2, insert the COUNTIF function to count the number of shipments for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C2:D2. 5
9 In cell B3, insert the SUMIF function to calculate the total orders for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C3:D3. 5
10 In cell B4, insert the AVERAGEIF function to calculate the average number of days for shipments from Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C4:D4. 5
11 Now you want to focus on shipments from Houston where the order was greater than $1,000.

In cell C7, insert the COUNTIFS function to count the number of orders where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. 5
12 In cell C8, insert the SUMIFS function to calculate the total orders where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. 5
13 In cell C9, insert the MAXIFS function to return the highest order total where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. 5
14 On the Map worksheet, insert a map for the states and revenues. Cut and paste the map in cell C1. 5
15 Format the data series to show only regions with data and show all map labels. 3
16 Change the map title to August 5-9 Gross Revenue. 2
17 Use the Loan worksheet to complete the loan amortization table.

In cell F2, insert the IPMT function to calculate the interest for the first payment. Copy the function to the range F3:F25. (The results will update after you complete the other functions and formulas.) 5
18 In cell G2, insert the PPMT function to calculate the principal paid for the first payment. Copy the function to the range G3:G25. 5
19 In cell H2, insert a formula to calculate the ending principal balance. Copy the formula to the range H3:H25. 5
20 Now you want to determine how much interest was paid during the first two years.

In cell B10, insert the CUMIPMT function to calculate the cumulative interest after the first two years. Make sure the result is positive and that cell references are used where appropriate. 5
21 In cell B11, insert the CUMPRINC function to calculate the cumulative principal paid at the end of the first two years. Make sure the result is positive and that cell references are used where appropriate. 5
22 You want to perform a what-if analysis to determine the rate if the monthly payment is $1,150 instead of $1,207.87.

In cell B15, insert the RATE function to calculate the necessary monthly rate given the NPER, proposed monthly payment, and loan. Make sure the result is positive. 5
23 Finally, you want to convert the monthly rate to an APR.

In cell B16, insert a formula to calculate the APR for the monthly rate in cell B15. 4
24 Save and close Exp22_Excel_Ch07_CumulativeAssessment_Shipping.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100

Project 4

Ch06_Cumulative_AutoSales

Project Description:
You are the finance manager for Grossman Auto Sales. Your company offers in-house financing and as part of your duties, you create reports detailing purchase options. You want to create a report to determine the optimal purchase price of a vehicle based on your customer's budget. You also want to create one- and two-variable data tables to provide detailed payment options based on varying interest rates and down payments.

Steps to Perform:

Step Instructions Points Possible
1 Download and open the file named Exp22_Excel_Ch06_Cumulative_AutoSales.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 Create appropriate range names for Purchase Price (cell C5), Sales Tax (cell C6), Down Payment (cell C7), Months Financed (C8), APR (cell C9), Down Payment Amount (cell C12), Amount Financed (cell C13), and Tax Owed (cell C14). Create the range names using the Create from Selection method, using the values in the left column. 6
3 Edit the existing name range Tax_Owed to display as Tax. Note, Mac users, in the Define Name dialog box, add the new named range, and delete the original one. 4
4 Apply the range names to the existing formulas and functions in the worksheet. 4
5 Create a new worksheet labeled Range Names, paste the newly created range name information in cell A1, and then resize the columns as needed for proper display. 6
6 Use Goal Seek to determine the optimal purchase price to reach a $500 monthly payment with all other variables remaining unchanged. 2
7 Start in cell E5. Complete the series of substitution values ranging from $20,000 to $75,000 at increments of $5,000 vertically down column E. Apply Comma Style format to the range E5:E16 with two decimal points and preserve the thick bottom border on row 16. 3
8 Enter references to the Down Payment Amount, Tax Owed, and Monthly Payment (Cells F4, G4, and H4) in the correct location for a one-variable data table. Use range names where indicated. 3
9 Complete the one-variable data table and format the results with Comma Style with no decimal places. 5
10 Apply Custom number formats to cell F4:H4 that displays the text Down Payment in cell F4, Tax in G4, and Monthly Pmt in cell H4. Bold and center the headings and substitution values. 3
11 Copy the purchase price substitution values from the one-variable data table and paste the values starting in cell J5. Adjust the width of column J as needed. 3
12 Type 5000 in cell K4. Complete the series of substitution values from $5,000 to $15,000 at $5,000 increments. Apply Accounting Number Format to the range K4:M4. 3
13 Enter the reference to the Monthly Payment function in the correct location for a two-variable data table. 3
14 Complete the two-variable data table and apply Comma Style to the range K5:M16 with no decimal places. 9
15 Apply a Custom number format to make the formula reference display as the descriptive column heading Purchase Price. Bold the headings in cells J4:M4. 4
16 Create a scenario named Best Case, using Purchase Price and Months Financed. Enter these values for the scenario: 40000, and 36. 4
17 Create a second scenario named Worst Case, using the same changing cells. Enter these values for the scenario: 50000, and 72. 4
18 Create a third scenario named Most Likely, using the same changing cells. Enter these values for the ­scenario: 45000, and 60. 4
19 Generate a Scenario Summary report based on Monthly Payment. Be sure to use best practice in formatting the report by deleting Column A:B, Row 1, and the Current Values column. 5
20 Load the Solver add-in if it is not already loaded. Set the objective to calculate a Monthly Payment of $500. 5
21 Use Purchase Price and Months Financed as changing variable cells. 4
22 Set constraints to ensure the Purchase Price is less than or equal to $50,000, greater than or equal to $30,000, and a whole number. 5
23 Set constraints to ensure months financed are less than or equal to 72, greater than or equal to 24, and a whole number. 6
24 Solve the problem and save the results. Generate the Answer Report. If you get an internal memory error message, remove Solver as an add-in, close the workbook, open the workbook, add Solver in again, and finish using Solver. 5
25 Save and close Exp22_Excel_Ch06_Cumulative_AutoSales.xlsx. Exit Excel. Submit the file as directed. 0