- Details
- Parent Category: Programming Assignments' Solutions
We Helped With This Excel Programming Homework: Have A Similar One?
Short Assignment Requirements
Assignment Description
GO16_XL_CH04_GRADER_4E_HW - Condo Loan 1.1
Project Description:
In this project, you will use a PMT function to calculate a value and then use it in a two-variable data table. You will create named ranges and use them in formulas.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step | Instructions | Points Possible |
1 | Start Excel. Download, save, and open the workbook named go16_xl_ch04_grader_4e_hw.xlsx. | 0.000 |
2 | Display
the Condo Purchase worksheet. In cell B5, insert the PMT function using the
data from the range B2:B5—be sure to divide the
interest rate by 12 multiply the
years by 12 and display the payment
as a positive number. The result, $6,598.44, is larger than the maximum
desired payment of $6,250. | 10.000 |
3 | Use
Goal Seek to change the amount of the loan so that the payment is $6,250.
Then, in cell A7, type Option #1 Reduce the
Loan | 7.000 |
4 | Use
Goal Seek to change the period of the loan (of the original data) so that the
payment is $6,250. Then, in cell A13, type Option #2 Increase Years | 4.000 |
5 | Display
the Payment Table worksheet, in the range A2:B4, enter the following row
titles and data. | 5.000 |
6 | In cell C8, type 60—the number of months in a 5-year loan. In D8, type 120—the number of months in a 10-year loan. Fill the series through cell H8. Apply Bold and Center to the range C8:H8. | 10.000 |
7 | Beginning in cell B9, enter varying interest rates in decrements of 0.5% beginning with 7.5% and ending with 4.0% If necessary, format all the interest rates with two decimal places, and then apply Bold and Center to the range B9:B16. | 10.000 |
8 | In cell B8, enter a PMT function using the information in cells B2:B4. Be sure that you convert the interest rate to a monthly rate and that the result displays as a positive number. | 5.000 |
9 | Create a Data table in the range B8:H16 using the information in cells B2:B4 in which the Row input cell is the Period and the Column input cell is the Interest rate. Copy the format from cell B8 to the results in the data table. Format cell D16 with the Note cell style as the payment option that is close to but less than $6,250 per month. | 10.000 |
10 | Display the Advertising Costs by Quarter worksheet, and then apply the Currency [0] cell style to the range B6:E6 and to the range B18:E18. Apply the Comma [0] cell style to the range B7:E17, and then apply the Total cell style to the range B18:E18. | 9.000 |
11 | Name
the following ranges: B6:E10 Newspaper_Costs B11:E14 Digital_Costs B15:E16 Magazine_Costs B17:E17 Billboard_Costs | 7.000 |
12 | Display the Name Manager, click Digital_Costs, and then change cell E14 to E15. Select the Billboard_Costs and then Edit the name to Outdoor_Costs Save your workbook. | 7.000 |
13 | Display the Annual Advertising Costs sheet. In cell B5, type =sum(N and sum the values using the appropriate range name in the displayed list of functions. Repeat for the other named ranges. From the Formulas tab, in the Function Library group, use AutoSum to sum all the costs. Apply Currency [0], Comma [0], and Total cell styles to the appropriate cells. | 10.000 |
14 | In the sheet tab row, right-click any sheet tab, and then click Select All Sheets. Display the Page Setup dialog box, click the Margins tab, and then under Center on page, click Horizontally. Click the Header/Footer tab, click Custom Footer, and then in the Left section, insert the File Name. Click OK two times. Ungroup the sheets, display the Payment Table sheet. Set the Orientation of this sheet to Landscape and set the Scale to 85%. | 5.000 |
15 | Display Backstage view, click Show All Properties. On the list of Properties, in the Tags box, type payment table, advertising costs In the Subject box, type your course name and section #. Under Related People, be sure that your name displays—edit if necessary. On the left, click Print. Under Settings, click the Print Active Sheets arrow, and then click Print Entire Workbook. At the bottom of the window, click Next Page to scroll through the worksheets. On the left, click Save. | 1.000 |
16 | Ensure that the worksheets are correctly named and placed in the following order in the workbook: Condo Purchase, Payment Table, Advertising Costs by Quarter, Annual Advertising Costs. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. | 0.000 |
| Total Points | 100.000 |
Assignment Description
GO16_XL_CH05_GRADER_5F_HW - Career Programs 1.1
Project Description:
In this project, you will you will edit a worksheet, with data that has been sorted, filtered, and grouped that analyzes Delivery Modes for programs at Laurel College.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step | Instructions | Points Possible |
1 | Start Excel. Download, save, and open the workbook named go16_xl_ch05_grader_5f_hw.xlsx. Save it in your Excel Chapter 5 folder as Lastname_Firstname_5F_Career_Programs.xlsx | 0.000 |
2 | On the Main-East-West worksheet, insert a table using the range A1:J40. Apply Light Green, Table Style Light 21. | 12.000 |
3 | Sort the values in the Campus column using a custom list in the following order: Main, East, and then West. Add a second level sort by Division and a third level sort by Program Name, both in ascending order. | 15.000 |
4 | On
the Delivery Mode Comparison worksheet, copy the range A6:G7. Paste it in
cell A1, change the title in cell A1 to Criteria and then name the range A2:G3 Criteria. | 15.000 |
5 | Copy the range A1:G2, and paste it into cell A36. Change the title in cell A36 to Online-Hybrid Delivery Mode and then name the range A37:G37 Extract. Select the range A7:G34, and then name this range Database. | 15.000 |
6 | Create
an advanced filter that will place in the extract area the records from the
Database range in which Online is
the primary delivery mode and Hybrid is the secondary delivery mode. | 12.000 |
7 | Display the Stipends by Division worksheet. Sort the data in ascending order first by Division and then by Program Chair Stipend. | 10.000 |
8 | Apply subtotals to the Program Chair Stipend column at each change in Division. AutoFit columns C: D and then collapse the outline so that the Level 2 summary information is displayed. | 8.000 |
9 | Group
all three worksheets. Insert a footer in the left section that includes the
File Name field, and in the right section, insert the Sheet Name field.
Center the worksheets Horizontally, and then set the Width and Height to 1
page. | 11.000 |
10 | Show all the properties, and then as the Tags, type career programs As the Subject, type your course name and section number. Be sure your name displays as the Author. | 2.000 |
11 | Save the workbook. Ensure that the workbook contains the following sheet tabs, in this order: Main-East-West, Delivery Mode Comparison, and Stipends by Division. Close the workbook, and then exit Excel. Submit the workbook as directed. | 0.000 |
| Total Points | 100.000 |
