Journal of Business Cases and Applications

A process for assessing Excel skills using a capital budgeting

analysis
Cheri G. Etling
The University of Tampa

Christine Harrington
The University of Tampa

Jill A. Misuraca
The University of Tampa

ABSTRACT

Many institutions have technology mastery as one of their stated learning goals.
Accreditation increasingly requires more robust assessment of stated goals. However, assessing
Excel skills separate from understanding new concepts or problem-solving skills can be difficult.
This paper presents one method for assessing Excel skills within a simple capital budgeting
analysis. The paper includes a practice problem and a timed exercise used in this process. The
practice problem is completed in class or through video capture to help students understand the
capital budgeting concepts. The students can then focus on utilizing Excel to evaluate
substantially similar problems during a timed exercise. The paper also includes information
regarding the skills that are evaluated along with a rubric that can be used for assurance of
learning and grading.

Keywords: business education, assessment, information technology, finance

Copyright statement: Authors retain the copyright to the manuscripts published in AABRI
journals. Please see the AABRI Copyright Policy at http://www.aabri.com/copyright.html.

A process for assessing, page 1

 Journal of Business Cases and Applications

INTRODUCTION

Accreditation standards often encourage undergraduate business programs to include

information technology mastery as a stated learning goal. For example, AACSB International
Standard 9 states that the undergraduate business curriculum would normally include learning
experiences that enable students to use current technologies in business and management
contexts (AACSB, 2013, pp. 30-1). Although limited in scope, information technology skills are
frequently assessed by evaluating students’ proficiency with using Excel to solve business
problems. Capital budgeting is a common topic in an introductory finance course (Bianco &
Bosco, 2011) and provides an opportunity to embed this type of skills assessment into a related
spreadsheet assignment. However, assessing technology skills through a capital budgeting
analysis can be confounded with evaluating problem solving or topic knowledge. While these
confounding factors cannot be eliminated from a technology skills assessment within a business
context, their influence can be reduced through a carefully designed assessment process.
This paper describes a process for assessing student proficiency with technology in a
capital budgeting context that may be incorporated in an introductory finance or accounting
course. The process emphasizes technology skill assessment while reducing the focus on
problem solving ability related to capital budgeting and promotes consistent student preparation
and measurement of learning outcomes across multiple sections of a course. The process begins
with a practice problem in which students learn (reinforce) Excel skills including organization
and presentation of information, cell referencing, construction and use of formulas, and the
application of Excel functions. The practice problem may be completed in class as an instructor-
led exercise or outside of class with the aid of a demonstration video using video capture
software. After experience with the practice problem, students are required to complete an Excel
project (the assessment instrument) that is a simplified version of the practice problem.
Measurement of learning outcomes is based on a rubric that separately evaluates each trait of the
assignment.
The assessment instrument is useful for measuring proficiency with Excel because it may
be individually assigned and completed in a timed setting, either in class during a single class
period (in one hour or less), at a campus testing center, or through a learning management system
(e.g., Blackboard) for online and hybrid courses. Completing the project in a timed setting
simulates the time pressure often found in a work environment. A timed and controlled setting
also reduces opportunities for file sharing or collaboration among students and allows a more
accurate assessment of individual skills.
The organization of the paper is as follows: the assessment project is described along
with simple ways to vary the project to reduce incidences of academic dishonesty as well as to
add difficulty, if desired. The skills being assessed are discussed and an assessment rubric is
provided that can be used for an assurance of learning report and for grading. One practice
problem is discussed, including guidelines for using video capture software to create a
demonstration video. Solutions are provided for the assessment instrument and the practice
problem.

ASSESSMENT INSTRUMENT DESCRIPTION

The assessment instrument is a simple capital budgeting project entitled, “Cupcake

Bakery” that is completed in Excel. This project is similar to an example in Ross, Westerfield,

A process for assessing, page 2

 Journal of Business Cases and Applications

and Jordan (2011). The assessment instrument and its solution (containing numerical values,
formulas, and functions) are provided in Appendix A. The primary assignment objectives are for
students to demonstrate the skills of professional organization of information and the efficient
use of Excel formulas, functions, and cell referencing.
In the project scenario, the student desires to bake and sell cupcakes on evenings and
weekends. The student has a full time job (being a student!) and lives in an apartment. The
student must rent bakery space and purchase special equipment to pursue this project. The
project has a three year expected life and a stated required return on investment. Unit sales, price,
and cost data are given. The project entails an annual fixed cost (bakery rent), and a one-time
equipment purchase at the beginning of year 1. The project requires an infusion of net working
capital at the beginning of year 1 that is maintained in the intervening years and withdrawn at the
end of the project’s life. A tax rate is also given. To simplify the problem, the equipment has a $0
salvage value and straight-line annual depreciation. While MACRS depreciation is most
commonly used in capital budgeting, straight-line depreciation reduces the complexity of the
problem to focus more on Excel skills.
Students are provided a written set of instructions that include the project description and
assigned tasks. Students must complete the assignment using a blank Excel workbook. Students
must type in all inputs to the problem, create pro forma income statements for three years,
calculate each year’s pro forma free cash flow, NPV, IRR, and apply a decision rule to accept or
reject the project. Pro forma statements require the application of formulas, the sum function,
and cell references for efficient copying to complete the problem. NPV and IRR are calculated
using Excel functions with cell referencing. The decision to accept or reject the project is stated
in a text box.
The assignment assumes that students are familiar with capital budgeting concepts,
creating pro forma statements, and using the required Excel formulas, functions, and cell
referencing. To shift the emphasis of the assessment away from problem solving and toward
Excel skills, students complete a similar but more challenging practice problem to learn how to
construct a capital budgeting analysis in Excel. To further reduce the emphasis on problem
solving and knowledge of the capital budgeting process, the assignment also includes a basic
income statement and formula hints for calculating free cash flow. These hints may be omitted to
add difficulty to the assignment.
Students are able to complete the assessment project in one hour or less after they have
worked the practice problem. Therefore, the assessment project is ideally administered as a timed
assignment, either in class or through the learning management system. The timed aspect
simulates time pressure in the workplace and emphasizes the need to know the technology well
enough that it doesn’t interfere with completing the primary task of analysis. Timed, in-class
assignments also deter collaboration between students which is helpful for assessing individual
learning. To further reduce the possibility of collaboration, the instructor may assign variations
of the base assignment by changing the business description and input values. The authors assign
two variations, “Art Gallery” and “Dance Studio” that contain different numerical inputs and
lead to different project selection decisions (problems and solutions available from the authors).

ASSURANCE OF LEARNING AND GRADING

A detailed rubric facilitates consistent evaluation across students, sections, and multiple
instructors. The authors use the Excel skills assessment rubric in Table A1 (Appendix A) to

A process for assessing, page 3

 Journal of Business Cases and Applications

measure learning outcomes for the project. The rubric is designed to assess Excel skills only. The
rubric features the following four Excel skills:
1. Organization and presentation (creation of an input area, titles, spacing, spelling, text
box)
2. Application of Excel formulas (add, multiply, subtract)
3. Application of Excel functions (NPV, IRR, sum)
4. Use of cell references (absolute, relative)

The rubric classifies performance on each trait as does not meet expectations, meets
expectations, or exceeds expectations. These classifications are compatible with a scoring system
for measuring learning outcomes for assessment as well as grading. Classifying a student’s work
as meeting expectations (or not) depends on institutional or departmental standards. For example,
a student may be classified as meeting expectations if he or she achieves this classification on at
least three traits. The instructor may desire to include additional traits for grading knowledge of
concepts and accuracy of the solution. Given the general nature of the traits, the rubric may be
adapted to assess Excel skills in multiple classes and a variety of projects.
Students at the authors’ institution submit their completed Excel files to the learning
management system. Spreadsheets are reviewed in electronic format. The use of formulas,
functions, and cell references traits are graded by toggling to “show formulas” in Excel’s
“formulas” ribbon. For each trait, a student’s work is scored based on percentages of 60%, 75%,
and 100% for does not meet, meets, and exceeds expectations, respectively. A total percentage
score is calculated as the arithmetic average of the four traits given in the rubric. For assurance of
learning, a total score of 100% exceeds expectations, a score from 70% to 99% meets
expectations, and a score below 70% do not meet expectations. Based on these scoring intervals,
35% of the authors’ students exceed, 61% meet, and 4% do not meet expectations.
Assessment notes for each trait are presented below Table A1 (Appendix A). The
assessment notes discuss typical errors made by the authors’ students and how the errors are
treated according to the rubric. While conceptual errors related to capital budgeting are not
entirely separable, the assessment notes focus on errors in Excel usage.

PRACTICE PROBLEM

Giving students the opportunity to practice using Excel in the context of capital
budgeting is essential to their successful completion of the assessment project. Assigning several
practice or end of chapter problems using Excel will help accomplish this task. However, the
instructor should provide students with guidance on how to use Excel effectively by working at
least one practice problem. In addition to homework problems related to capital budgeting,
students at the authors’ institution prepare for the assessment project by completing an
instructor-led practice problem given in Appendix B. The solution to the practice problem
appears in Figure B1 (Appendix B). The practice problem is from Ross, et al. (2011, pp. 271-4)
and is similar in scope but slightly more challenging than the assessment instrument. The
problem in Ross, et al. is modified for minor changes in wording.
The practice problem provides a scenario based on development of a product called
“shark attractant”. The format for the basic inputs is similar to the assessment instrument. The
project life is three years, and the project description includes a required return, unit sales and
cost estimates, an annual fixed cost, a one-time equipment charge, annual depreciation expense,

A process for assessing, page 4

 Journal of Business Cases and Applications

and a tax rate. Like the assessment instrument, students are asked to prepare pro forma income
and free cash flow statements for three years. However, the practice problem also requires
students to determine year zero capital spending and the change in net working capital over the
three year period. The assessment instrument provides this information to reduce the need to
understand cash flow determination and to focus on Excel skills. Additionally, the practice
problem asks students to calculate the project’s NPV by first calculating each period’s
discounted total free cash flow, and then applying a formula to find NPV. The discounted cash
flow approach gives students a method for checking the accuracy of the NPV calculation via the
Excel function. This step may be omitted to reduce confusion for students regarding the required
calculations in the assessment instrument.
The practice problem may be completed in class, preferably with students following
along in Excel on lab computers or laptops. An in-class demonstration allows students to ask the
instructor questions when they do not understand a concept or miss a step in Excel. However,
some students have difficulty keeping up in class and may learn better by listening first and then
completing the practice problem on their own.
Video screen capture software can be used to record a demonstration of the practice
problem in Excel. A video demonstration provides an out-of-class resource to help students
prepare for the assessment problem or may be an alternative delivery method for an online or
hybrid course or if the instructor does not have the time or equipment for an in-class
demonstration. A video demonstration allows students to view the practice problem multiple
times and review any missed steps or techniques. However, instructors do not always anticipate
what students do not know. The best process is to create and post a video demonstration
following an in-class demonstration. If this is not possible, then student questions may also be
addressed through the learning management system discussion forum or chat.

CREATING A VIDEO DEMONSTRATION

A number of software products will capture what is being done on the screen along with
audio input. Jing is a product for Windows operating systems that is available for free download.
Quicktime is standard, preloaded software on Mac computers. Other video capture software
products with more sophisticated editing features are available for both Macs and PCs but
require license purchases (e.g., Snagit, Camtasia). The campus educational technology
department can help instructors discover currently purchased video capture software licenses or
the software may be purchased at an educational discount if desired.
While Jing is available for free, it is limited to five-minute videos that may result in
multiple “episodes” of the demonstration problem. Dividing the demonstration into sections
could be a strategy to individually present the basic problem set-up, pro forma income
statements, calculation of cash flows/NPV/IRR, and recommendations. Shorter videos also
increase upload and download speeds. However, recording the exercise in multiple segments
increases the instructor’s time to ensure that the exercise is fully captured and may require
students to toggle between multiple videos. Online tutorials are available to help instructors use
Jing. Guidance for using Quicktime is available through its help menu.
General guidelines for using screen capture software with Excel are described in
Appendix C. The guidelines address how to begin recording in Excel, including background
noise considerations. While working in Excel, the guidelines suggest that the instructor explain
every step in Excel including any error correction and addressing anticipated student questions

A process for assessing, page 5

 Journal of Business Cases and Applications

while recording. The guidelines conclude with simple reminders about ending the recording.
Because the video file sizes can be large, the file may need to be uploaded to cloud storage or a
private YouTube account with a link in the learning management system. The campus
educational technology department can provide information on the best way to make the video
available to students.

CONCLUSION

This paper describes a process for assessing technology mastery in the context of a
capital budgeting analysis that is completed in Excel. The process minimizes the influences of
knowledge of business concepts or problem solving skills to focus the assessment primarily on
Excel skills. The process includes working through a practice problem that is similar to the
assessment instrument, assigning the assessment instrument in a timed setting, and using a
detailed rubric to evaluate learning outcomes. Ideally, the instructor will work the practice
problem in class and provide a video demonstration online of either the same problem or another
one that is essentially the same in format. Administering the assessment instrument in a timed
setting discourages collaboration between students. The assessment rubric promotes focus on
Excel skills and helps to ensure consistent evaluation of learning outcomes across multiple
sections of a course. Although a capital budgeting analysis is featured in this paper, the Excel
skills and assessment process are general enough to apply to other topics in an introductory
business course, such as time value of money or basic statistical analysis.

REFERENCES

Association to Advance Collegiate Schools of Business (AACSB). (2013). Eligibility procedures

and accreditation standards for business accreditation. Retrieved from
http://www.aacsb.edu/accreditation/business/standards/2013/2013-business-
standards.pdf.
Bianco, C. A., & Bosco, S. M. (2011). Financial literacy: What are business schools teaching?
Journal of Global Business Management, 7(1).
Ross, S. A., Westerfield, R. W., & Jordan, B. D. (2011). Essentials of corporate finance (7th ed.).
New York, NY: McGraw-Hill.

APPENDIX A

Cupcake Bakery Project

You have a full-time job, but want to bake and sell cupcakes on evenings and weekends. You
live in an apartment, so you will need to rent a bakery space and spend some money up front for
special equipment. You want to know if this idea is worth pursuing. You estimate the following
items related to the project:

Cupcake bakery project description

Project life (years) 3
Required return 20%

A process for assessing, page 6

 Journal of Business Cases and Applications

Sales data
Unit Sales (annual) 8,650
Unit Price $2.50
Cost data
Unit cost $1.00
Bakery rent (annual) $12,000
Equipment cost (one-time purchase) $900
After-tax equipment salvage (Year 3) $0
Depreciation expense (annual) $300
Tax rate 35%
Year 0 total capital spending - equipment $900
Change in net working capital
Year 0 -$1,500
Year 1 $0
Year 2 $0
Year 3 $1,500

Tasks to complete: use Excel to

1. Prepare pro forma income statements for three years
2. Prepare pro forma cash flows from assets (free cash flow) for three years
3. Calculate the project’s NPV (use Excel function)
4. Calculate the project’s IRR (use Excel function)
5. In your spreadsheet, use a text box and state whether you accept or reject this project, and
briefly explain your rationale.

Income statement
Sales
Cost of Goods
Gross Profit
Fixed Costs
Depreciation
EBIT=Gross profit – fixed costs – depreciation
Tax Expense=EBIT*tax rate
Net Income

Total project free cash flow

Operating cash flow=EBIT+ depreciation – tax expense
Change in NWC (see project description)
Capital spending

A process for assessing, page 7

 Journal of Business Cases and Applications

Figure A1 Cupcake Bakery Project Solution with Excel Formulas and Functions

A process for assessing, page 8

 Journal of Business Cases and Applications

Table A1 Excel Skills Assessment Rubric

Does not meet

Trait Meets expectations Exceeds expectations
expectations
Does not present Presents relevant Presents relevant
relevant information and information and results in a information and results
results in a clear and relatively clear and in a clear and
professional format with professional format with at professional format with
multiple spelling errors most one spelling error and no spelling errors and
Organization and
or failure to create an creates an input area, titles, creates an input area,
presentation
input area, titles, appropriate spacing, and a uses appropriate titles,
appropriate spacing, a text box for the discussion spacing, and a text box
text box for the with only minor for the discussion with
discussion, or does not deficiencies no deficiencies
complete the problem
Errors in formula input Uses formulas in Excel for Uses formulas in Excel
in two or more formulas all basic calculations with for all basic calculations
Application of or does not utilize Excel errors in formula input in at with no errors when
Excel formulas for all basic calculations most one formula inputting the formulas
– enters results
calculated by hand
Does not apply Applies appropriate Excel Applies appropriate
appropriate Excel functions such as NPV, Excel functions such as
functions such as NPV, IRR, sum, etc. where NPV, IRR, sum etc.
IRR, sum, etc. where needed to achieve an efficiently and
Application of
needed or used accurate answer but not as accurately where needed
Excel functions
incorrectly efficiently as possible, or (i.e. uses ranges instead
missing at most one of individual cell
function references)

Does not use cell Uses cell references for all Uses cell references for
references for two or calculations including all calculations and uses
more calculations Excel functions but does relative and absolute
not use relative and references appropriately
Use of cell
absolute references to allow for efficient
references
appropriately in all cases to copy/paste
allow for efficient
copy/paste

Assessment notes

1. Organization and presentation

a. “#####” in a cell is usually caused by a zoom level difference between the student’s
and instructor’s monitor. Students are not penalized for this type of presentation
problem.
b. Text boxes should be sized to fit the discussion. If the text boxes do not show all of
the text or are excessively large, then students should not exceed expectations.

A process for assessing, page 9

 Journal of Business Cases and Applications

2. Application of Excel formulas

a. Errors in formulas most commonly result from incorrect cell referencing and choice
of incorrect mathematical operator (e.g., addition instead of subtraction).
3. Application of Excel functions
a. Some students do not know how to apply the NPV and IRR functions because they
have not completed the practice problem. In these cases, students do not meet
expectations. Students who do not know to subtract the year 0 cash flow from the
NPV function, but correctly apply the sum and IRR functions do not meet
expectations.
b. A few students will correctly use the sum and IRR functions, but neglect to include
the NPV function and instead calculate NPV by summing the annual discounted cash
flows as in the practice problem. Students in this latter case meet expectations.
c. A few students will add a string of referenced cells to sum a column instead of using
the sum function (e.g., =+B1+B2+…). These students meet expectations provided all
other functions are applied appropriately, but do not exceed expectations due to
inefficient use of Excel functions.
d. Students should not use the sum function to subtract, e.g., =SUM(B13-B12). A
student could at most meet expectations if they incorrectly use the sum function.
4. Use of cell references
a. In rare cases, students will correctly identify functions, but type in numbers instead of
using cell references. These students do not meet expectations even if cell references
are used correctly elsewhere.
b. Students may use absolute and relative references inefficiently. In order to exceed
expectations, the students must use the appropriate type of cell reference in the year
one pro forma so that correct pro formas will be created in later years by using
copy/paste.

APPENDIX B

Shark Attractant Project

You have an idea for a new product called Shark Attractant, a “perfume” that attracts sharks.
You want to know if producing and selling this product will add value to your firm. You
estimate the following items related to the project:

Shark attractant project description

Project life (years) 3
Required return 20%
Sales Data
Unit Sales (annual) 50,000
Unit Price $4

A process for assessing, page 10

 Journal of Business Cases and Applications

Cost Data
Unit cost $2.50
Production facility rent (annual) $12,000
Equipment cost (one-time purchase) $90,000
Depreciation expense (annual) $30,000
Tax rate 34%
Net working capital investment (annual)* $20,000
*Assume NWC is recovered in Year 3

Use Excel to
1. Prepare pro forma income statements for three years
2. Prepare pro forma capital requirements for three years
3. Prepare pro forma free cash flow for three years
4. Calculate the project’s NPV using a formula
5. Calculate the project’s NPV using an Excel function
6. Calculate the project’s IRR using an Excel function

Insert a text box into your worksheet and answer the following questions based on your work:
1. Does the NPV method recommend selecting this project if it is independent? Briefly explain
your answer.
2. Does the IRR method recommend selecting this project if it is independent? Briefly explain
your answer.

A process for assessing, page 11

 Journal of Business Cases and Applications

Figure B1 Shark Attractant Project Solution with Excel Formulas and Functions

APPENDIX C

Guidelines for Using Video Capture Software with Excel

Getting started
 Have Excel open with the starting worksheet before opening the screen capture software.
 Choose to create a new screen recording.
 Choose the type of microphone that will be used
o If creating the video during the in-class demonstration, then use the computer’s built
in microphone if possible. This will capture student questions as well.
o If creating the video outside of class, it is recommended to find a quiet location and
use a headset microphone to minimize background noise. Students have indicated that
background noise from pets, children, music, etc. is distracting.
 Choose to start the recording and begin working in Excel. A controller bar will appear that
allows the instructor to pause and stop recording if needed.
Within Excel
 Speak slower and more carefully than normal, avoiding monotone speech or students will
lose interest.

A process for assessing, page 12

 Journal of Business Cases and Applications

 Explain everything that is done. Don’t assume that students watching the video will
understand why each step is important.
 Read through the problem and pause as each piece of information is entered. This helps
students break what appears to be an overwhelming amount of information into manageable
pieces.
 Don’t edit out minor input errors. Correct any errors while recording and move on.
Correcting errors in the video helps students understand that they don’t have to start over if
they make a mistake.
 Excel often “guesses” incorrectly at the cell format that is desired. Explain that to the
students as corrections are made.
 Try to anticipate questions students have or what they don’t know and address these items
proactively in the video. For example, explain why formatting the years horizontally and
using absolute and relative references allows the user to create pro forma statements in year
one and then copy the remaining years to reduce the amount of work required and reduce the
probability of input errors.
Making the video available
 Choose to stop recording when the problem is completed.
 Save the video.
 Watch the entire video to make sure that it recorded as intended and that major edits are not
needed.

A process for assessing, page 13