9. A Worksheet using Assumptions

We began entering data into a new business style worksheet to analyze six months of projected income and expenses for Campus Clothiers. Our worksheet will end up as a projected Income Statement.

We entered the sheet name, and subtitle, and then first column heading, 'January'. We changed the orientation of the text to appear at 45 degrees, then used the Fill Handle to create a series of the Months from January to June. Notice that the formatting of our first cell was copied along with the series.

We proceeded to add our Row Names, creating areas for Sales Income, Expenses, Operating Income, and working Assumptions on how we allocate funds to expenses. All of our formulas for expenses were based on 6 months of projected income. Here we allocate a percentage of our income to each expense, never spending more than we earn.

Because we are creating a large Worksheet, we use the technique of Freezing Panes. That helps you to keep your titles in view as you scroll through the data of the sheet.

We learned the value of using Relative and Absolute references in our formulas. In parts of our formulas that reference our Assumptions, we have to use Absolute reference, so that formulas copied across the 6 months with the Fill Handle will always reference the specific cells of our Assumptions. Notice that the F4 Key is a useful utility for painting $ signs around those parts of our formulas where we need to maintain Absolute Reference.

You can see that the IF FUNCTION helps in making decisions about when to pay out the Bonus to the employees of the company. The AUTOSUM FUNCTION gets the MVP award in this worksheet! And the TODAY FUNCTION also helps to inform us of what day we printed the Worksheet.

We formatted in ways that make the worksheet legible, presentable, and logical. We used the Currency Format (rather than the Accounting Format) in first lines and total lines of dollar figures - this is standard for a financial document, like an Income Statement. We also used the Comma Format for all numeric data in between first lines and totals

A picture helps to understand the data. The Three Dimensional Pie Chart is perfect for this project because it makes it easy to see the six monthly totals graphically. Pie Charts are great when you have limited data.

The Chart Tools Layout Tab helped to:

• Insert a Chart Title
  
• Insert Data Labels
  
• Rotate the Chart
  
• Apply a Three-Dimensional Format with beveling and a special effect
  
• Explode the Pie Chart
  
• Change the color of each slice

In addition we:

• Renamed our Sheet Tabs, reordered them, and added color to each
  
• Ran a Spell Check
  
• Ran a Print Preview to make sure that the Print Job would look OK
  
• Split the Window into four panes, in order to view different parts of the sheet
  
• Analyzed the data by changing the values of some of our Assumptions
  
• Ran Goal Seek to force the result of one cell by changing another

This Project is filled with interesting examples of how to set up a good worksheet, how formatting enhances the information, how to 'Chart' it, and how to use the analytic capabilities of Excel for making predictions. Each operation by itself is a small addition to the entire picture. But all together the result becomes a powerful means of presenting information!

Materials Covered: MS Excel - Project 3:
What-if Analysis, Charting, and Working with Large Worksheets, pp EX 161-228