excel assignment
0bApply: Case Problem 1
Data File needed for this Case Problem: Donation.xlsx
Henderson Pediatric Care Center Kari Essen is a fundraising coordinator for the Pediatric Care Center located in Henderson, West Virginia. Kari is working on a report detailing recent donations to the center and wants you to enter this data into an Excel workbook. Complete the following:
1. 1
Open the Donation workbook located in the Excel1 > Case1 folder included with your Data Files. Save the workbook as Donation List in the location specified by your instructor.
2. 2
In the Documentation sheet, enter your name in cell B3 and the date in cell B4.
3. 3
Increase the font size of the text in cell A1 to 28 points.
4. 4
Add a new sheet to the end of the workbook, and rename it as Donor List.
5. 5
In cell A1 of the Donor List worksheet, enter Donor List as the title, and then set the font size to 28 points.
6. 6
In the range A6:H13, enter the donor information shown in Figure 1-44 . Enter the ZIP code data as text rather than as numbers.
Figure 1-44Donation list
7. 7
Set the width of columns A through D to 25 characters. Set the width of column G to 15 characters.
8. 8
9. 9
In cell B2, enter a formula that counts how many numeric values are in the range H7:H13.
10. 10
In cell B3, enter a formula that calculates the sum of the donations in the range H7:H13.
11. 11
In cell B4, enter a formula that calculates the average donation by dividing the value in cell B3 by the value in cell B2.
12. 12
Add borders around the nonadjacent range A2:B4,A6:H13.
13. 13
Set the page orientation of the Donor List to landscape.
14. 14
Scale the worksheet to print on a single page for both the width and the height. If you are instructed to print the worksheet, print the Donor List sheet.
15. 15
Display the formulas in the Donor List worksheet. If you are instructed to print, print the worksheet.
16. 16
Save and close the workbook.
1. 1
Open the Balance workbook located in the Excel1 > Case2 folder included with your Data Files. Save the workbook as Balance Sheet in the location specified by your instructor.
2. 2
In the Documentation sheet, enter your name in cell B3 and the date in cell B4.
3. 3
Go to the Balance Sheet worksheet. Set the font size of the title in cell A1 to 28 points.
4. 4
In cell A2, enter the text Statement for March 2017.
5. 5
Set the width of columns A and E to 30 characters. Set the width of columns B, C, F, and G to 12 characters. Set the width of column D to 4 characters. (Hint: Hold down the Ctrl key as you click the column headings to select both adjacent and nonadjacent columns.)
6. 6
Set the font size of the text in cells A4, C4, E4, and G4 to 18 points.
7. 7
Set the font size of the text in cells A5, E5, A11, E11, A14, E15, A19, E20, and A24 to 14 points.
8. 8
Enter the values shown in Figure 1-45 in the specified cells.
Figure 1-45Assets and liabilities
9. 9
In cell C9, enter a formula to calculate the sum of the Current Assets in the range B6:B9.
10. 10
In cell C12, enter a formula to display the value of B12.
11. 11
In cell C17, enter a formula to calculate the sum of the Tangible Assets in the range B15:B17.
12. 12
In cells C20 and C22, enter formulas to display the values of cells B20 and B22, respectively.
13. 13
In cell C24, enter a formula to calculate the total assets in the balance sheet by adding cells C9, C12, C17, C20, and C22. Set the font size of the cell to 14 points.
14. 14
In cell G9, enter a formula to calculate the sum of the Current Liabilities in the range F6:F9.
15. 15
In cell G13, enter a formula to calculate the sum of the Long-Term Liabilities in the range F12:F13.
16. 16
In cell G18, enter a formula to calculate the sum of the Stockholders’ Equity in the range F16:F18.
17. 17
In cell G20, calculate the Total Liabilities and Equity for the company by adding the values of cells G9, G13, and G18. Set the font size of the cell to 14 points.
18. 18
Check your calculations. In a balance sheet the total assets (cell C24) should equal the total liabilities and equity (cell G20).
19. 19
Set the page layout orientation to landscape and the Balance Sheet worksheet to print to one page for both the width and height.
20. 20
Preview the worksheet on the Print screen in Backstage view, and then save and close the workbook.
1. 1
Open the FTP workbook located the Excel1 > Case3 folder included with your Data Files. Save the workbook as FTP Report in the location specified by your instructor.
2. 2
In the Documentation sheet, enter your name in cell B3 and the date in cell B4.
3. 3
Go to the Race Results worksheet. Change the font size of the title in cell A1 to 28 points.
4. 4
Set the width of column A and B to 15 characters. Set the width of column I to 2 characters.
5. 5
In the range J4:M4, enter the labels Median, Average, Min, and Max.
6. 6
In cell J5, use the MEDIAN function to calculate the median (midpoint) of the FTP values of races 1 through 5 for Diana Bartlett in the range D5:H5. Copy the formula in cell J5 to the range J6:J28 to calculate the median FTP values for the other riders.
7. 7
In cell K5, use the AVERAGE function to calculate the average the FTP value for races 1 through 5 for Diana Bartlett. Copy the formula to calculate the averages for the other riders.
8. 8
In cell L5, use the MIN function to return the minimum FTP value for Diana Bartlett. Copy the formula to calculate the minimums for the other riders.
9. 9
In cell M5, use the MAX function to return the maximum FTP value for Diana Bartlett. Copy the formula to calculate the maximums for the other riders.
10. 10
In the range C30:C33, enter the labels Median, Average, Min, and Max to record summary information for each of the five races.
11. 11
In cell D30, use the MEDIAN function to calculate the median FTP value from the range D5:D28. Copy the formula to the range E30:H30 to determine the median values for the other four races.
12. 12
In the range D31:H31, use the AVERAGE function to calculate the average FTP value for each race.
13. 13
In the range D32:H32, use the MIN function to calculate the minimum value for each race.
14. 14
In the range D33:H33, use the MAX function to calculate the maximum FTP value for each race.
15. 15
16. 16
In the range A3:A7, enter the labels Class Size, Class Average, Class Median, Class Minimum, and Class Maximum.
17. 17
In cell B3, use the COUNTA function to count the number of entries in the range A11:A34.
18. 18
In cell B4, use the AVERAGE function to calculate the average of all FTP values in the range D11:H34.
19. 19
In cell B5, use the MEDIAN function to calculate the median of all FTP values in the range D11:H34.
20. 20
In cell B6, use the MIN function to calculate the minimum FTP value in the range D11:H34.
21. 21
In cell B7, use the MAX function to calculate the maximum FTP value in the range D11:H34.
22. 22
Set the page layout orientation for the Race Results worksheet to portrait and scale the worksheet so that its width and height fit on one page.
23. 23
View the worksheet in Page Layout view, return to Normal view, and then save and close the workbook.
1. 1
Open the Service workbook located in the Excel1 > Case4 folder included with your Data Files. Save the workbook as Service Calls in the location specified by your instructor.
2. 2
In the Documentation sheet, enter your name in cell B3 and the date in cell B4.
3. 3
Go to the Call Sheet worksheet. Insert cells in the range A7:A27, shifting the other cells to the right.
4. 4
In cell A7, enter Cust ID as the label. In cell A8, enter Jensen-5864 (the customer’s last name and last four digits on the phone number) as the customer ID for Patricia Jensen. Use Flash Fill to enter in the remaining customer IDs in the column.
5. 5
Resize the columns of the Call Sheet worksheet so that all of the column labels and the cell contents are completely displayed.
6. 6
There is a problem with the some of the customer ZIP codes. New Jersey ZIP codes begin with a 0, and these leading zeros are not showing up in the contact information. Revise the text of the ZIP code values to correct this problem.
7. 7
The formula in cell L8 that calculates the total number of billable hours for the first customer is not correct. Instead of showing the number of hours, it displays the value as a percentage of a day. Fix this problem by revising the formula so that it multiplies the difference between the value in K8 and J8 by 24. (Hint: Use parentheses to enclose the expression that calculates the difference between starting and ending times so that the difference is calculated first.)
8. 8
Copy the formula you entered for cell L8 to calculate the total billable hours for the rest of the entries in column L.
9. 9
The total charge for each service call is equal to the hourly rate multiplied by the number of hours plus the charge for parts. In cell O8, enter a formula to calculate the total service charge for the first customer, and then copy that formula to calculate the rest of the service charges in column O.
10. 10
In cell B4, enter a formula that uses the COUNT function to count the total number of service calls.
11. 11
In cell B5, Stefan entered a formula to calculate the total charges from all of the service calls. Examine the formula, and correct the expression so that it adds all of the service call charges.
12. 12
Insert two new rows above row 5.
13. 13
In cell A5, enter the label Total Hours. In cell B5, enter function to calculate the total number of hours from all of the service calls.
14. 14
In cell A6, enter the label Average Charge. In cell B6, enter a formula that calculates the average charge per call by dividing the total charges by the total number of calls.
15. 15
Add borders around the cells in the nonadjacent range A4:B7,A9:O29.
16. 16
Set the page layout of the Call Sheet worksheet so that it prints on a single page in landscape orientation.
17. 17
View the worksheet in Page Break Preview, return to Normal view, and then save and close the workbook.