Openpyxl Financial Workbook Design - 01/08/2026 03:44 EDT

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Protection

wb=Workbook()
ws=wb.active; ws.title="Inputs"
dash=wb.create_sheet("Dashboard")
cf=wb.create_sheet("Cash Flow")

fill=PatternFill("solid", fgColor="FFF2CC")
hdr=PatternFill("solid", fgColor="D9EAD3")
for s in [ws,dash,cf]:
s["A1"]="Instructions"; s["A2"]="Enter values in yellow cells only. Gray/other cells contain formulas."
s["A1"].font=Font(bold=True)
items=[("Opening Balance",0),("Sales",0),("COGS",0),("Operating Expenses",0),("Financing Inflow",0),("Financing Outflow",0)]
ws["A4"]="Item"; ws["B4"]="Amount"
ws["A4"].fill=hdr; ws["B4"].fill=hdr
r=5
for it,val in items:
ws.cell(r,1).value=it
c=ws.cell(r,2); c.value=val; c.fill=fill; c.protection=Protection(locked=False); r+=1

dash["A4"]="Metric"; dash["B4"]="Value"
for c in ["A4","B4"]: dash[c].fill=hdr
metrics=[("Revenue","=Inputs!B6"),("Operating Expenses","=SUM(Inputs!B7:B8)"),("Net Cash Movement","=Inputs!B6-Inputs!B7-Inputs!B8+Inputs!B9-Inputs!B10+SUM('Cash Flow'!B12:B20)"),("Ending Balance","=Inputs!B5+B7")]
# correct refs
dash["A5"]="Revenue";dash["B5"]="=Inputs!B6"
dash["A6"]="Operating Expenses";dash["B6"]="=SUM(Inputs!B7:B8)"
dash["A7"]="Net Cash Movement";dash["B7"]="=Inputs!B6-Inputs!B7-Inputs!B8+Inputs!B9-Inputs!B10+SUM('Cash Flow'!B12:B20)"
dash["A8"]="Ending Balance";dash["B8"]="=Inputs!B5+B7"
cf["A4"]="Cash Flow Item";cf["B4"]="Amount"
cf["A4"].fill=hdr;cf["B4"].fill=hdr
rows=[("Opening Balance","=Inputs!B5"),("Cash from Sales","=INDEX(Inputs!B:B,MATCH(\"Sales\",Inputs!A:A,0))"),
("COGS","=-INDEX(Inputs!B:B,MATCH(\"COGS\",Inputs!A:A,0))"),
("Operating Expenses","=-SUMIFS(Inputs!B:B,Inputs!A:A,\"Operating Expenses\")"),
("Financing Inflow","=INDEX(Inputs!B:B,MATCH(\"Financing Inflow\",Inputs!A:A,0))"),
("Financing Outflow","=-INDEX(Inputs!B:B,MATCH(\"Financing Outflow\",Inputs!A:A,0))"),
("Manual Adjustment 1",0),("Manual Adjustment 2",0),("Ending Balance","=SUM(B5:B12)")]
rr=5
for name,val in rows:
cf.cell(rr,1).value=name
cell=cf.cell(rr,2)
cell.value=val
if isinstance(val,int):
cell.fill=fill; cell.protection=Protection(locked=False)
rr+=1
for sh in [ws,dash,cf]:
for row in sh.iter_rows():
for cell in row:
if cell.fill.fgColor.rgb!="00FFF2CC":
cell.protection=Protection(locked=True)
sh.protection.sheet=True
path="/mnt/data/Financial_Budget_Cashflow_Template.xlsx"
wb.save(path)
print(path)