Microsoft Office Excel 2007 Project 1 answer below »
Microsoft Office Excel 2007 Project 1 answer below »
PROJECT 1
There are THREE parts to this project, so be sure to complete all pieces.
Part 1
Design and create a workbook to compare the purchase versus lease of an automobile. The car you wish to purchase is $18,400. Your monthly net income (after taxes) is $3,024. You need to decide whether to lease the car over three years or buy the car with the aid of a bank loan for $18,400. Design and create your solution using Excel. Format your workbook attractively and appropriately based on the principles learned in this course. You may need to use the function wizard to explore functions. Be sure your calculations support your conclusions.
The following information is provided:
If you were to lease the vehicle with zero down, the lease payment would be $355 per month. At the end of the three years, there is an option to buy the vehicle for $12,000. What would be the total cost to own the vehicle at the end of three years?
If you were to purchase the vehicle with the aid of a bank loan with zero down for three years (interest rate is 7%), what would your monthly payment be? What would be the total cost to own the vehicle at the end of three years?
Ho w much would you pay, over and above the price of the vehicle, for each option?
What do you recommend as the best financial alternative: lease or purchase? If you were to be in this situation right now, which would you pick and why? What other factors would you consider in your decision OTHER THAN cost?
Create an attractive chart to illustrate your financial findings. Be sure to pick an appropriate chart type AND to format it attractively and according to principles learned in this course.
Perform all analysis using Excel. Answer the questions within your Excel workbook. When you’ve completed your analysis, upload your results
Part 2
Design and create a worksheet to aid you in the following decision regarding a home purchase. Format your workbook attractively and appropriately.
Asking price:$225,000Family monthly income:$5,250(after taxes)Savings:$33,750
Assumptions:•Mortgage payment maximum of 25% of net income•Mortgage rates:o6.5% with a minimum 10% down ORo6.25% with a minimum 15% down•You don’t want to use all of your savings on your down payment•TIP: Don’t forget to deduct your down payment from the amount you’re financing!
Create a worksheet to analyze this information and that provides answers to the following questions:•What would your monthly payment be for a 20, 25, and 30 year loan? Show analysis for both rates mentioned above.•Would you be able to afford a 20 or 25 year loan? Why or why not? What are your options?•What is the total cost of the home for each scenario?•What is the difference in the costs of the financing options for each of the scenarios?•What factors would contribute to taking a 20, 25 or 30 year loan?•Which would you choose? Why? What are some additional options you see to making the best situation ‘work’?
Create an attractive chart (or multiple charts) to illustrate your financial findings. Be sure to pick appropriate chart types AND to format all charts attractively and according to principles learned in this course.
Perform all analysis using Excel. Answer the questions within your Excel workbook. When you’ve completed your analysis, upload your results
Part 3
Download the Excel file “Payroll” from our class website. Modify this file according to the instructions below.
1.Add the following headings in row 4, starting in column H:•Total hours, Gross pay, Social security tax, Income taxes, Net pay
2.Add formulas to calculate the following for all employees:•Total hours (use a function)•Gross pay (use an IF function)oEmployees get paid at their regular rate for the first 40 hoursoEmployees receive overtime pay (1.5 times their regular rate) for any hours worked over 40•Social security tax (rate is in cell B2)•Income taxes (the tax rate is 15%)•Net pay (Gross pay less all taxes)
3.Format the worksheet attractively and appropriately AND consistently! A few extras:•Insert appropriate titles at the top of the worksheet according to principles taught in this class•Add any other formatting to make the worksheet attractive and in line with principles taught in this class
4.Create a formula that totals each of the columns created in steps 1 and 2
5.Create the following charts on separate worksheets and format appropriately:•Bar chart of total hours worked for all employeesoAdd data labels and recolor each bar (delete legend)oAdd a graphic and appropriate textbox to this chart (your choice)
6.Create a footer with your name, the file name and the date (using these features in Excel). Change the page orientation to landscape and use the Excel feature to make it fit on one page. .
