13. Making 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 saw 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 that contain 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.

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