Formulas in Excel
The use of formulas in spreadsheets has been credited with the success of moving microcomputers into the mainstream of modern computer usage. Formulas enabled financial officers to quickly and accurately compute the business "what if's.." that are important to decision-making even today.
We will examine some ideas with formulas in Excel and you should be able to see some excellent opportunities to calculate both simple and complex models.
First, we should point out that like other calculations in Excel, a formula is usually preceded with the = sign. That is, the expression =2 + 4 will cause Excel to calculate the sum of the two numbers and display the results as 6.
Second, we should point out that the power of formulas comes in when we use placeholders rather than actual numbers in the formula. For example:
=A3 + B3
directs Excel to add the contents of Cell A3 and the contents of B3 and put the sum in a different cell.
Look at this example where the formula in Cell A3 was actually entered in cell B3.
=B1 + B2 If someone enters 6 in Cell B1, the value of B3 is instantly calculated to display the sum of the current values of B1 and B2.
=B1 + B2 The person who entered 6 could be asking...."What happens to the total if this value was 6?" By setting up a formula in Cell B3 that directs Excel to add the cell contents rather than add two specific numbers, Excel lets you see the effects of changing values in a formula.
Suppose the model were a little more complex. Assume a manager is wishing to give raises to the employees in the company. It might be nice to have a spreadsheet to see what happens to the salaries if the percent raise was 2%, 2.5%, or 3%. The manager could determine from the "bottom line" how much raise the company can afford to give.
Enter Raise--> Names Results Bill Fred Joe Total Payroll The formulas would actually be entered in Column C. Each formula directs Excel to compute (in this case):
1.02 X Current Salary
to get the new salary. If the manager decides the company could stand the extra $1,890 for raises, the percent raise might be checked at 2.5%. Just as fast as the Enter key is pressed, the results would show the new calculations.
Enter Raise--> Names Results Bill Fred Joe Total Payroll Instantly, the manager would see that a 2.5% raise would cost $2,363.00 more in gross pay. Obviously different values could be tried until the budget was broken!
While a real financial forecast spreadsheet would have more employees and need the benefits offered by the company included in the financial model, the "instant" results of changing the percent in a single cell would give specific numerical data for financial decision-making.
Depending on the situation, a formula may have both "placeholder" values and constants. If you know that a number in a formula will never change, it probably will be entered as a constant. If the number in a formula will depend on the current value of a cell in the spreadsheet, you will probably use the cell address. If your spreadsheet requires "lot's" of formulas, you will appreciate all of the things Excel has done for you. We will explore how to copy formulas with little or no effort in another tutorial.
Hint with formulas: Some formulas can be quite complicated. It would be easy to type a single keystroke wrong. The keypunch error may not "crash" the formula. After you have entered all formulas in the spreadsheet, it is wise to "check" your work by entering into a cell a number that you know what the result should be when calculated with your formula(s). If you get the correct result with that number, you probably will get the correct result when you use the real value in that cell.