Level II Final Project Instructions-
Internet Research
Scenario
Next year you plan to start looking for a new job. You are willing to accept a job out of state if you believe it is fulfilling. You know that you will also need to get a place to live so you will start looking for a new house to move into.
To help you with the planning, you decided to create an Excel workbook to analyze the housing market in the area where you will relocate and develop a budget for expenses in the first year.
You can use multiple worksheets to present your information. Please make sure to enhance your project with appropriate customization - for example, you can apply Word art, customize font style and color, graphics, etc. Make sure to name each of the tabs in your workbook.
Add a comment to each of the formulas or tools that you will apply to your Project explaining the logic of why you want to use this particular formula/tool in each case.
You have to use troubleshooting formula(s) to check the key figures within the worksheet(s).
Make sure to that your Project looks professional.
I. Job
1. Identify the job you want to get. Research annual starting salaries for similar types of jobs in the area to which you are planning to move.
2. Research the types of jobs you want to get and see what the pay scale for similar kinds of jobs is in the area where you plan to move to. You can use various internet resources, a couple of them are: Salary Data & Career Research Center and Ferguson's Career Guidance Center.
3. Create a table to list at least 4 companies that offer the jobs of your interest: enter the name of the company, job title, and starting salary/wages for each of the positions you located. Create corresponding headers in the table.
4. Using the appropriate formula, calculate the average salary for the jobs you found.
Remember to add comments explaining your steps and tools/formulas that you use!
II. Living accommodations
1. Research the typical cost of a house you want to buy or rent in the city where your job is located. One of the websites you can use for this is Zillow.
2. Find at least 4 houses and create a table showing these listings. It should include the following headers and corresponding information: Selling Price, Down Payment (5%), Interest Rate, Length of Loan in Years, Monthly Payments.
Enter the data you found in the table; use all the information and tools Zillow offers, including the "Est. Mortgage" calculator.
3. Calculate the Down Payment (use 5%), and Monthly Payments for each listing. You should utilize appropriate formulas in each case.
4. Calculate the average selling price, down payment and monthly payments for each listing using the appropriate formulas.
5. Based on the data in your table, create a Pivot Chart and place it next to the listings table. If you choose to create the Pivot table in a new Worksheet, do not delete this tab with the actual Pivot chart that was created by Excel, just copy it next to the table.
6. Once you have decided which house you will purchase, create another table called My House, and populate it with the Selling Price, Down Payment, Interest Rate, Length of Loan in Years, and Monthly Payment information just for this house.
7. Assume that you do not want your Monthly Payments to be more than $950 per month. Use Goal Seek to find how much you need to pay down on the house to get to this number.
8. Import the picture of the selected house from the website where you located the listing into your Excel workbook and place it next to the house information. Hyperlink this picture with the website which gives the listing information on this house. Make sure when you click on the picture, you are redirected to this site.
9. Apply conditional formatting and highlight "Selling Price" cells that are greater than an average salary in one color and those that are less - in another color. Remember to add comments explaining your steps and tools/formulas that you use!
III. Living cost
1. Estimate your income after taxes, with net income as 75% of the annual salary.
2. Estimate other living costs in the city including your transportation (for example - renting a car, buying a bus pass), food, entertainment, clothes, cell phone, and Internet per month. Enter all this information in a table in your workbook.
3. Using appropriate formulas and cell references, calculate the total cost of living per month and then total cost of living per year.
Total living expenses for the year should include monthly mortgage (you need to link the data from the monthly payments from the My House information to the cell with the mortgage information) and should appropriately calculate your yearly expenses. At a minimum, you should include all the items that are listed in the table, which lists living cost per month.
4. Calculate your annual cash flow (expenses versus after taxes income) and determine if you have enough money to put aside for a vacation that you would like to go on at the end of the year.
Remember to add comments explaining your steps and tools/formulas that you use!
IV. Vacation
Now, assume you want to save money for a vacation at the end of the year.
1. Create a table and list travel expenses: hotel, transportation, food, entertainment, souvenirs, etc.
2. Use Goal Seek to find how much you need to save to pay for your vacation.
3. Create the vacation summary report.
Remember to add comments explaining your steps and tools/formulas that you use!
V. Additional Requirements
1. Macro
Create a macro and name it Font. Use an appropriate description. The macro should change all font to Times New Roman 12 points. Assign the macro to the Ctrl + Shift + L shortcut.
2. Footer
Create a footer with your name and MEID number in each of the worksheets.
3. Distribution
Prepare your workbook for distribution: inspect your workbook but do not remove any personal information, links or any other data that was required by the directions; mark your workbook as final.
4. Password
Apply "MEID" as the password to protect your file (make sure you use your own MEID number) and save the workbook as Budget_YourName. Replace "Your Name" with your First and Last names.
Attachment:- Project Assignment.rar