Sensitivity tables for price, repairs, and time

Begin with the decision the table should support

A sensitivity table recalculates a model while selected assumptions change. It shows how dependent the result is on those inputs. It does not tell you how likely a scenario is, identify the correct input automatically, or guarantee that the tested range contains every possible outcome. Its value comes from helping you make a concrete decision: what price can you pay, which inspection is worth doing, how much cash must stay available, or when should the project be reconsidered?

Choose an output before arranging rows and columns. For a resale project, expected project profit in dollars is often a useful first output. Add peak cash needed or the lowest projected cash balance when funding is a concern. For a rental, annual cash after debt and replacement funding may be more informative than resale profit. Avoid filling a screen with several ratios before deciding which result would actually change your behavior. The table should answer a question you can state in one sentence.

This guide uses a hypothetical house renovation and resale. All values are illustrative assumptions, not market forecasts or a recommended return. The base model is intentionally simple enough to audit by hand. We will vary sale price, repairs, and time, preserve the formulas, and then examine what the model excludes. Real projects can require more detail, but a larger worksheet should still pass the same test: another person can trace any displayed result to the inputs and explain why it changed.

Write the base equation and keep units visible

Assume a $180,000 acquisition price, $40,000 renovation budget, $6,000 separate contingency, $5,000 acquisition costs, and a $300,000 gross resale price. Selling costs equal 7% of resale price in this scenario. Carrying expense is $2,500 per month, including the deliberately simplified financing and nonfinancing amounts used here, for six months. No additional price-linked interest, income, or loan charges will be added elsewhere. Base profit is $300,000 times 0.93 minus $180,000 minus $40,000 minus $6,000 minus $5,000 minus $15,000, or $33,000.

Write the equation as profit equals 0.93 times sale price minus purchase price minus repairs minus contingency minus acquisition costs minus monthly carry times months. Label dollars, monthly dollars, and months on the worksheet. A table can look polished while mixing annual interest with monthly holding periods or applying a percentage twice. Keep the base inputs in one place and reference them from formulas. Separate input cells from calculated outputs so a reviewer can see what is being tested and what is being derived.

The $2,500 monthly carry is fixed for the first teaching pass. In practice, interest can depend on loan balance and draw dates, insurance can have minimum premiums, and some property expenses arrive in large installments. Start simple, then replace the allowance with the detail needed for the actual decision. Do not claim a table is conservative just because it includes many rows. Conservatism comes from justified assumptions, complete costs, and a plan for the cash timing those assumptions produce.

Vary one input to learn the model’s behavior

First hold everything constant except resale price. At $280,000, net modeled sale proceeds after the 7% deduction are $260,400, and profit is $14,400. At $300,000, profit is $33,000. At $320,000, profit is $51,600. Each $20,000 sale-price change moves profit by $18,600 because 7% of the change is absorbed by the assumed selling cost. This is the model’s local response, not evidence that any of the sale prices will be achieved.

Next change repairs alone. At $30,000, profit is $43,000; at $40,000, it is $33,000; at $50,000, it is $23,000. Every extra repair dollar reduces profit by one dollar in this simplified model. That relationship would change if the additional work also altered resale value, carrying time, or financing. Keep a controlled one-variable test separate from a broader scope scenario. Otherwise it becomes impossible to tell whether the output changed because of cost, benefits, or several assumptions moving at once.

Finally vary the holding period at the base price and repair budget. Four months produces $38,000 profit, six months $33,000, and eight months $28,000. The $5,000 difference per two months comes from the assumed $2,500 monthly carry. This clean pattern is a useful error check. If the spreadsheet produces a different pattern while all other inputs remain fixed, inspect its references. Regular mathematical relationships help verify a model before adding the complexity of changing draws, fees, or project milestones.

Build a two-variable table with a clear base intersection

Put sale prices in columns and repair budgets in rows, keeping time fixed at six months. Use three prices, $280,000, $300,000, and $320,000, and three repair amounts, $30,000, $40,000, and $50,000. The center cell is the $33,000 base case. That single visible base intersection makes the table easier to audit. A reader should know what remains constant without searching another page or guessing whether the contingency changes with repairs.

The row for $30,000 repairs yields profits of $24,400, $43,000, and $61,600 across the three sale prices. The $40,000 row yields $14,400, $33,000, and $51,600. The $50,000 row yields $4,400, $23,000, and $41,600. These are nine conditional results from one equation, not nine independently underwritten projects. Keep the separate $6,000 contingency fixed in all cells for this test, and say so. If you change contingency policy, create a new table rather than altering it silently.

Apply colors only after the numbers are correct. You might mark results below a chosen $25,000 planning profit as requiring review, but label the threshold as your decision criterion. A green cell means the model passes that criterion under those assumptions; it does not certify the investment’s safety. A table with every cell green may simply use a narrow range, omit a material cost, or set an undemanding threshold. Review the choices that produced the display before responding emotionally to its colors.

Connect the third variable to an execution story

A two-dimensional table cannot display every variable clearly. Rather than squeezing time into tiny labels, create separate four-, six-, and eight-month panels or calculate a handful of named project scenarios. A scope-related delay might simultaneously increase repair spending and carrying expense. A slower sale might extend carry without changing repairs. These are different causal stories and deserve different rows. The goal is to show what could happen together, not to imply that every combination of inputs is equally plausible.

For a combined adverse scenario, assume a $280,000 resale, $50,000 repairs, and an eight-month hold. With the same acquisition price, contingency, acquisition expenses, and 7% selling deduction, profit is negative $600. The calculation is $260,400 net sale proceeds less $180,000 purchase, $50,000 repairs, $6,000 contingency, $5,000 acquisition expenses, and $20,000 carry. That small loss contrasts with the $33,000 base profit because three unfavorable changes occur together. The scenario illustrates exposure without predicting an actual outcome.

Compare it with a targeted intervention. If verified scope planning can hold repairs at $40,000 while the same lower sale price and eight-month hold occur, profit becomes $9,400. If a purchase-price reduction of $10,000 is achievable without other changes, that adds another $10,000 to every displayed scenario in this simplified model. Identifying which actions shift the whole table is more useful than merely observing its weakest corner. Each proposed intervention still requires evidence that it is available and executable.

Calculate breakpoints rather than arguing about one forecast

To find the break-even gross sale price in the six-month base case, add all costs other than selling costs: $180,000 plus $40,000 plus $6,000 plus $5,000 plus $15,000 equals $246,000. Divide by 0.93 to obtain approximately $264,516. This is economic break-even under the stated inclusions, before any omitted overhead or taxes. It is not the price a buyer will pay or a guarantee that the property can sell. Compare the threshold with supported sale evidence and the range of uncertainty.

If the target profit is $25,000, the required gross resale price becomes $271,000 divided by 0.93, or approximately $291,398. At the base $300,000 resale, maximum repairs consistent with that target are $48,000, holding all other base inputs constant. Likewise, base costs excluding monthly carry total $231,000; $279,000 net proceeds less that amount and the $25,000 target leaves $23,000 for carry. At $2,500 per month, the model reaches the target boundary at 9.2 months.

These boundaries give diligence a purpose. If the likely repair scope is close to $48,000, a detailed inspection may change the decision. If execution could reasonably extend beyond 9.2 months, investigate the schedule and financing deadline rather than debating whether six months feels realistic. Breakpoints also clarify tradeoffs: a lower price buys additional repair or time capacity, while a higher required profit reduces it. The calculation does not remove judgment; it shows exactly where judgment matters to the decision.

Add liquidity and nonlinearity before relying on the result

Profit tables do not show when bills must be paid. Suppose the renovation requires substantial deposits immediately, while the financing reimburses work later. Two projects with identical final profit can require different amounts of owner cash. Add a dated cash schedule and identify the maximum cumulative cash contribution. Do not reduce that need by assuming the contingency can be spent twice or by counting anticipated sale proceeds before closing. Liquidity is a separate output with its own sensitivity to payment timing and delays.

Time costs can become nonlinear near deadlines. An illustrative loan extension might add a fixed fee once a certain month is reached, and an insurance renewal might require another premium payment. A table that simply multiplies $2,500 by months would miss those jumps. Model such charges only when supported by the actual terms or a clearly labeled scenario. Show the month when the additional cost appears. A narrow monthly average can obscure a large cash requirement or a contractual boundary that changes the available choices.

The same principle applies to repairs. A component might be repairable below one condition threshold and require replacement beyond it. Additional work may also improve the sale condition, but that benefit needs separate evidence rather than a dollar-for-dollar assumption. Use scenario branches when the project changes character. A simple linear table is appropriate for incremental changes within one plan; it becomes misleading when crossing into a substantially different scope, use, funding arrangement, or exit strategy.

Make the table a management instrument

Before finalizing, test the formula manually at the base cell and at one adverse corner. Check that a higher sale price increases profit, larger repairs decrease it, and more time increases carry under the intended assumptions. Change an input temporarily and verify every relevant output updates. Preserve one controlled version of the model with the assumptions and evidence date. A static screenshot can communicate the findings, but the editable source is what lets a reviewer reproduce them and update the decision after diligence.

Read the table as a research priority list. Identify the inputs with the largest consequence and weakest support, then assign a verification action, owner, and deadline. Ask whether an input range is based on actual scope, comparable evidence, a contractual term, or simply a round percentage. Do not average the cells and call the result expected profit unless the probabilities and method are justified. An unweighted collection of selected scenarios is not a probability distribution, and its average has no automatic forecasting meaning.

The useful takeaway is an action boundary: the proposed price requires repairs below a stated amount, completion within a stated time, and sale evidence supporting a stated range. If those conditions fail, revise the price, scope, funding, or decision. Revisit the table as quotes, inspections, and schedule information improve. The best table is not the one with the most cells; it is the one that turns uncertainty into specific questions and prevents the project from drifting beyond the limits you can carry.

• State the decision and output before selecting ranges.
• Audit the base equation and one adverse corner.
• Keep controlled sensitivities separate from linked project scenarios.
• Show target-profit and break-even thresholds.
• Add a cash calendar and deadline-related cost jumps.
• Assign verification work to consequential weak inputs.