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