Project 4: Budget vs Actual & Variance Analysis (PT Cipta Alat Nusantara)

Background

What I Did

• Built a monthly budget for January to December 2026 across nine accounts, with the main assumptions centralized in a parameter block and each account given a stated basis, so that changing a parameter flows automatically through every sheet.
• Built a depreciation schedule for the three fixed assets based on the asset lives recorded in Accurate, including the filing cabinet purchased in January and depreciated from February, so that budgeted depreciation is consistent with existing assets.
• Compared the budget with January actual results, calculated variances in Rupiah and percent, and classified each account as favorable, unfavorable, or on budget, with variance direction distinguished between revenue and expense accounts.
• Separated the gross profit variance into a volume variance and a margin variance using the flexed budget method.
• Traced the margin variance to product level using net quantities, selling prices, and average cost per item from Project 1.
• Built an annual budget summary, a break-even analysis, and a trend chart, with an actual results row that can be filled in month by month.
• Built a simple reforecast that updates one assumption only, namely the gross margin for February to December following the January actual margin, while keeping the original budget as the baseline for variances.
• Added cross-checks between actual results and the Project 1 report, as well as between analysis sheets, so that every total can be traced.

Result

• In January, Gross Profit was IDR 4,168,000 below budget and Net Loss was IDR 12,068,000 against a budgeted IDR 7,975,000 (an unfavorable variance of IDR 4,093,000). The operating expense variance was only IDR 75,000 (favorable), so the main variance sits in gross profit.
• The gross profit variance consists of a volume variance of IDR 1,015,000 and a margin variance of IDR 3,153,000. Kertas HVS A4 80gr accounts for IDR 2,783,000 (88.3%) of the margin variance, because its margin is only 24.0% (target 35%) and it represents 40.7% of Net Sales. Selling price and cost per unit did not change during January, so this low margin is built into the price list rather than caused by price or cost changes during the month.
• The 2026 budget produces a full-year Net Loss of IDR 63,566,250, and budgeted monthly Net Sales (peaking at IDR 81.5 million in December) do not reach break-even in any month, which is about IDR 89 million at the target margin and about IDR 105 million at the January actual margin. A simple reforecast using the January actual margin produces a full-year Net Loss of IDR 108,988,429, which is IDR 45,422,179 larger than the budget, so the target margin and price list need to be reviewed.

Skills Demonstrated