Microsoft Office Tutorials and References

In Depth Information

**ProSkills Exercise: Decision Making**

ProSkills

4. In the budget worksheet, enter realistic monthly earnings for each month of the year.

Use formulas to calculate the total earnings each month, the average monthly

earnings, and the total earnings for the entire year.

5. In the budget worksheet, enter realistic personal expenses for each month. Divide

the expenses into at least three categories, providing subtotals for each category and

a grand total of all the monthly expenses. Calculate the average monthly expenses

and total expenses for the year.

6. Calculate the monthly net cash ﬂ ow (the value of total income minus total

expenses).

7. Use the cash ﬂ ow values to track the savings throughout the year. Use a realistic

amount for savings at the beginning of the year. Use the monthly net cash ﬂ ow

values to add or subtract from this value. Project the end-of-year balance in the savings

account under your proposed budget.

8. Format the worksheet’s contents using appropriate text and number formats. Add

colors and line borders to make the content easier to read and interpret. Use cell

styles and themes to provide your worksheet with a uniform appearance.

9. Use conditional formatting to automatically highlight negative net cash ﬂ ow months.

10. Insert a pie chart that compares the monthly expenses for the categories.

11. Insert a column chart that charts all of the monthly expenses regardless of the

category.

12. Insert a line chart or sparkline that shows the change in the savings balance

throughout the 12 months of the year.

13. Insert new rows at the top of the worksheet and enter titles that describe the

worksheet’s contents.

14. Examine your assumptions. How likely are certain events to occur? Perform several

what-if analyses on your budget, providing the impact of (a) reducing income with

expenses remaining constant, (b) increasing expenses with income remaining

constant, (c) reducing income and expenses, and (d) increasing income and expenses.

Discuss the different scenarios you explored. How much cushion does your

projected income give you if expenses increase? What are some things you can do in

your budget to accommodate this scenario?

15. Think of a major purchase you might want to make—for example, a car or a

computer. Determine the amount of the purchase and the current annual interest rate

charged by your local bank. Provide a reasonable length of time to repay the loan,

such as ﬁ ve years for a car loan or 20 to 30 years for a home loan. Use the PMT

function to determine how much you would have to spend each month on the

payments for your purchase. You can do these calculations in a separate worksheet

16. Add the loan information to your monthly budget and evaluate the purchase of this

item on your budget. Is it affordable? Examine other possible loans you might

pursue and evaluate their impact on your budget. Come up with the most realistic way

of paying off the loan while still maintaining a reasonable monthly cash ﬂ ow and a

cushion against unexpected expenses. If the payment exceeds your budget, reduce

the estimated price of the item you’re thinking of purchasing until you determine the

monthly payment you can afford under the conditions of the loan.