Skip to content

use cases excel budget

Jump to bottom
zmworm edited this page Apr 5, 2026 · 5 revisions

Use Cases: Excel — Finance & Budget Reporting

Real-world scenarios for building multi-sheet financial reports with OfficeCLI.

Build a Multi-Department Q2 Budget vs. Actuals Workbook

Persona: Financial Analyst | Difficulty: Advanced | Tags: excel create finance

Build a two-sheet quarterly budget workbook with department data, cross-sheet summary formulas, a comparison chart, and frozen header rows — all without opening Excel.

Download: Q2_Budget.xlsx · Q2_Budget.py

Q2 Budget vs. Actuals — Departments sheet with chart

User:

I need to set up a quarterly budget workbook. Create a file called Q2_Budget.xlsx with two sheets: "Departments" and "Summary". On the Departments sheet, add a header row (Department, Q1 Actual, Q2 Budget, Q2 Actual, Variance) and then these rows of data: Sales, 120000, 135000, 128000, -7000 Marketing, 45000, 50000, 52000, 2000 Operations, 80000, 85000, 83000, -2000 HR, 30000, 32000, 31000, -1000 IT, 55000, 60000, 58000, -2000

Agent creates the workbook, renames Sheet1, adds the Summary sheet, writes the header row, and fills all five data rows with live Variance formulas in one batch:

Show commands (32 commands) 
officecli create Q2_Budget.xlsx

officecli batch Q2_Budget.xlsx --commands '[
  {"command":"set","path":"/Sheet1","props":{"name":"Departments"}},
  {"command":"add","parent":"/","type":"sheet","props":{"name":"Summary"}},

  {"command":"set","path":"/Departments/A1","props":{"value":"Department","bold":"true"}},
  {"command":"set","path":"/Departments/B1","props":{"value":"Q1 Actual","bold":"true"}},
  {"command":"set","path":"/Departments/C1","props":{"value":"Q2 Budget","bold":"true"}},
  {"command":"set","path":"/Departments/D1","props":{"value":"Q2 Actual","bold":"true"}},
  {"command":"set","path":"/Departments/E1","props":{"value":"Variance","bold":"true"}},

  {"command":"set","path":"/Departments/A2","props":{"value":"Sales"}},
  {"command":"set","path":"/Departments/B2","props":{"value":"120000"}},
  {"command":"set","path":"/Departments/C2","props":{"value":"135000"}},
  {"command":"set","path":"/Departments/D2","props":{"value":"128000"}},
  {"command":"set","path":"/Departments/E2","props":{"formula":"=D2-C2"}},

  {"command":"set","path":"/Departments/A3","props":{"value":"Marketing"}},
  {"command":"set","path":"/Departments/B3","props":{"value":"45000"}},
  {"command":"set","path":"/Departments/C3","props":{"value":"50000"}},
  {"command":"set","path":"/Departments/D3","props":{"value":"52000"}},
  {"command":"set","path":"/Departments/E3","props":{"formula":"=D3-C3"}},

  {"command":"set","path":"/Departments/A4","props":{"value":"Operations"}},
  {"command":"set","path":"/Departments/B4","props":{"value":"80000"}},
  {"command":"set","path":"/Departments/C4","props":{"value":"85000"}},
  {"command":"set","path":"/Departments/D4","props":{"value":"83000"}},
  {"command":"set","path":"/Departments/E4","props":{"formula":"=D4-C4"}},

  {"command":"set","path":"/Departments/A5","props":{"value":"HR"}},
  {"command":"set","path":"/Departments/B5","props":{"value":"30000"}},
  {"command":"set","path":"/Departments/C5","props":{"value":"32000"}},
  {"command":"set","path":"/Departments/D5","props":{"value":"31000"}},
  {"command":"set","path":"/Departments/E5","props":{"formula":"=D5-C5"}},

  {"command":"set","path":"/Departments/A6","props":{"value":"IT"}},
  {"command":"set","path":"/Departments/B6","props":{"value":"55000"}},
  {"command":"set","path":"/Departments/C6","props":{"value":"60000"}},
  {"command":"set","path":"/Departments/D6","props":{"value":"58000"}},
  {"command":"set","path":"/Departments/E6","props":{"formula":"=D6-C6"}}
]'

User:

On the Summary sheet, add a title "Q2 Budget Summary" in A1, then pull in the totals from the Departments sheet — I want rows for Total Q1 Actual, Total Q2 Budget, Total Q2 Actual, and Total Variance, each with the summed value from the Departments sheet. Also insert a new row in the Departments sheet between Marketing and Operations for a department called "Product" with values 60000, 65000, 63000, -2000, and update the Variance for Sales to +3000.

Agent inserts the Product row, corrects the Sales Q2 Actual, and populates the Summary sheet with cross-sheet SUM formulas in one batch:

Show commands (18 commands) 
officecli batch Q2_Budget.xlsx --commands '[
  {"command":"add","parent":"/Departments","type":"row","index":4},

  {"command":"set","path":"/Departments/A4","props":{"value":"Product"}},
  {"command":"set","path":"/Departments/B4","props":{"value":"60000"}},
  {"command":"set","path":"/Departments/C4","props":{"value":"65000"}},
  {"command":"set","path":"/Departments/D4","props":{"value":"63000"}},
  {"command":"set","path":"/Departments/E4","props":{"formula":"=D4-C4"}},

  {"command":"set","path":"/Departments/D2","props":{"value":"138000"}},
  {"command":"set","path":"/Departments/E2","props":{"formula":"=D2-C2"}},

  {"command":"set","path":"/Summary/A1","props":{"value":"Q2 Budget Summary"}},

  {"command":"set","path":"/Summary/A3","props":{"value":"Total Q1 Actual"}},
  {"command":"set","path":"/Summary/B3","props":{"formula":"=SUM(Departments!B2:B7)"}},

  {"command":"set","path":"/Summary/A4","props":{"value":"Total Q2 Budget"}},
  {"command":"set","path":"/Summary/B4","props":{"formula":"=SUM(Departments!C2:C7)"}},

  {"command":"set","path":"/Summary/A5","props":{"value":"Total Q2 Actual"}},
  {"command":"set","path":"/Summary/B5","props":{"formula":"=SUM(Departments!D2:D7)"}},

  {"command":"set","path":"/Summary/A6","props":{"value":"Total Variance"}},
  {"command":"set","path":"/Summary/B6","props":{"formula":"=SUM(Departments!E2:E7)"}}
]'

Inserting a row at index 4 shifts Operations, HR, and IT down by one — so the Summary SUM ranges B2:B7 now correctly span all six departments including the new Product row.

User:

On the Departments sheet, add a bar chart that shows each department on the x-axis and plots both Q2 Budget and Q2 Actual side by side so I can see how we tracked against budget. Also freeze the header row on both sheets so it stays visible when I scroll.

Agent adds the clustered bar chart and freezes row 1 on both sheets:

officecli batch Q2_Budget.xlsx --commands '[
  {
    "command":"add",
    "parent":"/Departments",
    "type":"chart",
    "props":{
      "chartType":"bar",
      "title":"Q2 Budget vs Actual by Department",
      "series1.name":"Q2 Budget",
      "series1.values":"Departments!C2:C7",
      "series1.categories":"Departments!A2:A7",
      "series2.name":"Q2 Actual",
      "series2.values":"Departments!D2:D7",
      "series2.categories":"Departments!A2:A7",
      "catTitle":"Department",
      "axisTitle":"Amount ($)",
      "x":6,"y":1,"width":10,"height":14,
      "legend":true
    }
  },
  {"command":"set","path":"/Departments","props":{"freeze":"A2"}},
  {"command":"set","path":"/Summary","props":{"freeze":"A2"}}
]'

Setting "freeze":"A2" on a sheet locks row 1 in place; everything above A2 stays fixed when scrolling.

See also: Add a Sheet · Set Cell Values · Batch Commands · Create a Workbook · Add a Chart · Cross-Sheet Formulas

Based on OfficeCLI v1.0.34

Clone this wiki locally