Excel Crash Course
Exam from Wall
Street Prep - Wall
Street Prep.
2022-2023
1. What is forecast Revenue in 2017?
o Forecast Revenue in 2017 is given as 13,642,021.
2. What is forecast Net Income in 2016?
o Forecast Net Income in 2016 is given as 925,777.
3. If Depreciation&Amortization as a % of Capital Expenditures is changed to
30%, what is Net Income in 2017?
o Net Income in 2017 calculation: Original Net Income in 2017 = 1,123,438
Given change in Depreciation&Amortization as % of Capital Expenditures to
30%: New Net Income in 2017 = Original Net Income in 2017 + (Original
Net Income in 2017 * (30% - original %)) New Net Income in 2017 =
1,123,438 + (1,123,438 * (30% - original %))
4. What is the EBITDA % Margin in 2018?
o EBITDA % Margin in 2018 is given as 17.1%.
Now, let's proceed to answer the additional questions based on the dataset on tab Q5-19 in
the Exam Workbook.
5. Over the entire analysis period, which sales rep sold the highest cumulative
quantity of a single item?
o Rob Stewart
6. What is the item code of that item?
o 16
7. Over the entire analysis period, what is the highest selling item code by
quantity?
o 16
8. Over the entire analysis period, what is the second highest selling item code by
quantity?
o 39
9. Only considering postal codes 93372, 93403 and 93434, which postal code had
the highest total profit during the month of March?
o 93372
10. What was the profit?
o 13,691.4
, 11. During the month of April, which postal code bought the most of item 13 by
quantity?
o 93558
12. What was that quantity?
o 24
13. What is the total of Amy Adam’s sales over the analysis period?
o 492,153.8
14. What was her total profit?
o 255,548.1
15. During the three-month period of January through March, what was the
quantity of item 23 sold while Fred Rubble was the Manager on duty?
o 784
16. During the month of May, how many postal codes bought more than 700
products by quantity?
o 4
17. Over the entire analysis period, which sales rep gave the most discounts by
dollar amount?
o Oliver Winslow
18. What was that amount?
o 17,444.4
19. Over the entire analysis period, which item code did postal code 93930 spend
the greatest dollars on?
o 16
Let's address each question one by one based on the options provided:
20. **Identify a function for cell D6 that will return the fraction of the year elapsed assuming a
360 day count basis.**
The correct function for this is:
```
=YEARFRAC(D4,D5,1)
```
Option:
```
, • =YEARFRAC(D4,D5,2) B
```
This function calculates the fraction of the year between dates D4 and D5, using a 360-day count
basis (denoted by the third argument, 1).
21. **Identify the formula that will always output a date that is the end-of-month date 3
months after the date inputted in D5.**
The correct formula for this is:
```
=EOMONTH(D5,3)
```
Option:
```
• =EOMONTH(D5,3) C
```
This formula returns the last day of the month, 3 months after the date in D5.
22. **Identify the formula that, based on user inputs in cells B1 and B2, outputs the text
"animal lover" for users who have at least 1 dog and at least one cat, and outputs "lonely
person" when those conditions are not met.**
1.
A B
Exam from Wall
Street Prep - Wall
Street Prep.
2022-2023
1. What is forecast Revenue in 2017?
o Forecast Revenue in 2017 is given as 13,642,021.
2. What is forecast Net Income in 2016?
o Forecast Net Income in 2016 is given as 925,777.
3. If Depreciation&Amortization as a % of Capital Expenditures is changed to
30%, what is Net Income in 2017?
o Net Income in 2017 calculation: Original Net Income in 2017 = 1,123,438
Given change in Depreciation&Amortization as % of Capital Expenditures to
30%: New Net Income in 2017 = Original Net Income in 2017 + (Original
Net Income in 2017 * (30% - original %)) New Net Income in 2017 =
1,123,438 + (1,123,438 * (30% - original %))
4. What is the EBITDA % Margin in 2018?
o EBITDA % Margin in 2018 is given as 17.1%.
Now, let's proceed to answer the additional questions based on the dataset on tab Q5-19 in
the Exam Workbook.
5. Over the entire analysis period, which sales rep sold the highest cumulative
quantity of a single item?
o Rob Stewart
6. What is the item code of that item?
o 16
7. Over the entire analysis period, what is the highest selling item code by
quantity?
o 16
8. Over the entire analysis period, what is the second highest selling item code by
quantity?
o 39
9. Only considering postal codes 93372, 93403 and 93434, which postal code had
the highest total profit during the month of March?
o 93372
10. What was the profit?
o 13,691.4
, 11. During the month of April, which postal code bought the most of item 13 by
quantity?
o 93558
12. What was that quantity?
o 24
13. What is the total of Amy Adam’s sales over the analysis period?
o 492,153.8
14. What was her total profit?
o 255,548.1
15. During the three-month period of January through March, what was the
quantity of item 23 sold while Fred Rubble was the Manager on duty?
o 784
16. During the month of May, how many postal codes bought more than 700
products by quantity?
o 4
17. Over the entire analysis period, which sales rep gave the most discounts by
dollar amount?
o Oliver Winslow
18. What was that amount?
o 17,444.4
19. Over the entire analysis period, which item code did postal code 93930 spend
the greatest dollars on?
o 16
Let's address each question one by one based on the options provided:
20. **Identify a function for cell D6 that will return the fraction of the year elapsed assuming a
360 day count basis.**
The correct function for this is:
```
=YEARFRAC(D4,D5,1)
```
Option:
```
, • =YEARFRAC(D4,D5,2) B
```
This function calculates the fraction of the year between dates D4 and D5, using a 360-day count
basis (denoted by the third argument, 1).
21. **Identify the formula that will always output a date that is the end-of-month date 3
months after the date inputted in D5.**
The correct formula for this is:
```
=EOMONTH(D5,3)
```
Option:
```
• =EOMONTH(D5,3) C
```
This formula returns the last day of the month, 3 months after the date in D5.
22. **Identify the formula that, based on user inputs in cells B1 and B2, outputs the text
"animal lover" for users who have at least 1 dog and at least one cat, and outputs "lonely
person" when those conditions are not met.**
1.
A B
