The Spreadsheet System That Runs a Guide Business

Congress wrote a legal definition of the thing this page tells you to build. A system of records means a group of any records from which information is retrieved by the name of the individual, or by some identifying number or symbol assigned to them.

That is a spreadsheet keyed by angler name, described in statute. The duties the Privacy Act attaches to such a system bind federal agencies and nobody else, and they are the two things a client list is actually for: holding only what is relevant and necessary, and holding it accurately enough to be fair to the person it describes. Elsewhere, the food and drug rules for electronic records supply the other half, which is what an electronic record has to do before anybody trusts it. Below both are read from the statute and the regulation as models rather than requirements, then the spreadsheet itself. Neither applies to you and none of this is legal advice. Adjacent reading is indexed at the running the business hub.

What is a system of records?

Defined by how you get information out of it.

Section 552a(a)(5) of Title 5 defines a system of records as a group of any records under the control of any agency from which information is retrieved by the name of the individual, or by some identifying number, symbol or other identifying particular assigned to the individual.

The definition turns on retrieval rather than on format, size or software, which is why it describes a spreadsheet as readily as a database.

The same section defines a record broadly, as information about an individual including education, financial transactions, medical history and employment history, containing the person's name or an identifying particular such as a photograph.

Which is a guide's client list precisely: names, what they paid, what they told you about their health, and a folder of photographs.

Section 552a is published by the Law Revision Counsel.

What that list should contain is covered in the client database piece.

DM Fly Fishing, TX

What duties attach to it?

Two that are worth adopting voluntarily.

Subsection (e)(1) requires an agency to maintain in its records only such information about an individual as is relevant and necessary to accomplish a purpose it is required to accomplish.

Subsection (e)(5) requires it to maintain all records used in making any determination about any individual with such accuracy, relevance, timeliness and completeness as is reasonably necessary to assure fairness to the individual in the determination.

Read those as a design brief for a client sheet and they produce a short, current list rather than a long, stale one.

Only what is relevant and necessary rules out the fields you collect because a form template had them, and accuracy sufficient to assure fairness rules out the note written from memory in February.

Neither is a legal obligation on a guide, and both describe the sheet you would want if you had to defend a decision made from it.

Why every extra field costs you is covered in the client database piece.

Fly and Fish with Fraser, FL

What does the electronic records rule ask?

Ten things, and four of them apply to a spreadsheet.

Section 11.10 of Title 21 requires persons who use closed systems to create, modify, maintain or transmit electronic records to employ procedures and controls designed to ensure the authenticity, integrity and, when appropriate, the confidentiality of those records.

Paragraph (b) requires the ability to generate accurate and complete copies of records in both human readable and electronic form suitable for inspection, review and copying.

Paragraph (c) requires protection of records to enable their accurate and ready retrieval throughout the retention period, and paragraph (d) requires limiting system access to authorised individuals.

Paragraph (a) requires validation of systems to ensure accuracy, reliability, consistent intended performance and the ability to discern invalid or altered records.

Section 11.10 is carried on the eCFR.

How access should be limited in practice is covered in the client database piece.

What is the audit trail requirement?

The one a raw spreadsheet fails outright.

Paragraph (e) requires the use of secure, computer-generated, time-stamped audit trails to independently record the date and time of operator entries and actions that create, modify or delete electronic records.

It then adds the sentence that matters: record changes shall not obscure previously recorded information.

And it requires the audit trail documentation to be retained for at least as long as the records themselves, and to be available for review and copying.

A spreadsheet on a laptop fails every limb of that, because a cell overwritten is a cell gone and nothing anywhere records that it changed.

A hosted sheet keeps every prior version, which meets the spirit of that requirement at no cost and is the reason the master copy should never live only on a laptop.

Why hosted beats local generally is covered in the tooling piece.

What should the sheet look like?

Two tabs, and the second one is the point.

A People tab, one row per client, keyed by name, holding the small set of fields you act on: contact details, state, last trip date, the weeks fished, party composition, amount paid and contact preference.

A Trips tab, one row per trip, holding date, water, party, conditions, amount, and a line about how it went and what they said they wanted next.

The People tab is derived from the Trips tab rather than maintained separately, which is what stops the two drifting apart.

Everything a follow-up decision needs lives on the first tab; everything the record needs to establish lives on the second.

Two tabs, nine and seven columns, and no third tab.

What the trip record must be able to show is covered in the debrief piece.

Why not one tab?

Because the two have different lifespans.

A client row is maintained and pruned; a trip row is written once and kept for as long as its own retention period requires.

Put them together and pruning becomes impossible, since removing a client would take a day of evidence with it, and the sheet simply accumulates.

It also breaks the accuracy duty in the model above, because a client who fished four times has four sets of conditions and one set of contact details, and forcing those into one row loses something either way.

Separating them costs one tab and makes both jobs possible.

Which is the whole of the design, and almost nobody does it.

The trip side has its own retention clock, set out in the fall wrap-up piece.

How should it be shared?

By view, not by file.

The access limitation in the electronic records model is not about passwords, it is about limiting people to what they need, and a spreadsheet makes that easy in one way and impossible in another.

Sending a copy of the file to a sub-guide is impossible to withdraw and impossible to update, and it puts your whole client list on somebody else's laptop permanently.

Sharing a filtered view of the Trips tab for a given week gives them what they need, stays current, and can be turned off.

Where the tool does not support that, a separate small sheet for the day is the low-technology equivalent and takes two minutes.

What must not happen is the master file leaving your control, because that is the one action you cannot undo.

Documenting that engagement properly is the first sub-guide piece.

What about formulas?

Three, and they should compute the things you would otherwise guess.

Days since the last trip, computed from the trip date, because nobody wants to work that out by eye across two hundred rows.

Trips per client, counted from the Trips tab, which distinguishes a returning client from a one-off without any judgment.

And total paid, summed the same way, which is the only honest measure of who your best clients actually are.

Those three turn a list into something you can sort, and sorting is the entire practical value of a client sheet.

Anything more elaborate is a tool pretending to be a spreadsheet, and at that point buy the tool.

Which figures matter across the season is covered in the numbers piece.

When should it be updated?

Same day, from the truck, before the details go.

The accuracy duty in the model is about fairness to the person the record describes, and a record written from memory in February is not accurate about anybody.

So the update belongs at the takeout as a five minute habit rather than in a pile of admin, and every message you send later is better for it.

Where a phone is the only device available, a note that gets transcribed weekly is better than nothing and worse than typing it once.

What matters is that the trip row exists before the next trip does, because two undocumented days become four and then a season.

The operations that keep good lists are not more organised; they write it down the same day.

What else belongs in that habit is covered in the debrief piece.

How should the columns be ordered?

By how often you look at them, left to right.

A sheet is read on a phone in a car park as often as on a desk, and the columns that matter are the ones visible without scrolling sideways.

Which puts name, telephone and days since last trip in the first three positions, because those are the three a follow-up decision needs.

Everything computed goes next, then everything descriptive, then everything you keep but rarely read.

Freezing the first row and the first column costs one menu click and makes the sheet usable at four hundred rows instead of forty.

None of that is clever and all of it is the difference between a sheet you consult and one you avoid.

Which fields a follow-up genuinely needs is the rebooking pack piece.

What should the dates look like?

Real dates, in one format, entered as dates.

A column of trip dates typed as text is a column you cannot sort, cannot subtract and cannot compute days-since from, which removes the entire value of the sheet.

Which means setting the column to a date format once, entering everything the same way, and never typing last June into it.

The same applies to money, where a column mixing 650 and six fifty and $650 will not sum.

Those two disciplines take one minute to establish and are the most common reason a guide's sheet cannot answer a question it obviously contains.

Validation on both columns prevents the problem rather than correcting it later.

Which numbers the sheet should be able to produce is covered in the numbers piece.

How should mistakes be corrected?

By adding, not by overwriting, where the record matters.

The audit trail principle says record changes shall not obscure previously recorded information, and a spreadsheet obscures by default because a corrected cell shows only the correction.

For contact details that is fine, since the current number is the only one anybody needs.

For a trip row it is not, because what happened on a day should not change after the day, and a corrected amount or a rewritten note is a different claim about the same trip.

Which suggests a simple rule: the People tab may be edited freely, and the Trips tab is append-only except to fix a typo.

Where a trip genuinely needs restating, add a note with today's date rather than editing the original line.

Why a contemporaneous record matters is covered in the chargebacks piece.

What breaks a spreadsheet list?

The second copy, before anything else.

A file emailed to somebody, which becomes a second master that immediately diverges and cannot be recalled.

One tab doing two jobs, so the list can never be pruned without destroying trip evidence.

Fields collected and never used, which the relevant-and-necessary standard in the model rules out and which lower completion everywhere.

Kept on a laptop rather than hosted, so a cell overwritten is gone and there is no version history to show what changed.

And updated in batches from memory, which produces a record that is neither accurate nor timely about anybody.

What should never be in the sheet at all is covered in the client database piece.

Driving a winter sequence off it is the winter email piece.

What about a backup?

A second copy somewhere the first cannot reach.

The retrieval requirement in the electronic records model is about being able to get the record back throughout its retention period, and a hosted sheet covers hardware failure while doing nothing about deletion.

Deletion is the likelier accident, whether that is a row removed, a column sorted badly or an account lost, and version history helps only if the file still exists.

So export a copy to a different service or a local archive on a recurring schedule, arranged once so it runs without your attention.

Testing it once is what separates a backup from a belief, and the test is to open that copy and look for whoever you entered most recently.

Thirty seconds, once, against a list that produces a five-figure share of your bookings.

What that list is worth is covered in the repeat client piece.

Should the sheet hold the photographs?

No, but it should hold the link.

Images in cells make a sheet slow, unsortable and impossible to export cleanly, and they duplicate a library that already exists in folders.

What works is a column on the Trips tab holding the folder link for that day, so the record points at the evidence rather than containing it.

Which also satisfies the retrieval principle better, because a link to a maintained folder survives longer than an embedded thumbnail.

Naming those folders by date and water rather than by client makes them findable from the trip row and from the calendar alike.

One column, one link, and the photographs stay where they belong.

How the folders themselves should be organised is covered in the photo delivery piece.

What is the working sheet?

Hosted, two tabs, three formulas, updated the same day.

Keep it in a hosted spreadsheet so version history, access control and a backup come free, and never on a device as the only copy.

Run a People tab keyed by name and a Trips tab keyed by date, with the first derived from the second.

Add three formulas: days since last trip, trips per client and total paid, and sort by them rather than reading down the list.

Share filtered views rather than the file, and write the trip row the same day rather than in batches.

And hold only fields you act on, because everything else is something to protect, prune and eventually explain.

The Privacy Act provisions quoted above are also published on govinfo.

What the whole database should look like is covered in the client database piece.