Assignments for Class 13: Using Excel for Financial Analysis
Assignment 1: Building a Budget Model
Objective: Create a detailed budgeting model using Excel.
Instructions:
- Create a New Workbook:
- Open Excel and create a new workbook named "Budget_Model.xlsx".
- Set Up Income and Expenses:
- In Sheet1, create a table with the following headers in Row 1:
- A1: Category
- B1: Amount
- Input Data:
- Under Category, list the following in column A:
- Salary
- Investment Income
- Rent
- Utilities
- Groceries
- Entertainment
- Enter realistic amounts for each category in column B (you can use any values you prefer).
- Calculate Totals:
- Below your income and expenses, calculate:
- Total Income (sum of salary and investment income)
- Total Expenses (sum of rent, utilities, groceries, and entertainment)
- Net Income (Total Income - Total Expenses)
- Format Your Table:
- Use bold formatting for headers and totals.
- Apply borders to make your table clear.
Deliverable:
- Submit your completed "Budget_Model.xlsx" file.
Assignment 2: Financial Function Analysis
Objective: Use Excel financial functions to analyze an investment scenario.
Instructions:
- Investment Scenario:
- Imagine you are evaluating an investment opportunity with the following cash flows:
- Year 0: -$5,000 (Initial investment)
- Year 1: $1,200
- Year 2: $1,800
- Year 3: $2,500
- Year 4: $3,000
- Create a New Workbook:
- Open Excel and create a new workbook named "Investment_Analysis.xlsx".
- Input Cash Flows:
- In Sheet1, create a table with the following headers in Row 1:
- A1: Year
- B1: Cash Flow
- List the years (0 to 4) in column A and their corresponding cash flows in column B.
- Calculate Financial Metrics:
- Use the following Excel functions:
- Net Present Value (NPV):
- Use a discount rate of 8% to calculate the NPV of the cash flows.
- Formula: =NPV(8%, B2:B5) + B1
- Internal Rate of Return (IRR):
- Calculate the IRR for the series of cash flows.
- Formula: =IRR(B1:B6)
- Format Your Table:
- Apply formatting to your table (bold headers, borders).
Deliverable:
- Submit your completed "Investment_Analysis.xlsx" file.
Solutions to Assignments
Solution for Assignment 1: Building a Budget Model
- Workbook Name: Budget_Model.xlsx
- Example Data Entry:
Category | Amount |
Salary | $3,500 |
Investment Income | $500 |
Total Income | =SUM(B2 ) |
Rent | $1,200 |
Utilities | $300 |
Groceries | $400 |
Entertainment | $200 |
Total Expenses | =SUM(B5 ) |
Net Income | =B4-B9 |
Solution for Assignment 2: Financial Function Analysis
- Workbook Name: Investment_Analysis.xlsx
- Example Cash Flow Table:
Year | Cash Flow |
0 | -$5,000 |
1 | $1,200 |
2 | $1,800 |
3 | $2,500 |
4 | $3,000 |
NPV (8%) | =NPV(8%, B2 ) + B1 |
IRR | =IRR(B1 ) |
- Example Calculated Values:
- NPV: (Calculated Value)
- IRR: (Calculated Value)
Notes for Submission
- Ensure all formulas are correctly applied.
- Format the spreadsheets neatly for readability.
- Submit your Excel files by the due date provided by the instructor.
