Excel project
Project #1: Excel Project
Hints
1
Getting Help
If it is a “how do I” question, then you must first look up the in the upper right corner of Excel.
After you read the information in the Help menu and still do not understand it, then point me to the specific part of the Help menu that you do not understand and I will be glad to explain it to you then.
The learning objective here is for you to get used to trying to figure things out for yourself as first option!
Hints of the Process
Concepts
Objectives:
Values by reference
Use of functions
Format appropriately
Refer to a different worksheet
Refer to a different workbooks
Carry-over Calculations (Q9: Quarter-over-quarter unit growth; Q14: Staffing expansion)
See calculation formulas
Compounding functions
Hands-on
Use professor’s template
First type a equal sign, i.e. “=”
ROUND, MROUND, IF, COUNTIF, etc.
Number vs. Currency, comma, “$”, etc.
Reference #s from 1st worksheet
Reference #s from seed.xlsx
= Last Value * Rate + Last Value
Ctrl + ~; Toolbar > Formulas > Show Formulas
Use AND, IF and COUNTIF within a single cell
3
How to Do Referencing
To refer to a different cell
To refer to another worksheet
To refer to another Excel file
Absolute Reference vs. Relative Reference: https://www.gcflearnfree.org/excel2016/relative-and-absolute-cell-references/1 /
Type “=” then click the other cell
Type “=” then click on the tab of that other worksheet
Type “=” then click on the tab of that other worksheet. However, make sure that you are a relative (and not an absolute file path)! Google this topic if you are having troubling making the relative path stick.
MROUND (number, multiple (0.25, 0.50, 0.75))
ROUND (number, num_digits (0, 1, 2))
Excel Programming/Functions Example
COUNTIF(range,criteria)
COUNT(value1,Value2)
Excel Programming/Functions Example
Excel Programming/Functions Example
AND(logical1,logical2)
IF(logical_test, value_if_true, value_if_false)
7
The Last Step
Q: How do you figure out which is the 1st profitable quarter?
A: If the profit for the current quarter is a positive number, and the total number of profitable quarters between the beginning till now is equal to one, i.e. use the IF function to figure out the first condition, AND that with the result of the COUNTIF function.
Rows 25-29 of the Income Statement worksheet are there to help you to break down the steps for figuring out the ‘Made It’ formula.
8
The Last Step: Pseudo-Code
# The profit for the current quarter is a positive number:
Value of Current > 0
# The total number of profitable quarters between the beginning till
# now is equal to one:
COUNTIF(The Beginning Value : Current Value, “>0”) = 1
AND (1, 2)
IF (3, “Make It”, “ “)
9