6367
EXCEL Work please guys help me with this assignment
Develop a Budget Worksheet
Excel is valuable to a wide audience of users: from accountantsof Fortune 500 companies to
homeowners
managing their budgets. An Excel workbook can be a complexdocument, recording
data from thousands of financial transactions, or it can track afew monthly household expenses.
Anyone who has to balance a budget, track expenses, or projectfuture income can use the
financial
tools in Excel to help them make good financial decisions abouttheir financing and future
expenditures.
In this exercise, you will use Excel to create a sample budgetworkbook that will contain information
of your choice, using the Excel skills and features presented inTutorials 1 through 4. Use the
following
steps as a guide to completing your workbook.
Note: Please be surenot to include any personal informationof a sensitive nature in any workbooks
you create to be submitted to your instructor for this exercise.Later, you can update the workbooks
with such information for your personal use.
1. Gather the data related to your monthly cash inflows andoutflows. For example, how much
do you take home in your paychecks each month? What othersources of income do you
have? What expenses do you haveA????1rent, utilities, gas, insurance,groceries, entertainment, car
payments,
and so on?
2. Create a new workbook for the sample financial data. Use thefirst worksheet as a
documentation
sheet that includes your name, the date on which you startcreating the
workbook,
and a brief description of the workbookA????1s purpose.
3. Plan the structure of the second worksheet, which willcontain the budget. Include a section
to enter values that remain consistent from month to month, suchas monthly income and
expenses. As you develop the budget worksheet, reference thesecells in formulas that require
those values. Later, you can update any of these values and seethe changes immediately
reflected throughout the budget.
4. In the budget worksheet, enter realistic monthly earnings foreach month of the year. Use
formulas
to calculate the total earnings each month, the average monthlyearnings, and the total
earnings
for the entire year.
5. In the budget worksheet, enter realistic personal expensesfor each month. Divide the expenses
into at least three categories, providing subtotals for eachcategory and a grand total of all the
monthly expenses. Calculate the average monthly expenses andtotal expenses for the year.
6. Calculate the monthly net cash flow (the value of totalincome minus total expenses).
7. Use the cash flow values to track the savings throughout theyear. Use a realistic amount for
savings
at the beginning of the year. Use the monthly net cash flowvalues to add or subtract
from this value. Project the end-of-year balance in the savingsaccount under your proposed
budget.
8. Format the worksheetA????1s contents using appropriate text andnumber formats. Add colors and
borders
to make the content easier to read and interpret. Use cellstyles and themes to provide
your worksheet with a uniform appearance.
9. Use conditional formatting to automatically highlightnegative net cash flow months.
10. Insert a pie chart that compares the monthly expenses forthe categories.
Insert a line chart or sparkline that shows the change in thesavings balance throughout the
12 months of the year.
13. Insert new rows at the top of the worksheet and enter titlesthat describe the worksheetA????1s
contents.
15. Think of a major purchase you might want to makeA????1forexample, a car or a house. Determine
the amount of the purchase and the current annual interest ratecharged by your local bank.
Provide a reasonable length of time to repay the loan, such asfive years for a car loan or 20 to
30 years for a home loan. Use the PMT function to determine howmuch you would have
to spend each month
17. After settling on a budget and the terms of a loan that youcan afford, develop an action plan
for putting your budget into place. What are some potentialpitfalls that will prohibit you from
following through on your proposed budget? How can you increasethe likelihood that you will
follow the budget? Be specific, and write down a list of goalsand benchmarks that youA????1ll use to
monitor your progress in following your financial plan.
Format the worksheets for your printer. Include headers andfooters that display the workbook
filename, the workbookA????1s author, and the date on which thereport is printed. If the report
extends across several pages, repeat appropriate print titles onall of the pages, and include
page numbers and the total number of pages on every printedpage.
20. Save and close the workbook.
