Case FA-4300 Excel: Job cost analysis for Ivy Corporation

Dr. Wendy Tietz, CPA, CMA, CSCA, CGMALeave a comment

In this case, students use a variety of pivot tables in Excel to summarize, sort, and aggregate more than 4,800 job cost records. All work is performed in Excel. This case is suitable for assignment after job costing been covered in managerial accounting. You can view this case at this link (this is the same link that you will use to assign to your students.)

Screenshot of Excel

To assign the case to your students, give them this link. All student instructions are included at that link – as are links for tutorial videos for Excel skills needed and accounting concept skills needed.

How to obtain case materials

***You can obtain the teaching notes and solution for the Ivy Corporation Job Cost Analysis Excel case by requesting it at this link.***

You will need to fill out a brief form and then we will verify your instructor status prior to sending you the instructor materials. We generally send the Instructor Guide within a week after the request; we will contact you if we need additional information to verify your instructor status.

Be sure to add drwendytietz@gmail.com to your contacts. Check your spam folder if you have not received the materials from us within a week.

Case FA-3801 Excel: Financial statement analysis for Jubilee Company

Dr. Wendy Tietz, CPA, CMA, CSCA, CGMALeave a comment

In this case, students use data found in a company’s financial statements in an Excel file to various ratios, including the current ratio, debt ratio, and gross margin percentage. All work is performed in Excel. This case is suitable for assignment after the basic ratios have been covered. You can view this case at this link (this is the same link that you will use to assign to your students.)

screenshot

To assign the case to your students, give them this link. All student instructions are included at that link – as are links for tutorial videos for Excel skills needed and accounting concept skills needed.

How to obtain case materials

***You can obtain the teaching notes and solution for the Jubilee Company Financial Statement Analysis Excel case by requesting it at this link.***

You will need to fill out a brief form and then we will verify your instructor status prior to sending you the instructor materials. We generally send the Instructor Guide within a week after the request; we will contact you if we need additional information to verify your instructor status.

Be sure to add drwendytietz@gmail.com to your contacts. Check your spam folder if you have not received the materials from us within a week.

Case FA-3001: Transaction analysis and the accounting equation for Pepper Food Delivery – Excel

Dr. Wendy Tietz, CPA, CMA, CSCA, CGMALeave a comment

In this case, students use transaction data in an Excel file to calculate assets, liabilities, equity, net income, the accounting equation, and other basic data. All work is performed in Excel. This case is suitable for early in the introductory financial accounting course – right after the accounting equation, transaction analysis, and the basic statements are covered. You can view this case at this link (this is the same link that you will use to assign to your students.)

Sample screen shot of tutorial for project

To assign the case to your students, give them this link. All student instructions are included at that link – as are links for tutorial videos for Excel skills needed and accounting concept skills needed.

How to obtain case materials

***You can obtain the solution for the Pepper Food Delivery Excel case by requesting it at this link.***

You will need to fill out a brief form and then we will verify your instructor status prior to sending you the instructor materials. We generally send the solution within a week after the request; we will contact you if we need additional information to verify your instructor status.

Be sure to add drwendytietz@gmail.com to your contacts. Check your spam folder if you have not received the materials from us within a week.

Introducing emerging tech in introductory accounting

Dr. Wendy Tietz, CPA, CMA, CSCA, CGMALeave a comment

We have a lot to cover in introductory financial accounting and in introductory managerial accounting. However, to only show students the manual way of doing things (i.e., pencil, paper, calculator) is doing them a disservice – they don’t get to see how accounting is much more than rote calculations.

I think certain emerging technology topics are appropriate for introductory accounting. I have developed a few Robotic Process Automation (RPA) that are suitable for intro accounting – one is a simple Hello World bot and another one is a bot that takes a selfie of itself while it runs. I have also developed a few Python projects for absolute beginners that will work in introductory accounting.

There will also be some other emerging tech projects posted here on blockchain, ETL, and others.

I am going to be using the RPA projects and the Python projects in my own classes in Spring 2020. Once I work through any bumps, I will be sharing those projects here. Stay tuned….

Wendy

Data analytics project: Contribution margin analysis and segment margins for introductory managerial accounting

Dr. Wendy Tietz, CPA, CMA, CSCA, CGMALeave a comment

This blog post contains a data analytics project that is suitable for introductory managerial accounting. The student uses Excel to analyze a data set that contains more than 48,000 records. The data analytics project can be assigned in the course once the concepts of contribution margin, contribution margin ratio, and segment margins reviewed.

Overview of project

This project uses sales and cost data for a fictitious cabinet hardware company, Cabinet Accessories Company (CAC.) In this project, the student will use Excel to create a variety of pivot tables, format the pivot tables, apply filters, and create calculated columns. The student also needs to cleanse the data set by finding and correcting errors in the data set. In addition, the student will create a pivot chart.

Project resources

The tutorial video has closed captioning; the annotated slides for the tutorial video have the same script. The tutorial data set is an Excel file containing a small subset of the full data set; I used this small data set in creating the tutorial video.

Office 365 or Excel 2016 (newest versions)

Anyone doing this project will need to have either Office 365 or Excel 2016. Updates Microsoft has made to Excel 2016 include the ability to automatically group dates by quarters and years (an integral part of this project), so if a student says that their pivot table does not have quarters or years (or any other issue), tell them to update their version of Office 365 and/or Excel 2016.

Solution

If you would like to have a copy of the Excel solution file, please fill out the request form. Once I verify your status as an instructor, I will email the file to you, usually within a week. Please do not post the solution file on the internet and please do not share the solutions file with your students. The Excel solutions file is for your own use only.

Data analytics project: Budgeting and performance evaluation for introductory managerial accounting using the City of Somerville, MA, online checkbook data set

Dr. Wendy Tietz, CPA, CMA, CSCA, CGMALeave a comment

This project covers budgeting and performance evaluation in introductory managerial accounting and uses Excel.

Overview of this project

This project uses the transaction data from the City of Somerville, Massachusetts, which posts its checkbook online for public use. In this project, the student will use Excel to create a variety of pivot tables, format the pivot tables, apply filters, and create calculated columns. In addition, the student will create a pivot chart. The student will also prepare a budget variance report, using the information from a pivot table, and will analyze the variances.

Project resources

The tutorial video has closed captioning; the annotated slides for the tutorial video have the same script. The tutorial data set is an Excel file containing a small subset of the full data set; I used this small data set in creating the tutorial video.

Office 365 or Excel 2016 (newest versions)

Anyone doing this project will need to have either Office 365 or Excel 2016. Updates Microsoft has made to Excel 2016 include the ability to automatically group dates by quarters and years (an integral part of this project), so if a student says that their pivot table does not have quarters or years (or any other issue), tell them to update their version of Office 365 and/or Excel 2016.

Solution

If you would like to have a copy of the Excel solution file, please fill out the request form. (There is a separate request form for each project.) Once I verify your status as an instructor, I will email the file to you within a week. Please do not post the solution file on the internet and please do not share the solutions file with your students. The Excel solutions file is for your own use only.

Data analytics project: Gross profit analysis for introductory financial accounting

Dr. Wendy Tietz, CPA, CMA, CSCA, CGMALeave a comment

This blog post contains a data analytics project that is suitable for introductory financial accounting. The student uses Excel to analyze a data set that contains more than 48,000 records. The data analytics project can be assigned early in the course once the concept of gross profit is reviewed.

Overview of project

This project uses sales and cost data for a fictitious cabinet hardware company, Cabinet Accessories Company (CAC.) In this project, the student will use Excel to create a variety of pivot tables, format the pivot tables, apply filters, and create calculated columns. The student also needs to cleanse the data set by finding and correcting errors in the data set. In addition, the student will create a pivot chart.

Project resources

The tutorial video has closed captioning; the annotated slides for the tutorial video have the same script. The tutorial data set is an Excel file containing a small subset of the full data set; I used this small data set in creating the tutorial video.

Office 365 or Excel 2016 (newest versions)

Anyone doing this project will need to have either Office 365 or Excel 2016. Updates Microsoft has made to Excel 2016 include the ability to automatically group dates by quarters and years (an integral part of this project), so if a student says that their pivot table does not have quarters or years (or any other issue), tell them to update their version of Office 365 and/or Excel 2016.

Solution

If you would like to have a copy of the Excel solution file, please fill out the request form. Once I verify your status as an instructor, I will email the file to you within a week. Please do not post the solution file on the internet and please do not share the solutions file with your students. The Excel solutions file is for your own use only.

Data analytics project: Fixed Asset Analysis for Hula Hut Burgers

Tracie Miller-NoblesLeave a comment

Overview of this project

This project uses fixed asset information from anonymized and modified data for a tax client from a public accounting firm. The data set includes 1,500 fixed asset records. In this project, the student will use Excel to create a variety of pivot tables, format the pivot tables, apply filters, create calculated fields, use the slicer and timeline tool. In addition, students will be asked to analyze the data for errors. The students will prepare several different fixed asset schedules and also a pivot chart (column type) that depicts book value by asset category and region.

Project resources

Learning Objectives

  1. Create a pivot table in Excel
  2. Format a pivot table
  3. Apply filters to a pivot table
  4. Create sum columns in a pivot table
  5. Create a calculated field
  6. Use the slicer tool in a pivot table
  7. Analyze data for errors
  8. Use the timeline tool in a pivot table
  9. Create a pivot chart in Excel

Office 365 or Excel 2016 (newest version)

Anyone doing this project will need to have either Office 365 or Excel 2016. It is important that students update their version of Office 365 and/or Excel 2016.

Solution

If you would like to have a copy of the Excel solution file, please fill out the request form. (There is a separate request form for each project.) Once I verify your status as an instructor, I will email the file to you within a week. Please do not post the solution file on the internet and please do not share the solutions files with your students. The Excel solutions file is for you own use only.

Data analytics project: Transaction analysis for introductory financial accounting using the City of Somerville, MA, online checkbook data set

Dr. Wendy Tietz, CPA, CMA, CSCA, CGMALeave a comment

Overview of project

This project uses the transaction data from the City of Somerville, Massachusetts, which posts its checkbook online for public use. In this project, the student will use Excel to create a variety of pivot tables, format the pivot tables, apply filters, and create calculated columns. In addition, the student will create a pivot chart. Essentially, the student is analyzing the transaction data from Somerville to answer several questions using pivot tables and charts in Excel.

Project resources

The tutorial video has closed captioning; the annotated slides for the tutorial video have the same script. The tutorial data set is an Excel file containing a small subset of the full data set; I used this small data set in creating the tutorial video.

Office 365 or Excel 2016 (newest versions)

Anyone doing this project will need to have either Office 365 or Excel 2016. Updates Microsoft has made to Excel 2016 include the ability to automatically group dates by quarters and years (an integral part of this project), so if a student says that their pivot table does not have quarters or years (or any other issue), tell them to update their version of Office 365 and/or Excel 2016.

Solution

If you would like to have a copy of the Excel solution file, please fill out the request form. Once I verify your status as an instructor, I will email the file to you within a week. Please do not post the solution file on the internet and please do not share the solutions file with your students. The Excel solutions file is for your own use only.

KAT Insurance: Data analytics case for introductory managerial accounting

Dr. Wendy Tietz, CPA, CMA, CSCA, CGMALeave a comment

In addition to the data analytics case we posted last week for introductory financial accounting, we have also developed a case, using the same KAT Insurance Company data set, for introductory managerial accounting. The three of us who teach introductory accounting (Wendy Tietz, Jennifer Cainas, and Tracie Miller-Nobles) developed a KAT Insurance case to introduce data analytics in the introductory managerial accounting course. This case uses real-life insurance company data that has been anonymized and simplified. The case itself is a one-page case and is designed to be able to be completed in 30 – 40 minutes with each software tool (Excel, Power BI, and Tableau.)

This case teaches students how to drill-down into a company’s sales data, gaining a deeper understanding of the company’s sales, variable costs, and contribution margin, and how this information can be used for decision-making. Students will use Excel, Power BI, and/or Tableau to analyze the sales transactions for an insurance company. Students will first have to find and correct errors in the data set. Students then will use various techniques to analyze the case. Step-by-step tutorial videos will guide students through the Excel, Power BI, and Tableau portions of the case analysis. This case will introduce the student to data analytics topics including data cleansing, data dictionaries, and data visualizations.

***You can obtain the Instructor Guide for the KAT Insurance Managerial Accounting case by requesting it at this link.***

Give us about 5 – 8 days to respond – but check your Spam folder for the email if you do not see our email within that approximate time frame.

Give us about 5 – 8 days to respond – but check your Spam folder for the email if you do not see our email within that approximate time frame. (You may want to add drwendytietz@gmail.com to your Safe Senders list. Please don’t email to that email address – that is an email address used solely for distributing the KAT Insurance resources. Thank you!)

Several resources have been developed for this case to make it easy to integrate into introductory managerial accounting courses. Read on for the resources that have been developed and for information on how to request the case and related resources.

What’s included

The following resources are available for this case and are included or linked in the Instructor Guide for the KAT Insurance Managerial Accounting case:

  1. Case (one-page Word handout)
  2. Student guide (Word)
  3. General learning objectives for the overall case
  4. Specific learning objectives (related to specifically to Excel, Power BI, and Tableau)
  5. Data sets containing 65,000+ records (4 versions: A, B, C, and D)
  6. 24-record tutorial data set
  7. Step-by-step tutorial video (close captioned)
  8. Scripted tutorial video slides (pdf)
  9. Editable grading rubric (Word document in Appendix A)
  10. Multiple-choice questions with solutions for each of the four data sets Excel solution files
  11. Optional video assignment instructions

How to obtain case materials

***You can obtain the Instructor Guide for the KAT Insurance Managerial Accounting case by requesting it at this link.***

You will need to fill out a brief form and then we will verify your instructor status prior to sending you the Instructor Guide. We generally send the Instructor Guide within a week after the request; we will contact you if we need additional information to verify your instructor status.

Be sure to add drwendytietz@gmail.com to your contacts. Check your spam folder if you have not received the materials from us within a week.

The Instructor Guide contains links to all the resources listed above. We do ask that you not share the Instructor Guide with students. Please do not post the solutions to the case on Blackboard, Canvas, or any other Learning Management System. Also, please do not post the Instructor Guide or the solutions on any web site. (Thank you in advance for helping to maintain the academic useful life of the materials.)

Again, you can obtain the Instructor Guide for the KAT Insurance Managerial Accounting case by requesting it at this link.

We hope you and your students find value in this data analytics case for introductory managerial accounting. Please share any feedback with us about your experiences in using the case. Enjoy! Also, remember that we also have KAT Insurance case for introductory financial accounting (see link.)

Sincerely,

Wendy M. Tietz, PhD, CPA, CMA, CSCA

Jennifer M. Cainas, DBA, MAcc, CPA

Tracie L. Miller-Nobles, CPA