Microsoft Office Tutorials and References
In Depth Information
ProSkills Exercise: Decision Making
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
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
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
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.