Assignment 1: Mutual funds offer convenience, diversification, and the services of professional money managers and analysts. Mutual funds can be particularly appealing for small investors who don’t have a lot of money and for those who are new to investing. This project will help you learn more about the various types of mutual funds and how to pick the funds that best suit your investment objectives.
Assume that you’ve just received a windfall of $25,000 and would like to invest it all in mutual
funds. There are several ways to classify mutual funds, but for this project, we will consider the following eight categories:
Growth
Value
Equity income
Bond
Balanced
Index
Socially responsible
International
Pick three or four categories that you believe best meet your financial needs and risk tolerance,
and then select one fund from each category. You are strongly encouraged to use some of the online sources and other references mentioned in this chapter to help you make your selections. For each fund, find the following information:
a. Name of fund, its ticker symbol, the fund manager, and the tenure of the fund manager.
b. Category and size of the fund—try to find the Morningstar style box.
c. Loads, fees, and other charges; minimum investment required.
d. Performance of the fund over the past 1, 3, and 5 years. Compare the fund’s performance to
at least two or three other funds in its category and to an appropriate index over these same
periods.
e. How much did the fund pay out last year in dividends and in distributions of short- and long-term capital gains?
f. What were the approximate and exact returns on the fund last year? (You may have to compute
this yourself; use the approximate return formula and a handheld calculator after finding its price
1 year ago from a source such as http://finance.yahoo.com.)
g. What services does the fund offer, such as automatic reinvestment plans or phone switching?
h. Briefly explain why you selected the fund and how it meets your investment objectives.
Assignment 2:
Personal Finance Data_.xlsx (17.409 KB)
You have recently been hired as a financial analyst for a personal finance consulting firm. Your first task is to create an interactive dashboard to help clients manage and understand their finances better. You have been provided with a dataset that contains detailed transaction records for a client over several months. The data includes information on the date, account type, description of the transaction, amounts debited and credited, sub-categories, categories, and category types.
Data Columns:
Date: The date of the transaction.
Account: The type of account used for the transaction (e.g., Checking, Debit Card).
Description: A brief description of the transaction.
Debit: Amount of money spent (if applicable).
Credit: Amount of money received (if applicable).
Sub-category: A more specific category of the transaction (e.g., Rent, Groceries).
Category: The main category of the transaction (e.g., Housing, Living Expenses).
Category Type: Whether the transaction is an Income or Expense.
Create an interactive dashboard that visualizes the provided personal finance data. The dashboard should help users understand their financial health, track their spending, and identify potential areas for savings. Your final product should closely resemble the example provided.
Data File:
The dataset contains detailed transaction records for a client, including information on the date, account type, description of the transaction, amounts debited and credited, sub-categories, categories, and category types.
Dashboard Requirements:
Your dashboard should include the following components:
Total Spend by Account:A donut chart displaying the total amount spent from different accounts (e.g., Checking, Credit).
Ensure that the donut chart shows the total spend YTD and percentage contributions from each account.
Monthly Expense and Income Analysis:A bar chart displaying monthly expenses for different categories (e.g., Living Expenses, Discretionary, Transport, etc.).
A bar chart displaying the monthly net income (Credit – Debit) for each month.
Category-wise Expense Breakdown:A treemap that shows the distribution of expenses across various categories and sub-categories for a selected month.
Ensure that each category and sub-category is color-coded and labeled with the expense amount.
Expense Trends:A line chart tracking the monthly expenses in different categories over the year.
Top Categories in Selected Period:A bar chart showing the top 5 expense categories for the selected period.
Expense by Category for Selected Month:A bar chart comparing expenses across categories for the selected month, highlighting increases and decreases.
Filters:Include interactive filters for selecting the month and categories to dynamically update the dashboard views.
Steps to Complete the Assignment:
Data Preparation:Clean and preprocess the data. Ensure that all transactions are correctly categorized and date formats are consistent.
Dashboard Construction:Use Microsoft Excel to construct the dashboard.
Ensure all charts are interactive and update based on the filters applied.
Layout and Design:Design the dashboard layout to be user-friendly and visually appealing.
Use appropriate colors, fonts, and labels to enhance readability and understanding.
Insights and Recommendations:Analyze the data and provide a brief report with key insights and recommendations for the client’s financial management.
Deliverables:
Interactive Dashboard:An interactive dashboard that meets the above requirements and closely modeled after the example dashboard shown in the provided video.
Analysis Report:A brief report summarizing the key insights derived from the dashboard.
Recommendations for improving financial health based on the analysis.
Submission:
Submit the following files:
The interactive dashboard file in Excel
The analysis report in word format.
You must use Excel to create the dashboard.
Ensure that your submitted files include a sheet demonstrating the thoroughness of your analysis.
Any dashboard generated using AI will be marked zero.
Evaluation Criteria:
Accuracy of data cleaning and preparation.
Completeness and clarity of visualizations.
Creativity and effectiveness of the dashboard design.
Depth of analysis and quality of insights.
Overall presentation and communication, including the effectiveness of the recorded presentation video.