Course Project: Comprehensive Problem 5

Now that you have had the chance to practice your Excel skills with some preprogrammed and model-building activities, it is time apply those skills and test the accounting concepts you have learned during the course.

The title of this course is “Advanced Automated Accounting” because you now have the opportunity to create automated accounting software using Excel. Exciting, I know. The project is to design a excel workbook around the Comprehensive Problem 5 in Accounting textbook (pg 1096 – 1097). The workbook should include three spreadsheets for parts A, B and C of the comprehensive problem 5. An outline for the workbook is as follows:

Spreadsheet One: Break-Even Analysis

  • Determine a fixed and variable portion of a utility cost using the high-low method.
  • Determine a contribution margin
  • Determine a fix cost
  • Determine a break-even number

Spreadsheet Two: Budgets

  • Include a production budget
  • Include a direct materials purchase budget
  • Include a direct labor budget
  • Include a factory overhead budget
  • Include a budgeted income statement

Spreadsheet Three: Variance Analysis

  • Determine a direct material price and quantity variances
  • Determine a direct labor rate and time variances
  • Determine a factory overhead controllable variance
  • Determine a factory overhead volume variance

This is a rough outline of what the software should be able to do. The rest is up to you. Be creative, have fun and WOW me. Use what you know about Excel and pretend you are creating software for a company to use frequently for break-even analysis, budgeting and variance analysis. The software (excel workbook) should be attractive and user friendly.