Accounting Perspectives
Volume 23, Issue 4 pp. 605-612
Article
Open Access

Budget Variance Analysis Case: Contrasting Excel with Tableau*

Alexey Nikitkov, 

Corresponding Author

Alexey Nikitkov

Brock University

Corresponding author.

Search for more papers by this author
First published: 08 December 2024
https://doi.org/10.1111/1911-3838.12383
*

Accepted by Lan Guo.

ABSTRACT

en

This case not only familiarizes students with data analytics but also demonstrates its practical application. It presents a scenario of an electronic components company, equips students with budgetary and financial performance information, and challenges them to prepare a visual budgetary variance analysis for a board meeting. This hands-on exercise, conducted first in Excel and then in Tableau, enhances students' analytics skills. The learning tasks include creating formulas that depend on multiple worksheets in Excel, interpreting data results, and importing data into Tableau. Students will establish correct relations between imported tables; create formulas and new calculated fields; filter and format data; construct graphs; create workbooks, dashboards, and stories; publish graphical analyses on the Tableau cloud server; and distribute the presentation via a hyperlink. The case was tested with Master of Accountancy students in an Advanced Information Systems class and received high student evaluation scores. It is recommended for use in an undergraduate or graduate accounting or business program after students conceptualize the budgetary variance analysis.

RÉSUMÉ

fr

Étude de cas sur l'analyse des écarts budgétaires : comparer Excel et Tableau

Cette étude de cas permet aux étudiants de se familiariser avec l'analyse des données tout en démontrant son application pratique. Elle présente la situation d'une compagnie de composants électroniques, fournit aux élèves de l'information sur le rendement budgétaire et financier, et les met au défi de préparer une analyse visuelle des écarts budgétaires pour une réunion du conseil d'administration. Cet exercice pratique, d'abord effectué sur Excel et ensuite sur Tableau, renforce les capacités d'analyse des étudiants. Les tâches d'apprentissage comprennent la création de formules qui font appel à plusieurs feuilles de calcul dans Excel, l'interprétation des données, puis l'importation des données dans Tableau. Les étudiants établiront des liens corrects entre les tableaux importés, créeront des formules et de nouveaux champs calculés, filtreront et formateront des données, construiront des graphiques, élaboreront des classeurs, des tableaux de bord et des histoires, publieront des analyses graphiques sur le serveur infonuagique de Tableau et distribueront la présentation grâce à un hyperlien. Cette étude de cas a été mise à l'essai auprès d'étudiants à la maîtrise en comptabilité dans un cours sur les systèmes d'information avancés et a obtenu de bonnes notes de leur part. Il est recommandé de l'utiliser au premier cycle ou aux cycles supérieurs dans les programmes de comptabilité ou de gestion une fois que les étudiants ont conceptualisé l'analyse des écarts budgétaires.

THE CASE

WinMo is a North American consumer electronics and networking company headquartered in Mississauga, Ontario. It produces mobile and computer connectivity devices and peripherals for consumer and commercial use. These include wireless chargers, power banks, charging cables, data cables, audio and video adapters, headphones, earbuds, smart speakers, screen protectors, surge protectors, electronic device sanitizers, docking stations, data hubs, and network cables. WinMo operates in a highly competitive industry environment.

As a CPA, your recent hiring by WinMo carries significant weight. You have been appointed as an assistant to the controller of the division that manufactures high-end fiber-optic cables. Your task is to present the cost variance results for this production year at the upcoming board meeting. The controller has received comments that company senior management previously had difficulty understanding the variance analysis and thus ascribed small value to it. Your upcoming presentation is not just important—it is crucial to restoring upper management's attention and interest. Your work could potentially change the way the company's senior management perceives and values variance analysis.

The branch manufactures professional fiber-optic cable, model number WM-1210. Each requires 10 m of wire, one pair of connectors, 0.3 kg of plastic covering, and one unit of packaging. The manufacturing process requires, on average, ¼ h to construct a cable and some additional manufacturing supplies and lubrication oils, which are allocated based on labor hours. There are substantial fixed costs incurred by the branch. See the list of actual transactions recorded in the accounting system (Exhibit 1) and the enclosed budget (Exhibit 2).

EXHIBIT 1. Content of the “Input from AIS” tab of the Inputfile.xlsx provided to students

Date ActualCost Transaction Description Allocation
1/1/23 $10,400 Wire DM
1/1/23 $200 Janitorial contract FOH
1/5/23 $1,000 Manufacturing equipment lease FOH
1/9/23 $15,200 Wire DM
1/10/23 $400 Advertising and promotion FOH
1/10/23 $1,800 Commercial liability Insurance FOH
1/12/23 $1,210 IT/Phones/Computing equipment lease FOH
1/14/23 $1,410 Packaging DM
1/15/23 $1,980 Plastic DM
1/20/23 $5,800 Legal and Accounting Dep. payroll FOH
1/28/23 $512 Water bill, not manufacturing FOH
1/30/23 $4,000 Payroll, labour DL
1/30/23 $3,000 Manager's salary FOH
2/5/23 $1,000 Manufacturing equipment lease FOH
2/12/23 $300 Manufacturing supplies VOH
2/12/23 $100 Plant insurance FOH
2/14/23 $1,150 IT/Phones/Computing equipment lease FOH
2/27/23 $2,090 Plastic DM
2/28/23 $4,840 Payroll, labour DL
2/28/23 $3,200 Manager's salary FOH
3/1/23 $180 Janitorial contract FOH
3/3/23 $3,000 Connectors, pair DM
3/5/23 $1,000 Manufacturing equipment lease FOH
3/11/23 $600 Advertising and promotion FOH
3/12/23 $1,190 IT/Phones/Computing equipment lease FOH
3/15/23 $150 Lubrication oils, manufacturing VOH
3/20/23 $6,100 Legal and Accounting Dep. payroll FOH
3/30/23 $500 Manufacturing supplies VOH
3/31/23 $3,600 Payroll, labour DL
3/31/23 $3,000 Manager's salary FOH
4/5/23 $6,900 Wire DM
4/5/23 $1,000 Manufacturing equipment lease FOH
4/14/23 $1,220 IT/Phones/Computing equipment lease FOH
4/23/23 $2,460 Plastic DM
4/30/23 $4,800 Payroll, labour DL
4/30/23 $2,600 Manager's salary FOH

EXHIBIT 2. Content of the “Input from Budget” tab of the Inputfile.xlsx file provided to students, with three calculated fields added

image

Notes: This image shows how the “Input from Budget” tab of the Inputfile.xlsx file needs to be developed in the Part A (Excel) preparation phase: three calculated fields—BudgetCostPerUnit, ActualTotalCost, and ActualCostPerUnit—must be added to the initial data set to enable further calculations. This spreadsheet is provided as part of the instructor's solution file, Solution for Part A.xlsx.

The controller is wondering whether Tableau, a popular business intelligence/visualization tool, can facilitate senior management's understanding of the variance analysis. She asks you to create a Tableau presentation and share it with senior management. The adopted threshold is set at 3% of the total variance. The company has a policy to investigate any variance above this threshold, and if the threshold is met, you should highlight and analyze all objective causes for such high variance.

To facilitate this analysis, you download the following items from the information system:
  • all transactions for the year (see Exhibit 1) and
  • information from the budget adopted before the beginning of the fiscal year (see Exhibit 2).

PREPARATION FOR THE ANALYSIS

Download the Inputfile.xlsx file provided by your instructor. Make two copies of the file: Inputfile1 and Inputfile2. The first will be used for analysis in Excel and the second will be used in Tableau.

PART A: USING EXCEL

The learning goals for this part of the analysis are as follows:
  1. Make sense of the data inputs.
  2. Create additional data fields using Excel functions to allow for variance analysis.
  3. Calculate individual and total variances.

Step A1

Using Inputfile1.xlsx, add three more columns to the “Input from Budget” tab and calculate the following values with Excel formulas:
  • Budget Cost per Unit per budget category,
  • Actual Total cost per budget category, and
  • Actual Cost per Unit per budget category.

Step A2

Add a new sheet, “Individual variances,” and calculate the following values:
  • Direct Materials (DM) price variance for wire, plastic, connectors, and packaging;
  • Direct Materials (DM) quantity variance for wire, plastic, connectors, and packaging;
  • Direct Labor (DL) rate variance;
  • Direct Labor (DL) efficiency variance;
  • Variable Overhead (VOH) spending variance for supplies and oils;
  • Variable Overhead (VOH) efficiency variance for supplies and oils;
  • Fixed Overhead (FOH) budget variance; and
  • Fixed Overhead (FOH) volume variance.

Step A3

Add a new sheet entitled “Total variance,” assemble all the variances per category, and calculate the following values:
  • the resulting total variance,
  • the total budget value, and
  • the total variance as a percentage of the total budget.

Present your analysis and conclusion explaining the causes of any significant variance.

Consider these questions:
  • How visually appealing are your results?
  • What graphs can one create in Excel to illustrate the numerical results obtained?
  • How would you present your analysis to a large audience?

PART B: USING THE POWER OF TABLEAU

The learning goals for this part of the analysis are as follows:
  1. Learn to install Tableau.
  2. Know how to establish relations between two or more tables in Tableau.
  3. Develop the ability to compose new data field scripts (calculated fields).
  4. Learn how to create a graphical representation to address the required questions.
  5. Learn how to create a story (report) and distribute it from the cloud portal.
  6. Learn to present the results from your analysis and critically process these results.

Step B1

Install Tableau on a computer as follows:
  1. Visit https://www.tableau.com/.
  2. Select “Try Tableau for free.”
  3. Select “Tableau Desktop” and enter your email address. The download for PC or Mac starts automatically.
  4. Follow the instructions and register your copy for a 14-day trial, or longer as an academic subscription.
  5. Make sure you can open the Tableau application after the installation and registration.

Step B2

Import input data to Tableau:
  1. Open Tableau and connect to Inputfile2.xlsx.

  2. Select two tables, “Input from AIS” and “Input from Budget,” and connect them by selecting the following fields to join the tables:

    • “Transaction Description” from “Input from AIS” and
    • “BudgetCategory” from “Input from Budget.”

Step B3

Create the following calculated fields:
  • BudgetCostPerUnit = [BudgetCost]/[BudgetUnits]
  • ActualTotalCost = {INCLUDE [Transaction Description]:SUM([ActualCost])}
  • ActualCostPerUnit = [ActualTotalCost]/[ActualUnitsPurchased]
  • MaterialQty = ([ActualUnitsUsed] − [BudgetUnits]) × [BudgetCostPerUnit]
  • MaterialPrice = ([ActualCostPerUnit] − [BudgetCostPerUnit]) × [ActualUnitsUsed]
  • Difference = sum([ActualCost]) − sum([Budget Cost])
  • Wire (Price) = ATTR(IF[BudgetCategory] = 'Wire' THEN [MaterialPrice] END)
  • Plastic (Price) = ATTR(IF[BudgetCategory] = 'Plastic' THEN [MaterialPrice] END)
  • Connectors (Price) = ATTR(IF[BudgetCategory] = 'Connectors, pair' THEN [MaterialPrice] END)
  • Packaging (Price) = ATTR(IF[BudgetCategory] = 'Packaging' THEN [MaterialPrice] END)
  • Wire (Qty) = IF[BudgetCategory] = 'Wire' THEN [MaterialQty] END
  • Plastic (Qty) = IF[BudgetCategory] = 'Plastic' THEN [MaterialQty] END
  • Connectors (Qty) = IF[BudgetCategory] = 'Connectors, pair' THEN [MaterialQty] END
  • Packaging (Qty) = IF[BudgetCategory] = 'Packaging' THEN [MaterialQty] END
  • Supplies (Spend) = IF [Budget Category] = 'Manufacturing supplies' THEN {INCLUDE [Budget Category]:SUM([Actual Cost])} − [Actual Units Purchased] × [BudgetCostPerUnit] END
  • Oils (Spend) = IF [Budget Category] = 'Lubrication oils, manufacturing' THEN {INCLUDE [Budget Category]:SUM([Actual Cost])} − [Actual Units Purchased] × [BudgetCostPerUnit] END
  • Supplies (Eff) = IF [BudgetCategory] = 'Manufacturing supplies' THEN [BudgetCostPerUnit] × ([ActualUnitsPurchased] − [BudgetUnits]) END
  • Oils (Eff) = IF [BudgetCategory] = 'Lubrication oils, manufacturing' THEN [BudgetCostPerUnit] × ([ActualUnitsPurchased] − [BudgetUnits]) END
  • Direct Materials price variance = [Connectors (Price)] + [Packaging (Price)] + [Plastic (Price)] + [Wire (Price)]
  • Direct Materials quantity variance = SUM([Wire (Qty)]) + SUM([Plastic (Qty)]) + SUM([Connectors (Qty)]) + SUM([Packaging (Qty)])
  • Direct Labor rate variance = ATTR(IF [BudgetCategory] = 'Payroll, labour' THEN ([ActualCostPerUnit] − [BudgetCostPerUnit]) × [ActualUnitsPurchased] END)
  • Direct Labor efficiency variance = IF [BudgetCategory] = 'Payroll, labour' THEN ([ActualUnitsPurchased] − [BudgetUnits]) × [BudgetCostPerUnit] END
  • VOH spending variance = SUM([Oils (Spend)]) + SUM([Supplies (Spend)])
  • VOH efficiency variance = SUM([Oils (Eff)]) + SUM([Supplies (Eff)])
  • FOH budget variance = {SUM(IF [CategoryType] = 'FOH' THEN [ActualCost] END)} − {SUM(IF [CategoryType] = 'FOH' THEN [BudgetCost] END)}/{SUM(IF [BudgetCategory] = 'Payroll, labour' THEN [BudgetUnits]/[ActualUnitsPurchased] END)}
  • FOH volume variance = {SUM(IF [CategoryType] = 'FOH' THEN [BudgetCost] END)}/{SUM(IF [BudgetCategory] = 'Payroll, labour' THEN [BudgetUnits]/([BudgetUnits] − [ActualUnitsPurchased]) END)}
  • Total of variances = [Direct Materials price variance] + [Direct Materials quantity variance] + [Direct Labor rate variance] + SUM([Direct labour efficiency variance]) + [VOH Spending variance] + [VOH efficiency variance] + SUM([FOH budget variance]) + SUM([FOH volume variance])
  • Total Budget = Budgetcost
  • Variance % = [Total of variances]/SUM([BudgetCost])

Step B4

Produce the following graphical and numerical representations:
  • Workbook 1: Using a horizontal bar chart, graphically show the difference between the Budgeted and Actual total amounts per category (DM, DL, VOH, FOH), including numerical totals.
  • Workbook 2: Using a horizontal bar chart, graphically show the difference between Budgeted and Actual total amounts per account (e.g., Wire, Plastic, Connectors, etc.), including numerical totals.

  • Workbook 3: Using a bar chart, where positive and negative variances are separated by the 0 line, calculate and show individual variances (not totals) as follows:

    • DM price variance (wire, plastic, connectors, packaging),
    • DM quantity variance (wire, plastic, connectors, packaging),
    • DL rate variance,
    • DL efficiency variance,
    • VOH spending variance (supplies, oils),
    • VOH efficiency variance (supplies, oils),
    • FOH budget variance, and
    • FOH volume variance.

  • Workbook 4: Using a bar chart, where positive and negative variances are separated by the 0 line, tally up all the variances to show the Total of Variances composition.
  • Workbook 5: Show the Total variances, Total Budget figure, and the resulting variance in numerical and percentage of the total budget (e.g., 0.5% vs. limit of 3%) forms.

Step B5

Prepare a story (report) based on Workbooks 1–5. The story should include formatted workbook graphics and textual (or visual) comments compiled in a logical and cohesive report that addresses the original narrative about WinMo and its budget variance investigation.

Step B6

Open a Tableau public account and upload your Dashboards/Story to the Tableau cloud. Email your instructor the hyperlink referencing your presentation and giving access to your work.

To publish a story on the Tableau Online site:
  1. Register with Tableau online at https://identity.idp.tableau.com.
  2. In your Tableau file, select Server/Publish Workbook.
  3. Select the Tableau server and upload the file.
  4. After you verify that your workbook has been published, add your instructor's email as a new user and give them the ability to read, but not modify, the workbook. Send an email invitation to your instructor permitting access.

Teaching Notes

Teaching notes for instructional cases are not published in the journal but are made available to full CAAA member subscribers via the CAAA website. If you are a full member of the CAAA and wish to obtain a copy of the Teaching Notes, please go to https://www.caaa.ca/en/journals-and-research/accounting-perspectives-ap/ and click on “Teaching Notes”. You will then be directed to your member login where you can access and download the notes.

  • 1 The instructor may use the Sakai, Brightspace, or Top Hat portal to distribute files to the students.
  • 2 Refresh your memory about formulas to calculate Direct Materials Price and Quantity, Direct Labor Rate and Efficiency, Variable Overhead Spending and Efficiency, and Fixed Overhead Budget and Volume variances.

The full text of this article hosted at iucr.org is unavailable due to technical difficulties.