MANAGERIAL ACCOUNTING

Individual Assignment

Budgetary Planning | Budgetary Control | Standard Costing & Variance Analysis

Course Managerial Accounting

Assignment Type Individual Assignment
Total Marks 20 Marks
Topics Covered Sessions 13, 14, 15
Submission Format Microsoft Excel Workbook (.xlsx) + Written Report (.docx or .pdf)
 SECTION 1 — ASSIGNMENT OVERVIEW

1.1 Purpose
This individual assignment assesses your ability to apply the core concepts of managerial accounting in an
integrated, practical context. You will take on the role of a Management Accountant for a manufacturing company
of your own choice or creation, and will prepare a master budget, conduct a budgetary control analysis, and
perform a standard cost variance analysis — all based on data that you develop independently.

1.2 Your Responsibility: Developing a Company Scenario

IMPORTANT: No data is provided in this assignment. You must independently develop a realistic
manufacturing company scenario, including all financial figures, cost standards, and assumptions. Your data
must be plausible and internally consistent throughout all parts.

You may base your scenario on a real manufacturing company (using publicly reported or estimated figures) or a
fictional company of your own design. Either way, your company must:
• Manufacture and sell a clearly defined physical product.
• Use direct materials and direct labour as primary production inputs.
• Have identifiable fixed and variable cost components.
• Operate across at least four quarterly periods.

1.3 Assignment Structure

Part Topic Session Reference Marks

A Budgetary Planning — Master Budget Preparation Session 13 5
B Budgetary Control — Flexible Budget & Variance Session 14 5
Analysis
C Standard Costing & Variance Analysis Sessions 15 & 16 5
D Written Report & Recommendations All Sessions 5

TOTAL 20 Marks

1.4 Submission Requirements

• Excel Workbook (.xlsx): All numerical work — budgets, variance tables, and reconciliations — in a single,
well-organised file with clearly labelled worksheets for each part.
• Written Report (.docx or .pdf): A structured analytical response of 400–500 words addressing the questions
in Part D.
• All computed Excel cells must use formulas — do NOT hardcode calculated results.
• Your Assumptions worksheet must document all figures you have chosen and briefly justify their realism.
• Late submission penalties apply as per course policy.

1.5 Academic Integrity

This is strictly an individual assignment. Every student must develop their own unique company scenario and
independently complete all parts. Submitting identical or near-identical scenarios, datasets, or analyses
constitutes academic misconduct.

PART A — BUDGETARY PLANNING: MASTER BUDGET [5 Marks]

Your task: Using your own company data, prepare a Master Budget for one full financial year presented
across four quarters with an Annual Total column. Submit all budgets in a single Excel worksheet labelled
'Part A – Master Budget'.

Data You Must Define (in your Assumptions worksheet)

Required Input Guidance

Company name and product Any single physical manufactured product; state the unit of
measure.
Budgeted sales volume (units per quarter) State four quarterly figures reflecting realistic seasonal
patterns.
Selling price per unit Consistent across all quarters (or justify any change).
Finished goods inventory policy E.g. 10% of next quarter's sales. State your policy clearly.
Direct material: quantity per unit and price per E.g. 4 kg of steel @ $9/kg. Must be specific.
unit
Raw material inventory policy E.g. 5% of next quarter's production needs.
Direct labour: hours per unit and rate per hour E.g. 3 hours/unit @ $12/hr.
Variable manufacturing overhead rate ($/DL Applied per direct labour hour.
hr)
Fixed manufacturing overhead ($/quarter) A single fixed dollar amount per quarter.
Selling & admin expense — variable ($/unit) Separate variable and fixed components required.
and fixed ($/quarter)
Income tax rate Use a realistic rate, e.g. 25%–30%.

Task A1 — Sales Budget [1 Mark]

Prepare a quarterly Sales Budget showing budgeted units sold, selling price, and total sales revenue per quarter,
plus an Annual Total column.

Task A2 — Production Budget [1 Mark]

Prepare a Production Budget for each quarter using the formula:

Units to Produce = Expected Sales + Desired Ending Finished Goods Inventory − Beginning
Finished Goods Inventory
 • Apply your stated inventory policy; show beginning and ending inventory separately for all quarters.
• State your assumption for Q4 ending finished goods inventory.

Task A3 — Direct Materials Purchases Budget [2 Marks]

Prepare the Direct Materials Purchases Budget showing, per quarter:
• Total material required for production (units to produce × standard quantity per unit).
• Add: desired ending raw material inventory (per your stated policy).
• Less: beginning raw material inventory.
• Quantity to purchase and total cost of purchases (quantity × standard price).
• State your Q4 ending raw material inventory assumption.

Task A4 — Direct Labour Budget [1 Mark]

Prepare the Direct Labour Budget showing budgeted DL hours (units to produce × standard hours) and budgeted
DL cost (hours × standard rate) per quarter, plus annual totals.

Task A5 — Budgeted Income Statement [3 Marks]

Prepare a full-year Budgeted Income Statement using absorption costing. Include:
• Standard cost per unit workings (DM + DL + Variable OH + absorbed Fixed OH) — show clearly in Excel.
• Sales Revenue, Cost of Goods Sold (at standard), Gross Profit, Selling & Admin Expenses, Operating
Income (EBIT), Income Tax, and Net Income.

Note: Manufacturing Overhead and Selling & Admin Expense budgets are supporting schedules
required to complete A5. Present them clearly in your Excel worksheet even though they are not
separately marked.

PART B — BUDGETARY CONTROL [5 Marks]

Your task: Simulate actual results for ONE of your four quarters. Create realistic figures that deviate from
your budget — showing at least one cost above budget and at least one below. Document these actuals in
your Assumptions worksheet with a brief note explaining what 'happened' that quarter.

Task B1 — Static Budget vs. Actual Comparison [2 Marks]

In a worksheet labelled 'Part B – Budgetary Control', present:
• Your original static budget figures for the chosen quarter.
• Your actual results alongside.
• The static budget variance for each line item (Actual − Budget), labelled F (Favourable) or U
(Unfavourable).
• A brief note (in the Excel sheet) explaining why comparing actuals against a static budget can be
misleading when the actual volume differs from budget.
Task B2 — Flexible Budget & Variance Analysis [4 Marks]
Prepare a flexible budget for your chosen quarter based on your actual output level.
• Recalculate all variable cost lines at the actual activity level using your standard rates.
• Keep fixed costs at the original budgeted amount.
• Present three columns: (i) Flexible Budget, (ii) Actual Results, (iii) Flexible Budget Variance (F or U).
• Identify the single most significant variance using the management-by-exception principle. In a text box or
note within the Excel sheet, state its probable cause and suggest one corrective action for management.

PART C — STANDARD COSTING & VARIANCE ANALYSIS [5 Marks]

Your task: Using the same quarter's actual results from Part B, calculate the four key standard cost variances
for direct materials and direct labour. All workings must be shown step-by-step in a worksheet labelled 'Part
C – Variance Analysis'.

Additional Actual Data Required (in your Assumptions worksheet)

Item Guidance
Actual material purchased (quantity) and Actual price should differ from your standard price.
actual price paid
Actual quantity of material used in Should differ from the standard quantity for actual
production output.
Actual labour hours worked and actual rate Both should differ from your standard hours and rate.
paid AR can be derived: Total Cost ÷ Actual Hours.

Task C1 — Material Price Variance (MPV) and Material Quantity Variance (MQV) [2 Marks]

MPV = (SP − AP) × AQP MQV = (SQ − AQU) × SP SP = Std Price | AP = Actual Price |
AQP = Actual Qty Purchased | SQ = Std Qty for actual production | AQU = Actual Qty Used

• Calculate both variances, showing all component values clearly.

• State F or U for each variance.
• Verify: Material Cost Variance = MPV + MQV (reconciliation check required).

Task C2 — Labour Rate Variance (LRV) and Labour Efficiency Variance (LEV) [2 Marks]

LRV = (SR − AR) × AH LEV = (SH − AH) × SR SR = Std Rate | AR = Actual Rate | AH
= Actual Hours | SH = Std Hours for actual production

• Calculate SH explicitly (actual units produced × standard hours per unit).

 • If using total actual cost, derive AR = Total Actual Labour Cost ÷ Actual Hours.
• State F or U for each variance.
• Verify: Labour Cost Variance = LRV + LEV (reconciliation check required).

PART D — WRITTEN REPORT [5 Marks]

Submit a structured written response of 400–500 words as a separate file (.docx or .pdf). Address all three of the
following points:
1. Budgeting Process: Briefly explain why the Sales Budget must be prepared first and how it drives the rest
of the master budget. Use your own company as the example.

2. Budgetary Control Findings: Based on your Part B flexible budget analysis, assess your company's
performance in the chosen quarter. Apply the management-by-exception principle — identify the most
significant variance, suggest its likely cause, and recommend one corrective action.

3. Variance Interpretation: Interpret your Part C results. Explain what the material and labour variances
reveal about operational efficiency in your company, and identify which single variance is most
concerning and why.

Marks are awarded for the quality of analysis and the ability to link findings directly to your own company
scenario — not for length. Responses that are generic or disconnected from your data will not score well.

EXCEL WORKBOOK — REQUIRED STRUCTURE

Your Excel workbook must be built from scratch and contain the following worksheets:

Worksheet Name Required Contents

Assumptions All self-defined inputs: selling price, sales volumes, standards,
inventory policies, actual results for Part B/C, and brief justification
notes.
Part A – Master Budget Sales Budget (A1), Production Budget (A2), Direct Materials Purchases
Budget (A3), Direct Labour Budget (A4), supporting OH and S&A
schedules, and Budgeted Income Statement (A5).
Part B – Budgetary Static budget vs. actual comparison (B1) and flexible budget variance
Control analysis (B2), including written note on the most significant variance.
Part C – Variance Analysis Step-by-step calculations for MPV, MQV, LRV, LEV, with reconciliation
checks for both material and labour cost variances.

Presentation Standards
• All computed cells must contain Excel formulas referencing your Assumptions sheet — no hardcoded
calculated values.
• Label all variances as F (Favourable) or U (Unfavourable) directly next to each calculated amount.
• Use clear headings, borders, and shading to distinguish input data from calculations and totals.
• Use consistent number formatting: $#,##0.00 for currency, #,##0 for quantities, 0.0% for rates.