- Details
- Parent Category: Programming Assignments' Solutions
We Helped With This Excel Programming Assignment: Have A Similar One?
Category | Programming |
---|---|
Subject | Excel |
Difficulty | College |
Status | Solved |
More Info | Need Help With Excel |
Assignment Description
Specialized Functions
Project Description:
In the following project, you will perform sales analysis, calculate summary data using database functions, and complete an amortization table.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step | Instructions | Points Possible |
1 | Download and open the file named exploring_e07_grader_a1.xlsx, and then save the file as e02c1Sales_LastFirst, replacing LastFirst with your name. | 0 |
2 | Click the Sales Data by Agent worksheet and enter a nested function in cell H9 the Bonus column. If the employee is international and sold over $200,000 they receive 5% bonus, all other employees receive 3%. | 10 |
3 | Using the appropriate cell referencing, copy the function down the column. | 7 |
4 | Type Ron in cell B24. | 4 |
5 | Type Q1 in cell B25. | 4 |
6 | Enter a nested function in cell B26 that uses the cells B24 and B25 to return a specific sales record. | 10 |
7 | Click the Individual Awards worksheet and enter conditions in the Criteria Range for international sales reps that made $250,000 or more in sales. | 10 |
8 | Perform an advanced filter based on the criteria range. Set the filter to copy the new data into row 22. | 10 |
9 | Enter a database function to calculate the total number of international sales rep in cell J8. | 12 |
10 | Enter a database function to calculate the highest international sales dollar in cell J9. | 3 |
11 | Click the Acquisition worksheet and then insert a function in cell E2 to calculate the loan amount based on the loan parameters. | 4 |
12 | Enter a formula in cell E3 to calculate the total number of periods. | 2 |
13 | Enter a formula in cell E4 to calculate the periodic monthly rate. | 2 |
14 | Enter a function in cell E5 to calculate the monthly payment. Modify the function to ensure that the result is a positive number. | 2 |
15 | Enter a function in cell E6 to calculate the total interest paid after five payments. Modify the function to ensure that the result is a positive number. | 2 |
16 | Complete the loan amortization table for the first five payments only. In cell A11, enter 1. In cell B11, create a relative reference to cell B7 and in cell C11, create a relative reference to cell E2. Use the DATE function to complete the Payment Date column and financial functions for the Interest Paid and Principal Payment columns. In cell F11, enter =C11-E11. In cell C12, create a relative reference to cell F11. Note: Be sure to only complete the table through row 15. | 18 |
17 | Save the file making sure the worksheets are in the following order: Sales Data By Agent, Individual Awards, and Acquisition. Close Excel. Submit the file as directed. | 0 |
| Total Points | 100 |
Assignment Description
Specialized Functions
Project Description:
In the following project, you will use Excel to perform calculations regarding rental properties. You will create a basic search, utilize database functions, and create an amortization table
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step | Instructions | Points Possible |
1 | Download and open the file named exploring_e07_grader_h3.xlsx, and then save the file as e07c2Apartment_LastFirst, replacing LastFirst with your name. | 0 |
2 | Insert functions in the Pet Deposit column of the Summary worksheet to calculate the required pet deposit for each unit. If the unit has two or more bedrooms and was remodeled after 2008 the deposit is $125, if not it is $75. | 10 |
3 | Enter nested functions in the Recommendation column to indicate Need to remodel if the apartment is unoccupied and was last remodeled before 2005. For all other apartments, display No change. | 10 |
4 | Type 101 in cell B2. | 4 |
5 | Insert a nested lookup function in cell E2 that will look up the rental price in column D using the apartment number referenced in cell B2. | 10 |
6 | Click the Database worksheet and enter conditions in the Criteria Range for unoccupied two- and three-bedroom apartments that need to be remodeled. | 10 |
7 | Perform an advanced filter based on the criteria range. Filter the existing database in place. | 10 |
8 | In cell C7, enter a DCOUNTA function to calculate the number of apartments to remodel. | 6 |
9 | In cell C8, enter a database function to calculate the total lost rent for the month. | 2 |
10 | Enter a database function to calculate the year of the oldest remodel in cell C9. | 2 |
11 | Click the Loan worksheet and enter 3/20/2015 in cell B7. | 2 |
12 | Insert a formula in cell E2 to calculate the loan amount based on the loan parameters in the input area. | 2 |
13 | Insert a formula in cell E3 to calculate the total number of periods. | 2 |
14 | Insert a formula in cell E4 to calculate the periodic monthly rate. | 2 |
15 | Insert a function in cell E5 to calculate the monthly payment. Ensure that the function returns a positive value. | 2 |
16 | In cell E6, insert a function to calculate the total interest paid on the loan. Ensure that the function returns a positive value. | 2 |
17 | Complete the loan amortization table for the first five payments only. In cell A11, enter 1. In cell B11, create a relative reference to cell B7 and in cell C11, create a relative reference to cell E2. Use the DATE function to complete the Payment Date column and financial functions for the Interest Paid and Principal Payment columns. In cell F11, enter =C11-E11. In cell C12, create a relative reference to cell F11. Note: Be sure to only complete the table through row 15. | 18 |
18 | Create a footer with the sheet name code in the center, and the file name code on the right side of each worksheet. | 6 |
19 | Save the file making sure the worksheets are in the following order: Summary, Database, and Loan. Close Excel. Submit the file as directed. | 0 |
| Total Points | 100 |
Assignment Description
Statistical Functions
Project Description:
You are the manager of a local gym and you will use excel to perform statistical analysis investigating the relationship between weight loss and workouts with a personal trainer.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step | Instructions | Points Possible |
1 | Download and open the file named exploring_e08_grader_a1, and then save the file as exploring_e08_grader_a1_LastFirst, replacing “LastFirst” with your name. | 0 |
2 | Enter a conditional function in cell L4 to calculate total members 25 years old or younger. Format the results with Number format and no decimal points. | 5 |
3 | Enter a similar function in cell L5 to calculate the total number of members over the age of 25. | 5 |
4 | Enter a conditional function in cell M4 to calculate the total membership fees paid by members 25 years old or younger. | 5 |
5 | Enter a similar function in cell M5 to calculate the total number of membership fees paid by members over the age of 25. Format the results in M4 and M5 with Accounting Number Format. | 10 |
6 | Enter a function in cell L9 that calculates the total number of members 25 years old or younger that lost more than 5 pounds. | 5 |
7 | Enter a similar function in cell L10 that calculates the total number of members over the age of 25 that lost more than 5 pounds. | 10 |
8 | Enter a function in cell H4 that calculates the rank of the weight loss in cell G4 against the range of individual weight lost in the data set. | 10 |
9 | Use the fill handle to copy the function down column H. Be sure to include the appropriate absolute or mixed cell references before copying the functions. | 10 |
10 | Enter a function in cell K13 to calculate the correlation of column G and I. | 5 |
11 | Format the results as Number format with three decimal positions. | 5 |
12 | Click the DATA tab and select Data Analysis. Select Descriptive Statistics and click OK. Complete the input criteria using the weight loss data in column G. Set the Output functions to display in cell K15 (Hint: be sure to output Summary Statistics). | 10 |
13 | Click the DATA tab and select Data Analysis. Select Histogram and click OK. Use the ages in column C as the input range. Use the age range K4:K5 as the bin range. Output the data to a new worksheet. Be sure to include a chart with the output. | 15 |
14 | Name the new worksheet Histogram. Move the Member Information worksheet so that it is first in the workbook. | 5 |
15 | Ensure that the worksheets are correctly named and placed in the following order in the workbook: Membership Information; Histogram. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. | 0 |
| Total Points | 100 |
Assignment Description
Statistical Functions
Project Description:
In the following project, you will use excel to perform statistical analysis of a cross section sample of an employee satisfaction survey.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step | Instructions | Points Possible |
1 | Download and open the file named exploring_e08_grader_h3, and then save the file as exploring_e08_grader_h3_LastFirst, replacing “LastFirst” with your name. | 0 |
2 | Enter a conditional function in cell I5 to calculate average satisfaction for support staff (H5). Format the results with Number format and two decimal positions. | 6 |
3 | Use the fill handle in cell I5 to copy the function down through the range I6:I9. Be sure to use the appropriate mixed or absolute referencing before copying the functions. | 3 |
4 | Enter a function in cell J5 to calculate the average salary of all support staff (H5) in the survey. | 6 |
5 | Use the fill handle in cell J5 to copy the function down through the range J6:J9. Be sure to use the appropriate mixed or absolute referencing before copying the functions. | 3 |
6 | Enter a function in cell I12 to calculate the number of Directors in the survey that have a job satisfaction level of 4 or higher. | 6 |
7 | Enter a function in cell I13 to calculate the average salary of Directors in the survey that have a job satisfaction level of 4 or higher. | 6 |
8 | Adapt the process used in the previous two steps to calculate the total number and average salary of managers that have a job satisfaction of 4 or higher in cells I16 and I17. | 6 |
9 | Enter a function in cell F4 that calculates the rank of the salary in cell D4 against the range of salaries in the data set. | 6 |
10 | Use the fill handle to copy the function down column F. Be sure to include the appropriate absolute or mixed cell references before copying the functions. | 5 |
11 | Enter a function in cell I20 to calculate the minimum Quartile value in the list of salaries. | 6 |
12 | Use the fill handle to complete the remaining quartile values in cell range I21:I24. Be sure to include the appropriate absolute or mixed cell references before copying the functions. | 5 |
13 | Enter a function in cell H27 to calculate the correlation of column D and E. | 12 |
14 | Format the results as Number Format with two decimal positions. | 5 |
15 | Click the DATA tab and select Data Analysis. Select Descriptive Statistics and click OK. Complete the input criteria using the salary data in column D. Set the Output functions to display on a new worksheet. (Hint: be sure to output Summary Statistics). | 12 |
16 | Name the newly created worksheet Descriptive Statistics. | 3 |
17 | Click the DATA tab and select Data Analysis. Select Histogram and click OK. Use the salaries in column D as the input range. Use the quartiles in the range I20:I24 as the bin range. Output the data in cell H29. Be sure to include a chart with the output. | 10 |
18 | Move the Employee Satisfaction worksheet to display first in the workbook. Ensure that the worksheets are correctly named and placed in the following order in the workbook: Employee Satisfaction; Descriptive Statistics. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. | 0 |
| Total Points | 100 |