Bad And Good Formula Technique

Bad & Good Formula Writing Techniques Contents:

4th Quarter - Bad Version Bad Formulas Displayed Good Version Good Formulas Displayed A simple worksheet displays the danger of using actual values in Excel formulas and how to correct this critical design flaw.

Return to Contents

Current

Projected

Revenue Internet Access Subscriptions E-mail & Bulletin Board Service Web Page Disk Space Rental Monthly Revenues

35,000 4,500 1,000 $81,000

1,750.00 225 50 $2,025.00

Expenses Salaries Rent Equipment Maintenance Advertising Operating Costs Monthly Expenses

100,200 3,000 5,820 3,560 8,000 $120,580

5,210.40 156 302.64 185.12 416 $6,270.16

$(39,580.00)

$(4,245.16)

Profit

Poorly-written formulas

Return to Contents

Return to Contents

Projected October Revenue Internet Access Subscriptions1,750.00 E-mail & Bulletin Board Service 225 Web Page Disk Space Rental 50 Monthly Revenues $2,025.00 Expenses Salaries Rent Equipment Maintenance Advertising Operating Costs Monthly Expenses Profit

5,210.40 156 302.64 185.12 416 6270.16 $(4,245.16)

Problems: 5% is entered into each formula. If the value changes, each formula must be changed manually.

SUM formulas list the values to sum. If the values change, each SUM formula must be changed manually. Solutions: Put the projection values in one place in the spreadsheet. Refer to those values' cells in formulas. In SUM formulas, use cell references, not actual values.

Current

Projected

Revenue Internet Access Subscriptions E-mail & Bulletin Board Service Web Page Disk Space Rental Monthly Revenues

35,000 4,500 1,000 $40,500

1,750 225 50 $2,025

Expenses Salaries Rent Equipment Maintenance Advertising Operating Costs Monthly Expenses

100,200 3,000 5,820 3,560 8,000 $120,580

5,210.40 156 302.64 185.12 416 $6,270

$(80,080.00)

$(4,245.16)

Profit

Revenue projection index v Expense projection index va

Correctly-written formulas

Revenue projection index value: Expense projection index value:

Return to Contents

5% 5.20%

Return to Contents

Current

Projected

Revenue projection index 5% value: Expense projection5.20% index value:

Revenue Internet Access 35,000Subscriptions 1,750 E-mail & Bulletin 4,500 Board Service 225 Web Page Disk 1,000Space Rental 50 Monthly Revenues ### $2,025 Expenses Salaries 100,200 5,210.40 Rent 3,000 156 Equipment5,820 Maintenance 302.64 Advertising3,560 185.12 Operating Costs 8,000 416 Monthly Expenses ### $6,270 Profit

###

$(4,245.16)

Correctly-written formulas