This project covers Lesson 11 Variances. You are incorporating variances and Excel skills. In the template, you will only be allowed to type in the gray boxes.
Bandit’s Home Décor
Bandit Home Decor Company produces and sells velvet decorative pillows for use in home decor. Each pillow uses velvet for the pillows cover, cotton for the pillow’s shell, and hollowfibre for the pillows interior. You are the Controller for Bandit’s. As sales have grown, you have also become increasingly busy. To save yourself some time, you have decided to automate some of the reports that you have been producing manually every month. As a first step, you have decided to create an Excel spreadsheet program that your clerk can use to produce monthly variance reports for management.
Every month, your clerk will input new figures into your spreadsheet. However, your clerk isn’t very knowledgeable about accounting, so he will need to depend on your Excel program to be accurate, complete, and understandable. Your clerk only knows how to input data, save files, and print in Excel.
Your project is to create and produce three interconnected Standard Costing and Variance Analysis Reports in Excel.
Required:
Creating the Reports. Creating your report will require you to create at least three worksheets in your Excel Workbook:
Worksheet 1: Data Input Worksheet,
Worksheet 2: A Flexible Budget Variance Report, and
Worksheet 3: A Price and Efficiency Variance Report.
Worksheet 1:
The Report Name of Worksheet 1 is “Data Input Worksheet.” Above is an Excel template for Worksheet 1. You need to update the highlighted cells for the information you are given (in Variance Analysis Data) and format it as described in this assignment. Worksheet 1 serves as an input sheet for the rest of your Excel model. Every highlighted value is subject to change (this is where the accounting clerk will enter updated information).
Your first worksheet must be used for data input only. Your second and third worksheets are reports for management. These latter reports must use the input data from the first worksheet, plus your Excel formulas, to generate a Flexible Budget Variance Analysis Report (Worksheet 2) and a Price and Efficiency Variance Report (Worksheet 3).
Therefore, your Workbook must be structured so that it will: (1) allow for any of the highlighted information on Worksheet 1 to be changed and (2) automatically adjust for these changes and correctly generate revised numbers, variance indicators, dates, etc. on the other two worksheets.
Formatting: All numbers on Worksheet 1 should be formatted to show to the 3rd decimal place. Format dollar amounts with $ signs. DO NOT add $ to unit numbers.
Worksheet 2:
The Report Name of Worksheet 2 is “Flexible Budget Variance Report.” You must create this worksheet. This worksheet should have a header with the report name and date. The date should be linked to the date in worksheet 1. Be sure to include a separate line item for each of the types of Direct Material for Bandit’s (velvet, cotton, hollowfibre). See the sample worksheets for an outline of the format for this worksheet.
Formatting: In the “Standards” Column on Worksheet 2 format to show to the 3rd decimal place. Format the other columns on Worksheet 2 to show whole dollar.
Worksheet 3:
The Report Name of Worksheet 3 is “Price and Efficiency Variance Report.” You must create this worksheet. This worksheet should have a header with the report name and date. The date should be linked to the date in worksheet 1. You must complete a table that organizes and reports the Price Variance, Efficiency Variance, and Flexible Budget Variances for each of the 6 different cost items (velvet , cotton, hollowfibre, labor, variable overhead, and fixed overhead). See the sample worksheets for an outline of the format for this worksheet.
Formatting: Format the variances on Worksheet 3 to show to the 2nd decimal place (dollars and cents).
Variance Absolute Values and Indicators. All variances must be reported as Absolute Values. You must also use “Nested If” commands to automatically indicate whether the variances are Favorable [F], Unfavorable [U], or neither (-), whenever variances are reported. This siteLinks to an external site. give a good overview of Nested If statements
Citation
Adapted from: Silvester, K.J. (2017) Faith’s Country Primitives: An Excel Project In Flexible Budgeting and Standard Cost Analysis. Journal of Business Case Studies V13, N4 pp85-98 under copyright CC-BY
