-
Notifications
You must be signed in to change notification settings - Fork 528
use cases excel budget
Persona: Financial Analyst | Difficulty: Advanced | Tags:
excelcreatefinance
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
Build a Multi-Department Q2 Budget vs. Actuals Workbook
See also: Add a Sheet · Set Cell Values · Batch Commands · Create a Workbook · Add a Chart · Cross-Sheet Formulas
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 (33 commands)
officecli create Q2_Budget.xlsx officecli set Q2_Budget.xlsx '/Sheet1' --prop name=Departments officecli add Q2_Budget.xlsx '/' --type sheet --prop name=Summary officecli set Q2_Budget.xlsx '/Departments/A1' --prop value=Department --prop bold=true officecli set Q2_Budget.xlsx '/Departments/B1' --prop value="Q1 Actual" --prop bold=true officecli set Q2_Budget.xlsx '/Departments/C1' --prop value="Q2 Budget" --prop bold=true officecli set Q2_Budget.xlsx '/Departments/D1' --prop value="Q2 Actual" --prop bold=true officecli set Q2_Budget.xlsx '/Departments/E1' --prop value=Variance --prop bold=true officecli set Q2_Budget.xlsx '/Departments/A2' --prop value=Sales officecli set Q2_Budget.xlsx '/Departments/B2' --prop value=120000 officecli set Q2_Budget.xlsx '/Departments/C2' --prop value=135000 officecli set Q2_Budget.xlsx '/Departments/D2' --prop value=128000 officecli set Q2_Budget.xlsx '/Departments/E2' --prop formula="=D2-C2" officecli set Q2_Budget.xlsx '/Departments/A3' --prop value=Marketing officecli set Q2_Budget.xlsx '/Departments/B3' --prop value=45000 officecli set Q2_Budget.xlsx '/Departments/C3' --prop value=50000 officecli set Q2_Budget.xlsx '/Departments/D3' --prop value=52000 officecli set Q2_Budget.xlsx '/Departments/E3' --prop formula="=D3-C3" officecli set Q2_Budget.xlsx '/Departments/A4' --prop value=Operations officecli set Q2_Budget.xlsx '/Departments/B4' --prop value=80000 officecli set Q2_Budget.xlsx '/Departments/C4' --prop value=85000 officecli set Q2_Budget.xlsx '/Departments/D4' --prop value=83000 officecli set Q2_Budget.xlsx '/Departments/E4' --prop formula="=D4-C4" officecli set Q2_Budget.xlsx '/Departments/A5' --prop value=HR officecli set Q2_Budget.xlsx '/Departments/B5' --prop value=30000 officecli set Q2_Budget.xlsx '/Departments/C5' --prop value=32000 officecli set Q2_Budget.xlsx '/Departments/D5' --prop value=31000 officecli set Q2_Budget.xlsx '/Departments/E5' --prop formula="=D5-C5" officecli set Q2_Budget.xlsx '/Departments/A6' --prop value=IT officecli set Q2_Budget.xlsx '/Departments/B6' --prop value=55000 officecli set Q2_Budget.xlsx '/Departments/C6' --prop value=60000 officecli set Q2_Budget.xlsx '/Departments/D6' --prop value=58000 officecli set Q2_Budget.xlsx '/Departments/E6' --prop 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 (17 commands)
officecli add Q2_Budget.xlsx '/Departments' --type row --index 4 officecli set Q2_Budget.xlsx '/Departments/A4' --prop value=Product officecli set Q2_Budget.xlsx '/Departments/B4' --prop value=60000 officecli set Q2_Budget.xlsx '/Departments/C4' --prop value=65000 officecli set Q2_Budget.xlsx '/Departments/D4' --prop value=63000 officecli set Q2_Budget.xlsx '/Departments/E4' --prop formula="=D4-C4" officecli set Q2_Budget.xlsx '/Departments/D2' --prop value=138000 officecli set Q2_Budget.xlsx '/Departments/E2' --prop formula="=D2-C2" officecli set Q2_Budget.xlsx '/Summary/A1' --prop value="Q2 Budget Summary" officecli set Q2_Budget.xlsx '/Summary/A3' --prop value="Total Q1 Actual" officecli set Q2_Budget.xlsx '/Summary/B3' --prop formula="=SUM(Departments!B2:B7)" officecli set Q2_Budget.xlsx '/Summary/A4' --prop value="Total Q2 Budget" officecli set Q2_Budget.xlsx '/Summary/B4' --prop formula="=SUM(Departments!C2:C7)" officecli set Q2_Budget.xlsx '/Summary/A5' --prop value="Total Q2 Actual" officecli set Q2_Budget.xlsx '/Summary/B5' --prop formula="=SUM(Departments!D2:D7)" officecli set Q2_Budget.xlsx '/Summary/A6' --prop value="Total Variance" officecli set Q2_Budget.xlsx '/Summary/B6' --prop formula="=SUM(Departments!E2:E7)"
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 add Q2_Budget.xlsx '/Departments' --type chart \ --prop chartType=bar \ --prop title="Q2 Budget vs Actual by Department" \ --prop series1.name="Q2 Budget" \ --prop series1.values=Departments!C2:C7 \ --prop series1.categories=Departments!A2:A7 \ --prop series2.name="Q2 Actual" \ --prop series2.values=Departments!D2:D7 \ --prop series2.categories=Departments!A2:A7 \ --prop catTitle=Department \ --prop axisTitle="Amount ($)" \ --prop x=6 \ --prop y=1 \ --prop width=10 \ --prop height=14 \ --prop legend=True officecli set Q2_Budget.xlsx '/Departments' --prop freeze=A2 officecli set Q2_Budget.xlsx '/Summary' --prop freeze=A2
Based on OfficeCLI v1.0.34