We have a spreadshee that is a simple spreadsheet we’ve used for literally thousands of project proposals, business plans, and random financial analyses. If you would like a copy in Excel format, download it from our Web site, www.netbanker.com or send an e-mail to bizcase@netbanker.com  and we’ll e-mail a copy. Finally, if you prefer a diskette, let us know and we’ll drop one in the mail to you. E-mail your request to diskette@netbanker.com or call 206.517.5021. Following is a line-by-line explanation of the spreadsheet.

• All revenues and costs variables are in US$ per month per subscriber household (each household could have more than one user).
• This analysis was designed for retail customers, but it could be adapted to business customers.
• Following finance conventions, all revenues and expenses (both fixed and variable) are assumed to have incurred at year-end of each period.
• No terminal, or end value is used in the NPV calculation.

1. Discount Rate: This is the rate used to discount future cash flows to calculate Net Present Value (NPV). Your finance department can supply this number. If your company does not use discounted cash flow analysis, input zero or simply ignore the discounted cash flows.

2. Percent New and Saved HHs: The combined percentage of total OLB subscribers that either are new to your bank because of your OLB offering; or have not left your bank (i.e. “saved) because of the OLB program. If you don’t care to make this distinction plug a zero.

3. Direct Revenue: These are fees assessed specifically for online services such as bill payment, account access, archive searches, etc.

4. OLB Monthly Fee: This is the average monthly flat subscription fee assessed for using online banking.

5. OLB Transaction Fee: This is the average amount charged to users for online transactional services in addition to, or in lieu of, monthly fees in #5 (e.g., fee for e-mail notifications).

6. Other Direct Fees: Any other per-subscriber fees, such as commissions from other service providers, referral fees, etc.

7. Indirect Revenues: Incremental revenues from other products sold online, or to online households, that would not have been sold without an online banking program. On our model they are presented “below the line” (lines 40-48) because their revenues will likely accrue to other products/departments. Nevertheless they are critical components of online banking profitability. In our model, the values are expressed in before tax dollar contributions.

8. Contribution per New HH: The total net contribution (variable revenues less variable costs) provided by households new to the bank because of OLB (#2 above).

9. Incremental Contribution per Existing HH: The impact of the online sales channel is recognized here. Estimate incremental product sales to OLB households, then calculate the annual profit contribution from the incremental sales.

10. Variable Expenses: Expenses incurred to service each subscriber. In this analysis they are assumed to vary directly with volume (e.g., the variable cost of subscriber #1 equals the variable cost of subscriber #1,000 or #10,000).

11. Processing Cost: Average variable cost, both internal and from vendors, to process OLB inquiries, statement generation, funds transfers, e-mails, bill payments etc. (everything except customer service below).

12. Customer Service Cost: Average cost to service customer questions through e-mail and telephone. Include salary, benefits, work stations, telcom, and occupancy. Also include the cost of mailings to educate users, communicate program changes, explain new features, etc. In our example, we assume average customer service costs fall over time as both the bank and OLB users get through the learning curve.

13. New Account Set-up: One-time cost to set-up new subscribers on the system including collateral material, software distribution, and any vendor charges. Initial customer education could be included here or averaged into #12.

14. Total New OLB Subscribers: Enter your yearly OLB sales forecast.

15. Closed Accounts: Enter your forecast for closed OLB accounts.

16. Total Year End # OLB Subscribers: Calculate from line14 less line15.

17. Existing HHs (year-end): Households you would have with or without OLB. Spreadsheet calculation = line16 x (1- line2).

18. New HHs (year-end): Incremental HHs because of OLB.
Calculation = line16 x line2.

19. Average Annual # OLB Subscribers: Assumes sales and closures occur at a constant rate over the year. Calculation = previous year line16 + current year line16 divided by 2.

20. OLB Monthly Fees: Calculation = line19 x line4 x 12 months

21. OLB Transaction Fees: Calculation = line19 x line5 x 12 months

22. Other Direct Fees: Calculation = line19 x line6 x 12 months

23. Total Revenue: Calculation = sum of line 20, 21 & 22

24. Fixed Costs: Summary of the costs that do NOT vary with volume.

25. Management: Enter your annual direct management expense for operating the program (product, operations, legal, customer service).

26. Systems Development/Integration: Fixed costs for development and annual systems/maintenance costs to run OLB (if any).

27. Marketing: Enter your annual OLB marketing, brochure, statement insert, online advertising expenses. Enter only the marketing costs associated with producing the subscriber volumes in the forecast.

28. Overhead: Enter annual overhead allocations (if required).

29. Vendor Licensing Fees: Enter any fixed annual vendor payments. Variable (per subscriber) fees should be included in lines 32-24.

31. Variable Costs: Expense items that vary with volume.

32. Transaction Processing: Calculation = line19 x line11 x 12 months

33. Customer Service: Calculation = line19 x line 12 x 12 months

34. New Account Set-up: Calculation = line14 x line13

37. Net Income (before tax): Calculation = line23 minus line36

38. Net Income (after tax): Calculation = line37 x 0.60

39. Discounted Net Income: Calculation = (line 37) ¸ (1 + line1)^year

40. Indirect Income: Income that doesn’t show on the OLB P&L such as online sales of other products or intangible benefits.

41. Incremental Contribution from Existing HHs: Assumes contribution begins in second year. Calculation = prior year’s line19 x line9

42. Contribution from New HHs: Assumes contribution begins in second year. Calculation = line19 x line8

43. Other Indirect Revenue: This is a catch-all to quantify any other indirect benefits of OLB, such as PR value, image value, implicit fees earned on other products, etc.

44. Cost of Additional Risks: An annual “reserve” for catastrophic losses.

45. Total Indirect Profit (before tax): Calculation = sum of line 41 to 44

46. Total Indirect Profit (after tax): Calculation = line45 x 0.60

47. Net Direct & Indirect Income (before tax): Calculation=line45+line37

48. Discounted Direct & Indirect Cash Flow:
Calculation = line 47 ¸ (1 + line1)^year