I need an Excel expert who knows how to do Amortization and Data Visualization
Budget: $10 – $30 CAD
Most banking websites have mortgage calculators that provide some estimates, but they do not present all the information needed to make a decision. That’s where you come in.
In your group, you will construct an Excel file that will display comprehensive information about a possible mortgage. Using Excel functions and formulas, you will calculate all the required values for every step of an amortization period.
Since users are generally interested in end products and summaries, your file will output the requested mortgage information on an easily accessible dashboard. All the calculations will be done on different sheets of the workbook.
Criteria
Content
Create an Excel file that will display important information on a dashboard. All calculations for this dashboard should be done on secondary sheets.
At a minimum, the dashboard should show the following:
• Value of PMT based on user-entered variables/chosen options (include an option for down payments)
• Partial amortization schedule for a chosen year, with totals for that year
• Line graph showing remaining balance over entire mortgage
• Pie chart displaying percentage of PMT that goes to interest and to principal (can be set to show details for a single payment or group of payments)
• Calculation of interest/principal portions between selected payments and overall mortgage remaining after a chosen payment
• Area where lump sum prepayments can be input to see how they may affect the remainder of the mortgage
Formatting
Besides summarizing and displaying a lot of information, a significant feature of a dashboard is its appearance. When creating your dashboard, consider the following aspects:
• Consistent and appealing colours, borders, fonts, etc.
• Proper cell formatting
• Dropdown menus for the user to pick options from
• Size, layout and organization of objects, cells, drop-down menus, etc.
• Data validation and error messages if the user enters something invalid
Additionally, your group must include the following Excel features in your file:
• First sheet renamed to clearly indicate what is displayed
• Other sheets hidden
• Headers and footers on the first sheet that include group member names, the project title, the sheet name, the date and page numbers. Sheet name, date, and page numbers should be automated.
• An appropriate print area for the first sheet
• Proper print formatting. Consider features such as orientation, alignment, margins, etc.
In your group, you will construct an Excel file that will display comprehensive information about a possible mortgage. Using Excel functions and formulas, you will calculate all the required values for every step of an amortization period.
Since users are generally interested in end products and summaries, your file will output the requested mortgage information on an easily accessible dashboard. All the calculations will be done on different sheets of the workbook.
Criteria
Content
Create an Excel file that will display important information on a dashboard. All calculations for this dashboard should be done on secondary sheets.
At a minimum, the dashboard should show the following:
• Value of PMT based on user-entered variables/chosen options (include an option for down payments)
• Partial amortization schedule for a chosen year, with totals for that year
• Line graph showing remaining balance over entire mortgage
• Pie chart displaying percentage of PMT that goes to interest and to principal (can be set to show details for a single payment or group of payments)
• Calculation of interest/principal portions between selected payments and overall mortgage remaining after a chosen payment
• Area where lump sum prepayments can be input to see how they may affect the remainder of the mortgage
Formatting
Besides summarizing and displaying a lot of information, a significant feature of a dashboard is its appearance. When creating your dashboard, consider the following aspects:
• Consistent and appealing colours, borders, fonts, etc.
• Proper cell formatting
• Dropdown menus for the user to pick options from
• Size, layout and organization of objects, cells, drop-down menus, etc.
• Data validation and error messages if the user enters something invalid
Additionally, your group must include the following Excel features in your file:
• First sheet renamed to clearly indicate what is displayed
• Other sheets hidden
• Headers and footers on the first sheet that include group member names, the project title, the sheet name, the date and page numbers. Sheet name, date, and page numbers should be automated.
• An appropriate print area for the first sheet
• Proper print formatting. Consider features such as orientation, alignment, margins, etc.