Blog · Territory, capacity and quota planning · Construction
Development appraisal sensitivity analysis compares project profit and funding needs under supplied sales, cost, and timing assumptions. Work through a US residential development example, spreadsheet formulas, and a cash reconciliation.
Development appraisal sensitivity analysis measures how supplied sales, cost, and timing assumptions change a development project's profit and cash needs. Start with a reconciled base appraisal, change one assumption at a time, then test a combined downside. Keep the profit calculation separate from the timing of payments and receipts.
This example is a US company's proposed residential development for sale. It evaluates project economics, not a property investment recommendation or a residual land-valuation method. The company supplies the quantities, prices, costs, and schedule assumptions.
Use a consistent project scope and currency. Record which costs are included, whether estimates include contingency, and whether carrying costs already contain interest. Show income tax and financing cash flows separately when they belong in the company's decision process.
The OCC's Commercial Real Estate Lending handbook discusses feasibility, cash availability throughout development, and stress testing under changing conditions. The practical implication is to test timing as well as the final profit total.
For the general definition, see sensitivity analysis. The development-specific job is tying each changed assumption to both the cost appraisal and the cash schedule.
Bring the unit sales schedule, construction budget, land cost, soft-cost budget, selling-cost estimate, and monthly cash schedule. Retain unit or phase identifiers, cost code, amount, expected payment month, and source version. Separate actual spending, committed spending, and remaining estimates so the same obligation appears only once.
The project team supplies the following simplified inputs. Costs are USD, sales occur together at completion, and the monthly carrying allowance includes the assumed interest and holding costs. Income tax, debt draws, and principal repayments are excluded from this project-profit illustration.
| Spreadsheet row | Input | Supplied value |
|---|---|---|
| B2 | Units sold | 20 |
| B3 | Selling price per unit (USD) | 500,000 |
| B4 | Land cost (USD) | 2,000,000 |
| B5 | Hard construction cost (USD) | 4,500,000 |
| B6 | Soft costs (USD) | 800,000 |
| B7 | Selling costs (USD) | 300,000 |
| B8 | Project duration (months) | 18 |
| B9 | Monthly carrying cost (USD) | 40,000 |
The carrying allowance is a supplied simplification. A detailed appraisal may calculate interest from dated loan balances and rates instead. Use one method consistently and avoid adding interest again if it is already in the allowance.
Development profit = Sales revenue - Total development cost
Profit margin = Development profit / Sales revenue
Break-even selling price per unit = Total development cost / Units sold
For the input layout above, enter these Excel formulas:
B10=B2*B3
B11=B8*B9
B12=SUM(B4:B7)+B11
B13=B10-B12
B14=B13/B10
B15=B12/B2
Microsoft's SUM documentation confirms that the function adds values or ranges. Here it combines the cost categories before adding carrying cost. Format B14 as a percentage and keep the underlying values unrounded.
| Output | Calculation | Result |
|---|---|---|
| Sales revenue (USD) | 20 × 500,000 | 10,000,000 |
| Carrying cost (USD) | 18 × 40,000 | 720,000 |
| Total cost (USD) | 2,000,000 + 4,500,000 + 800,000 + 300,000 + 720,000 | 8,320,000 |
| Profit (USD) | 10,000,000 - 8,320,000 | 1,680,000 |
| Profit margin | 1,680,000 / 10,000,000 | 16.8% |
| Break-even price per unit (USD) | 8,320,000 / 20 | 416,000 |
The proof is the identity: $8,320,000 of costs plus $1,680,000 of profit equals $10,000,000 of sales. The break-even price holds only while the unit count and all stated costs stay unchanged.
Reduce price by 5%, from $500,000 to $475,000. Separately increase hard costs by 10%, from $4,500,000 to $4,950,000. For the delay case, extend duration by three months, from 18 to 21. Keep every other input fixed in each individual test.
| Case | Revenue (USD) | Total cost (USD) | Profit (USD) | Profit margin |
|---|---|---|---|---|
| Base | 10,000,000 | 8,320,000 | 1,680,000 | 16.80% |
| Price reduced 5% | 9,500,000 | 8,320,000 | 1,180,000 | 12.42% |
| Hard costs increased 10% | 10,000,000 | 8,770,000 | 1,230,000 | 12.30% |
| Completion delayed three months | 10,000,000 | 8,440,000 | 1,560,000 | 15.60% |
| All three changes combined | 9,500,000 | 8,890,000 | 610,000 | 6.42% |
Margins in this table are rounded to two decimal places. The price case loses $500,000 of revenue. The hard-cost case adds $450,000 of expense. The delay adds 3 × 40,000 = $120,000 of carrying cost. Combined profit is therefore 1,680,000 - 500,000 - 450,000 - 120,000 = $610,000.
Selling costs stay at the supplied $300,000 in every case. If commissions vary with selling price, change that formula too and label the relationship. Scenario analysis in Excel covers organizing broader linked cases; use DCF sensitivity analysis when discounted cash-flow value is the separate output being tested.
A positive final margin does not establish that the company can fund construction. Under the stated assumption that all sales close together, the base project's cash stages are:
| Cash stage | Net cash flow (USD) | Cumulative project cash (USD) |
|---|---|---|
| Land purchase | -2,000,000 | -2,000,000 |
| Hard costs, soft costs, and carrying costs before receipts | -6,020,000 | -8,020,000 |
| Sales receipts less selling costs at closing | 9,700,000 | 1,680,000 |
The second row is 4,500,000 + 800,000 + 720,000 = $6,020,000. Closing cash is 10,000,000 - 300,000 = $9,700,000. Final cumulative cash matches the $1,680,000 profit because this simplified schedule pays and receives every included item.
Before financing, the base funding gap is $8,020,000. A three-month delay adds $120,000, taking that gap to $8,140,000 while receipts remain unchanged. A real phased development needs dated payments and closings; construction project cash flow covers that schedule.
Bring a sample of your own sales schedule, development budget, and supplied cash scenarios to Covirage. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring your files; account creation takes about a minute.
See Covirage for construction for the business context.
Covirage is not affiliated with Microsoft.
Start with assumptions that could change the project decision: selling prices, units sold, construction costs, completion dates, sales pace, and carrying costs. The useful range comes from project evidence and accountable owners, rather than a universal percentage applied to every development.
No. Profit margin divides profit by sales revenue, while return on cost divides profit by total development cost. State the denominator beside every percentage and use the same definition when comparing cases or reporting against a company target.
Construction and carrying costs can be paid before property sales produce cash. A delayed closing increases the amount or duration of funding needed even when the final project profit remains positive. Review the cash schedule as well as the total appraisal.