populate-financials.py
1 #!/usr/bin/env python3 2 """ 3 Populate the SBT Financial Spreadsheet template with Audit&Fix data. 4 5 Copies the template xlsx and fills in the data cells (preserving all formulas). 6 Data sourced from auditandfix-business-plan.md financial sections. 7 8 Usage: 9 python3 populate-financials.py 10 """ 11 12 import shutil 13 from copy import copy 14 from openpyxl import load_workbook 15 16 TEMPLATE = 'examples/2. Financial Spreadsheets 2409v1.xlsx' 17 OUTPUT = 'AuditFix Financial Spreadsheets 2026.xlsx' 18 19 # ─── Business Plan Financial Data ─────────────────────────────────────── 20 21 BUSINESS_NAME = 'Audit&Fix' 22 YEAR1 = '2026' 23 YEAR2 = '2027' 24 25 MONTHS = ['January', 'February', 'March', 'April', 'May', 'June', 26 'July', 'August', 'September', 'October', 'November', 'December'] 27 28 # Personal Budget - Monthly amounts (AUD) 29 # SEA: $1,074/fortnight = $2,328/month for 60 weeks (Feb 2026 - Apr 2027) 30 # Year 1 (Jan-Dec 2026): SEA starts Feb, so Jan=0, Feb-Dec=$2,328 31 SEA_MONTHLY = [0, 2328, 2328, 2328, 2328, 2328, 2328, 2328, 2328, 2328, 2328, 2328] 32 33 # Personal COL: $1,500/wk = $6,500/mo (actual expenses, ex-SEA) 34 # SEA covers ~$500/wk ($2,328/mo) separately as income 35 36 # Vehicle expenses (monthly) 37 VEHICLE = { 38 'petrol': 300, # $69/wk 39 'other': 400, # Car payment $92/wk 40 } 41 # Vehicle total: $700/mo 42 43 # Living expenses (monthly) 44 LIVING = { 45 'rent': 1800, # $415/wk 46 'utilities': 220, # $51/wk 47 'phone_internet': 80, # $18/wk 48 'mobile': 80, # $18/wk 49 'food': 1100, # Groceries + dining $254/wk 50 'medical': 100, # Personal care $23/wk 51 'clothes': 150, # $35/wk 52 'household': 170, # Entertainment/streaming $39/wk 53 'other': 650, # Gym, contingency, misc $150/wk 54 } 55 # Living total: $4,350/mo 56 57 # Insurance (monthly) 58 INSURANCE = { 59 'life': 50, # $12/wk 60 'health': 180, # $42/wk 61 'car': 120, # $28/wk 62 } 63 # Insurance total: $350/mo 64 65 # Loans (monthly) 66 LOANS = { 67 'personal': 500, # Debt repayment $115/wk 68 } 69 # Loans total: $500/mo 70 71 # Other personal expenses (monthly) 72 OTHER_PERSONAL = { 73 'emergency': 300, # Emergency fund $69/wk 74 'savings': 300, # General savings $69/wk 75 } 76 # Other total: $600/mo 77 78 # Total monthly COL: 700 + 4350 + 350 + 500 + 600 = $6,500 ($1,500/wk) 79 80 # ─── Startup Costs ──────────────────────────────────────────────────── 81 82 STARTUP_COSTS = { 83 'business_name': ('A5', 50), # Business name registration 84 'domain': ('A8', 30), # Domain registration 85 'accountant': ('A12', 0), # Accountant fees (none yet) 86 'website_hosting': ('A29', 240), # mapped to "Website hosting" row 87 'api_credits': ('A33', 200), # Initial API testing credits → Marketing row 88 'logo': ('A15', 200), # Logo → mapped to Utility connections row (reuse) 89 'contingency': ('A34', 144), # Contingency → "More…" row 90 } 91 92 # ─── Sales Data ──────────────────────────────────────────────────────── 93 94 # Year 1: Website Audit Reports 95 Y1_UNITS = [0, 1, 4, 5, 5, 5, 8, 10, 12, 12, 14, 14] # customers/month 96 Y1_PRICE = 297 # AUD per report 97 Y1_COGS_PER_UNIT = 2 # API cost per customer 98 99 # Year 2: Website Audit Reports (quarterly → spread to monthly) 100 # Q1=40/mo, Q2=50/mo, Q3=55/mo, Q4=55/mo 101 Y2_UNITS = [40, 40, 40, 50, 50, 50, 55, 55, 55, 55, 55, 55] 102 Y2_PRICE = 297 103 Y2_COGS_PER_UNIT = 2 104 105 # ─── Operating Expenses (P&L) ───────────────────────────────────────── 106 107 # Year 1 monthly operating expenses (constant $616/mo) 108 Y1_OPEX = { 109 'advertising': 67, # Outreach API costs → row 15 110 'utilities': 0, # Not applicable for digital biz 111 'telephone': 67, # Twilio SMS → row 20 112 'internet': 20, # OpenRouter API → row 21 113 'website_hosting': 20, # Hosting & Domain → row 29 114 'email_service': 310, # Claude Max 20x (US$200 ≈ AU$310) → row 30 115 'other1': 92, # ZenRows → row 31 116 'other2': 0, # row 32 117 'licensing': 30, # Xero Accounting → row 27 118 'membership': 10, # Google Workspace → row 26 119 } 120 121 # Year 2 monthly (varies by quarter) 122 # Q1: $783/mo, Q2-Q4: $2,206/mo (VA + marketing added) 123 Y2_OPEX_Q1 = { 124 'advertising': 67 + 167, # Outreach $67 + Paid ads $500/3≈$167 125 'telephone': 67, 126 'internet': 20, 127 'website_hosting': 20, 128 'email_service': 310, # Claude Max 20x (US$200 ≈ AU$310) 129 'other1': 92, 130 'licensing': 30, 131 'membership': 10, 132 'wages': 0, 133 } 134 Y2_OPEX_Q234 = { 135 'advertising': 67 + 500 + 500, # Outreach + paid ads + content 136 'telephone': 83, # Twilio scaled 137 'internet': 25, # OpenRouter scaled 138 'website_hosting': 20, 139 'email_service': 310, # Claude Max 20x (US$200 ≈ AU$310) 140 'other1': 92, # ZenRows 141 'licensing': 30, 142 'membership': 10, 143 'wages': 600, # VA 10hrs/wk @ $15/hr 144 } 145 146 # ─── Cash Flow extras ───────────────────────────────────────────────── 147 148 # Opening balance Year 1: startup capital from father's investment 149 OPENING_BALANCE_Y1 = 75000 # Father's $75k investment 150 151 152 def fill_monthly(ws, row, values, col_start=2): 153 """Fill cells B{row} through M{row} with monthly values.""" 154 for i, val in enumerate(values): 155 ws.cell(row=row, column=col_start + i, value=val) 156 157 158 def fill_constant(ws, row, value, months=12, col_start=2): 159 """Fill a row with the same value for all months.""" 160 fill_monthly(ws, row, [value] * months, col_start) 161 162 163 def populate_personal_budget(wb): 164 ws = wb['Personal Budget'] 165 166 # Update header 167 ws['N1'] = f'(Year 1 - {YEAR1})' 168 ws['A4'] = f'For the year {YEAR1} - {YEAR2}' 169 170 # Update SEA rates 171 ws['Q2'] = 1074 # SEA fortnightly rate (2026) 172 ws['Q3'] = 0 # No rental assistance 173 174 # Month names 175 for i, month in enumerate(MONTHS): 176 ws.cell(row=5, column=2 + i, value=month) 177 178 # INCOME 179 # Row 7: SEA 180 fill_monthly(ws, 7, SEA_MONTHLY) 181 # Row 8: Wages/Salary = 0 182 fill_constant(ws, 8, 0) 183 # Row 9: Other income = 0 184 fill_constant(ws, 9, 0) 185 186 # VEHICLE EXPENSES (rows 15-21) 187 fill_constant(ws, 15, 0) # Registration 188 fill_constant(ws, 16, 0) # Maintenance 189 fill_constant(ws, 17, 0) # Repairs 190 fill_constant(ws, 18, VEHICLE['petrol']) # Petrol 191 fill_constant(ws, 19, 0) # Public Transport 192 fill_constant(ws, 20, 0) # Parking 193 fill_constant(ws, 21, VEHICLE['other']) # Other (car payment) 194 195 # LIVING EXPENSES (rows 25-40) 196 fill_constant(ws, 25, 0) # Rates 197 fill_constant(ws, 26, 0) # Mortgage 198 fill_constant(ws, 27, LIVING['rent']) # Rent 199 fill_constant(ws, 28, LIVING['utilities']) # Utilities 200 fill_constant(ws, 29, LIVING['phone_internet']) # Phone/internet 201 fill_constant(ws, 30, LIVING['mobile']) # Mobile 202 fill_constant(ws, 31, 0) # Home maintenance 203 fill_constant(ws, 32, LIVING['food']) # Food and groceries 204 fill_constant(ws, 33, LIVING['medical']) # Medical/health 205 fill_constant(ws, 34, LIVING['clothes']) # Clothes/shoes 206 fill_constant(ws, 35, 0) # Education 207 fill_constant(ws, 36, LIVING['household']) # Household purchases 208 fill_constant(ws, 37, 0) # Pets 209 fill_constant(ws, 38, 0) # Childcare 210 fill_constant(ws, 39, 0) # School fees 211 fill_constant(ws, 40, LIVING['other']) # Other expenses 212 213 # INSURANCE (rows 44-49) 214 fill_constant(ws, 44, INSURANCE['life']) 215 fill_constant(ws, 45, INSURANCE['health']) 216 fill_constant(ws, 46, 0) # Superannuation 217 fill_constant(ws, 47, 0) # Home and Contents 218 fill_constant(ws, 48, INSURANCE['car']) 219 fill_constant(ws, 49, 0) # Other insurance 220 221 # LOANS (rows 53-58) 222 fill_constant(ws, 53, LOANS['personal']) # Personal loans 223 fill_constant(ws, 54, 0) 224 fill_constant(ws, 55, 0) 225 fill_constant(ws, 56, 0) 226 fill_constant(ws, 57, 0) 227 fill_constant(ws, 58, 0) 228 229 # OTHER (rows 62-67) 230 fill_constant(ws, 62, OTHER_PERSONAL['emergency']) 231 fill_constant(ws, 63, OTHER_PERSONAL['savings']) 232 fill_constant(ws, 64, 0) 233 fill_constant(ws, 65, 0) 234 fill_constant(ws, 66, 0) 235 fill_constant(ws, 67, 0) 236 237 print(" Personal Budget populated") 238 239 240 def populate_startup(wb): 241 ws = wb['Start-up costing'] 242 243 ws['A2'] = f'Start-up costing for {BUSINESS_NAME} in {YEAR1}' 244 245 # Startup costs column B 246 ws['B4'] = 0 # Registrations header 247 ws['B5'] = 50 # Business name 248 ws['B6'] = 0 # Licences 249 ws['B7'] = 0 # Permits 250 ws['B8'] = 30 # Domain names 251 ws['B9'] = 0 # Trademarks 252 ws['B10'] = 0 # Vehicle registration 253 ws['B11'] = 0 # Membership fees 254 ws['B12'] = 0 # Accountant fees 255 ws['B13'] = 0 # Solicitor fees 256 ws['B14'] = 0 # Rental 257 ws['B15'] = 0 # Utility connections 258 ws['B16'] = 0 # Phone connection 259 ws['B17'] = 0 # Internet connection 260 ws['B18'] = 0 # Computer software 261 ws['B19'] = 0 # Training 262 # B20 is formula (linked from Personal Budget drawings) 263 ws['B21'] = 0 # Stock 264 ws['B22'] = 0 # Insurance header 265 ws['B23'] = 0 # Building and contents 266 ws['B24'] = 0 # Vehicle 267 ws['B25'] = 0 # Public liability 268 ws['B26'] = 0 # Professional indemnity 269 ws['B27'] = 0 # Product liability 270 ws['B28'] = 0 # Workers compensation 271 ws['B29'] = 0 # Business assets 272 ws['B30'] = 0 # Business revenue 273 ws['B31'] = 0 # Printing 274 ws['B32'] = 0 # Stationery 275 ws['B33'] = 200 # Marketing (logo + initial API credits) 276 ws['B34'] = 240 # More (website hosting 1yr) 277 ws['B35'] = 200 # Utilities (initial API testing credits) 278 ws['B36'] = 144 # Buffer row if available 279 280 # Equipment column E - all already owned 281 for r in range(4, 37): 282 if ws.cell(row=r, column=5).value is None or not isinstance(ws.cell(row=r, column=5).value, str) or not ws.cell(row=r, column=5).value.startswith('='): 283 ws.cell(row=r, column=5, value=0) 284 285 print(" Start-up Costing populated") 286 287 288 def populate_sales_targets(wb): 289 ws = wb['Sales Targets'] 290 291 ws['A1'] = f'Sales Targets for {BUSINESS_NAME} as at {YEAR1}' 292 293 # Product 1: Website Audit Report (Year 1) 294 ws['A4'] = 'Website Audit Report' 295 # Row 5: Units sold per month 296 fill_monthly(ws, 5, Y1_UNITS) 297 # Row 6: Unit price per month 298 fill_constant(ws, 6, Y1_PRICE) 299 # Row 7: Total sales = formula (units × price) 300 # Row 9: COGS per unit 301 fill_constant(ws, 9, Y1_COGS_PER_UNIT) 302 # Row 10: Total COGS = formula (units × COGS) 303 304 # Clear products 2-5 for Year 1 305 for prod_start in [13, 21, 29, 37]: 306 fill_constant(ws, prod_start, 0) # units 307 fill_constant(ws, prod_start + 1, 0) # price 308 fill_constant(ws, prod_start + 4, 0) # COGS per unit 309 310 # Year 2 section (starts around row 55-58) 311 # Product 1 Year 2 312 ws['A57'] = 'Website Audit Report' 313 fill_monthly(ws, 58, Y2_UNITS) 314 fill_constant(ws, 59, Y2_PRICE) 315 fill_constant(ws, 62, Y2_COGS_PER_UNIT) 316 317 # Clear products 2-5 for Year 2 318 for prod_start in [66, 74, 82, 90]: 319 fill_constant(ws, prod_start, 0) 320 fill_constant(ws, prod_start + 1, 0) 321 fill_constant(ws, prod_start + 4, 0) 322 323 print(" Sales Targets populated") 324 325 326 def populate_pnl(wb): 327 ws = wb['Profit and Loss forecast'] 328 329 ws['A1'] = f'Profit and loss for {BUSINESS_NAME} as at {YEAR1}' 330 ws['O1'] = f'{YEAR1}' 331 332 # Year 1 operating expenses (rows 13-39, monthly values B:M) 333 # Row 13: Accountant fees 334 fill_constant(ws, 13, 0) 335 # Row 14: Solicitor fees 336 fill_constant(ws, 14, 0) 337 # Row 15: Advertising and marketing (outreach API costs) 338 fill_constant(ws, 15, Y1_OPEX['advertising']) 339 # Row 16: Bank fees 340 fill_constant(ws, 16, 0) 341 # Row 17: Interest paid 342 fill_constant(ws, 17, 0) 343 # Row 18: Credit card fees 344 fill_constant(ws, 18, 0) 345 # Row 19: Utilities 346 fill_constant(ws, 19, 0) 347 # Row 20: Telephone/internet (Twilio SMS) 348 fill_constant(ws, 20, Y1_OPEX['telephone']) 349 # Row 21: Internet (OpenRouter API) 350 fill_constant(ws, 21, Y1_OPEX['internet']) 351 # Row 22: Rent & rates 352 fill_constant(ws, 22, 0) 353 # Row 23: Motor vehicle expenses 354 fill_constant(ws, 23, 0) 355 # Row 24: Repairs and maintenance 356 fill_constant(ws, 24, 0) 357 # Row 25: Stationery and printing 358 fill_constant(ws, 25, 0) 359 # Row 26: Membership fees (Google Workspace) 360 fill_constant(ws, 26, Y1_OPEX['membership']) 361 # Row 27: Licensing (Xero Accounting) 362 fill_constant(ws, 27, Y1_OPEX['licensing']) 363 # Row 28: Insurance 364 fill_constant(ws, 28, 0) 365 # Row 29: Website hosting (Hosting & Domain) 366 fill_constant(ws, 29, Y1_OPEX['website_hosting']) 367 # Row 30: Email service (Claude API) 368 fill_constant(ws, 30, Y1_OPEX['email_service']) 369 # Row 31: Other (ZenRows SERP scraping) 370 ws['A31'] = 'ZenRows SERP API' 371 fill_constant(ws, 31, Y1_OPEX['other1']) 372 # Rows 32-39: zeros 373 for r in range(32, 40): 374 fill_constant(ws, r, 0) 375 376 # Year 2 P&L (starts row 55) 377 ws.cell(row=55, column=2, value=f'Profit and loss for {BUSINESS_NAME} as at {YEAR2}') 378 ws['O55'] = f'{YEAR2}' 379 380 # Year 2 operating expenses (rows 67-93) 381 for month_idx in range(12): 382 col = 2 + month_idx # B=2 through M=13 383 opex = Y2_OPEX_Q1 if month_idx < 3 else Y2_OPEX_Q234 384 385 ws.cell(row=67, column=col, value=0) # Accountant 386 ws.cell(row=68, column=col, value=0) # Solicitor 387 ws.cell(row=69, column=col, value=opex['advertising']) # Advertising 388 ws.cell(row=70, column=col, value=0) # Bank fees 389 ws.cell(row=71, column=col, value=0) # Interest 390 ws.cell(row=72, column=col, value=0) # Credit card 391 ws.cell(row=73, column=col, value=0) # Utilities 392 ws.cell(row=74, column=col, value=opex['telephone']) # Telephone (Twilio) 393 ws.cell(row=75, column=col, value=opex['internet']) # Internet (OpenRouter) 394 ws.cell(row=76, column=col, value=0) # Rent 395 ws.cell(row=77, column=col, value=0) # Motor vehicle 396 ws.cell(row=78, column=col, value=0) # Repairs 397 ws.cell(row=79, column=col, value=0) # Stationery 398 ws.cell(row=80, column=col, value=opex['membership']) # Membership (Google) 399 ws.cell(row=81, column=col, value=opex['licensing']) # Licensing (Xero) 400 ws.cell(row=82, column=col, value=0) # Insurance 401 ws.cell(row=83, column=col, value=opex['website_hosting']) # Website hosting 402 ws.cell(row=84, column=col, value=opex['email_service']) # Email/Claude API 403 ws.cell(row=85, column=col, value=opex['other1']) # ZenRows 404 for r in range(86, 92): 405 ws.cell(row=r, column=col, value=0) 406 ws.cell(row=91, column=col, value=0) # Superannuation 407 ws.cell(row=92, column=col, value=0) # Income tax 408 ws.cell(row=93, column=col, value=opex.get('wages', 0)) # Wages (VA) 409 410 # Year 2 Drawings (row 102) - "please enter amounts manually" 411 # Jan-Apr 2027: SEA still active, drawings = COL minus SEA = $6,500 - $2,328 = $4,172 412 # May-Dec 2027: SEA ended, drawings = full COL = $6,500 413 y2_drawings = [4172] * 4 + [6500] * 8 414 fill_monthly(ws, 102, y2_drawings) 415 416 print(" Profit and Loss populated") 417 418 419 def populate_cashflow(wb): 420 ws = wb['Cash flow forecast'] 421 422 ws.cell(row=2, column=2, value=f'Cash flow for {BUSINESS_NAME} in {YEAR1}') 423 424 # Opening balance - B5 is manual, rest are formulas 425 ws['B5'] = OPENING_BALANCE_Y1 426 427 # Cash incoming rows 8-11 (non-sales, non-formula) 428 fill_constant(ws, 8, 0) # Asset sales 429 fill_constant(ws, 9, 0) # Debtor receipts 430 # Loans: father's investment in month 1 only 431 loans = [0] * 12 432 ws.cell(row=10, column=2, value=0) # Already in opening balance 433 for i in range(12): 434 ws.cell(row=10, column=2 + i, value=loans[i]) 435 fill_constant(ws, 11, 0) # Credit card 436 437 # Cash outgoing: copy operating expenses from P&L (rows 16-43) 438 # Row 15 = Purchases/COGS (linked from Sales Targets) 439 fill_constant(ws, 16, 0) # Other stock 440 fill_constant(ws, 17, 0) # Accountant 441 fill_constant(ws, 18, 0) # Solicitor 442 fill_constant(ws, 19, Y1_OPEX['advertising']) # Advertising 443 fill_constant(ws, 20, 0) # Bank fees 444 fill_constant(ws, 21, 0) # Interest 445 fill_constant(ws, 22, 0) # Credit card 446 fill_constant(ws, 23, 0) # Utilities 447 fill_constant(ws, 24, Y1_OPEX['telephone']) # Telephone (Twilio) 448 fill_constant(ws, 25, Y1_OPEX['internet']) # Internet (OpenRouter) 449 fill_constant(ws, 26, 0) # Rent 450 fill_constant(ws, 27, 0) # Motor vehicle 451 fill_constant(ws, 28, 0) # Repairs 452 fill_constant(ws, 29, 0) # Stationery 453 fill_constant(ws, 30, Y1_OPEX['membership']) # Membership (Google) 454 fill_constant(ws, 31, Y1_OPEX['licensing']) # Licensing (Xero) 455 fill_constant(ws, 32, 0) # Insurance 456 fill_constant(ws, 33, Y1_OPEX['website_hosting']) # Website hosting 457 fill_constant(ws, 34, Y1_OPEX['email_service']) # Email (Claude) 458 ws['A35'] = 'ZenRows SERP API' 459 fill_constant(ws, 35, Y1_OPEX['other1']) # ZenRows 460 for r in range(36, 41): 461 fill_constant(ws, r, 0) 462 fill_constant(ws, 41, 0) # Superannuation 463 fill_constant(ws, 42, 0) # Income tax 464 fill_constant(ws, 43, 0) # Wages 465 # Row 44 = Drawings (linked from Personal Budget) 466 467 # Year 2 Cash Flow (starts row 57) 468 ws.cell(row=57, column=2, value=f'Cash flow for {BUSINESS_NAME} in {YEAR2}') 469 470 # Year 2 opening balance: B60 should be linked to Year 1 closing 471 # but in the template it's manual for first month 472 # Leave as formula if it exists, otherwise the Year 1 closing flows through 473 474 # Year 2 cash outgoing (rows 70-97 roughly - need to check) 475 # For Year 2, expenses vary by quarter 476 for month_idx in range(12): 477 col = 2 + month_idx 478 opex = Y2_OPEX_Q1 if month_idx < 3 else Y2_OPEX_Q234 479 480 ws.cell(row=70, column=col, value=0) # Other stock 481 ws.cell(row=71, column=col, value=0) # Accountant 482 ws.cell(row=72, column=col, value=0) # Solicitor 483 ws.cell(row=73, column=col, value=opex['advertising']) # Advertising 484 ws.cell(row=74, column=col, value=0) # Bank fees 485 ws.cell(row=75, column=col, value=0) # Interest 486 ws.cell(row=76, column=col, value=0) # Credit card 487 ws.cell(row=77, column=col, value=0) # Utilities 488 ws.cell(row=78, column=col, value=opex['telephone']) # Telephone 489 ws.cell(row=79, column=col, value=opex['internet']) # Internet 490 ws.cell(row=80, column=col, value=0) # Rent 491 ws.cell(row=81, column=col, value=0) # Motor vehicle 492 ws.cell(row=82, column=col, value=0) # Repairs 493 ws.cell(row=83, column=col, value=0) # Stationery 494 ws.cell(row=84, column=col, value=opex['membership']) # Membership 495 ws.cell(row=85, column=col, value=opex['licensing']) # Licensing 496 ws.cell(row=86, column=col, value=0) # Insurance 497 ws.cell(row=87, column=col, value=opex['website_hosting']) # Website hosting 498 ws.cell(row=88, column=col, value=opex['email_service']) # Email/Claude 499 ws.cell(row=89, column=col, value=opex['other1']) # ZenRows 500 for r in range(90, 96): 501 ws.cell(row=r, column=col, value=0) 502 ws.cell(row=96, column=col, value=0) # Superannuation 503 ws.cell(row=97, column=col, value=0) # Income tax 504 ws.cell(row=98, column=col, value=opex.get('wages', 0)) # Wages 505 506 print(" Cash Flow populated") 507 508 509 def populate_breakeven(wb): 510 ws = wb['Break-Even Analysis'] 511 512 # Table 1 (rows 11-33) - Website Audit Report 513 ws['B12'] = 8 # Avg units sold per month (Year 1 avg: 90/12 ≈ 7.5) 514 ws['B14'] = 297 # Selling price 515 516 # COGS worktable — API cost breakdown per customer 517 # Each report analyses ~10 sites. Costs per customer: 518 ws['D12'] = 'Scoring API (GPT-4o-mini, 2 passes × 10 sites)' 519 ws['E12'] = 0.30 520 ws['D13'] = 'Rescoring API (GPT-4o-mini, 60% of sites)' 521 ws['E13'] = 0.09 522 ws['D14'] = 'Proposal generation (Claude Sonnet)' 523 ws['E14'] = 0.18 524 ws['D15'] = 'Enrichment API (Claude Haiku, 10 sites)' 525 ws['E15'] = 0.20 526 ws['D16'] = 'SERP scraping (ZenRows, 10 queries)' 527 ws['E16'] = 0.92 528 ws['D17'] = 'Payment processing (PayPal/Stripe ~1%)' 529 ws['E17'] = 0.31 530 ws['D18'] = 'Total COGS per customer' 531 ws['E18'] = 2 # Rounded from $2.00 532 533 # Table 2 (rows 47-59) - Year 2 projection 534 ws['B48'] = 50 # Avg units per month Year 2 535 ws['B50'] = 297 # Price 536 537 # Year 2 COGS breakdown (same structure, slightly optimised) 538 ws['D48'] = 'Scoring API (GPT-4o-mini)' 539 ws['E48'] = 0.30 540 ws['D49'] = 'Rescoring API (GPT-4o-mini)' 541 ws['E49'] = 0.09 542 ws['D50'] = 'Proposal generation (Claude)' 543 ws['E50'] = 0.18 544 ws['D51'] = 'Enrichment API (Claude Haiku)' 545 ws['E51'] = 0.20 546 ws['D52'] = 'SERP scraping (ZenRows)' 547 ws['E52'] = 0.92 548 ws['D53'] = 'Payment processing (~1%)' 549 ws['E53'] = 0.31 550 ws['D54'] = 'Total COGS per customer' 551 ws['E54'] = 2 552 553 print(" Break-Even Analysis populated") 554 555 556 def populate_balance_sheet(wb): 557 ws = wb['Balance Sheet Forecast'] 558 559 # The balance sheet has Year 1 and Year 2 columns 560 # Key items to fill: 561 # Current Assets: Bank/Cash (linked from cash flow closing balance) 562 # Fixed Assets: Equipment 563 # Liabilities: None (no loans) 564 # Equity: Owner's equity + retained earnings 565 566 # Most cells may be formula-linked, so only fill manual cells 567 # Look for non-formula cells to fill 568 569 # Equipment value: $2,200 (laptop, monitor, desk, chair - already owned) 570 # This goes in Fixed Assets 571 572 print(" Balance Sheet - skipped (mostly formula-linked)") 573 574 575 def populate_pricing_example(wb): 576 ws = wb['Pricing Example'] 577 578 # This is optional but useful to fill 579 # Need to check the structure first 580 print(" Pricing Example - skipped (optional sheet)") 581 582 583 def main(): 584 print(f"Copying template: {TEMPLATE}") 585 shutil.copy2(TEMPLATE, OUTPUT) 586 587 print(f"Loading workbook: {OUTPUT}") 588 wb = load_workbook(OUTPUT) 589 590 print("Populating sheets...") 591 populate_personal_budget(wb) 592 populate_startup(wb) 593 populate_sales_targets(wb) 594 populate_pnl(wb) 595 populate_cashflow(wb) 596 populate_breakeven(wb) 597 populate_balance_sheet(wb) 598 populate_pricing_example(wb) 599 600 print(f"\nSaving: {OUTPUT}") 601 wb.save(OUTPUT) 602 print(f"Done! Open in Excel or LibreOffice to verify formulas calculate correctly.") 603 604 605 if __name__ == '__main__': 606 main()