This assignment has three parts, A, B, and C. Each part must be on a separate worksheet.
Curtiss Investing provides stock investment recommendations for its clients. Data about the stocks that Curtiss analyzes are in the following table:
Stock EPS in 2019 EPS in 2020 2019 PE ratio 2020 PE ratio
Avocado $3.47 $8.47 11.9 17.5
Banana $4.23 $4.89 10.6 14.1
Cherry $1.27 $1.07 15.4 10.2
Note: EPS = Earnings per share. PE = Price-to-earnings ratio.
The recommendations depend on the type of client and the performance of the stock. The client types and the recommendation rules are as follows:
Individual investors who already own the stock:
Hold if the EPS increased from 2019 to 2020
Individual investors who do not own the stock:
Buy the stock if the EPS and the PE ratio increased from 2019 to 2020
Otherwise, do not buy
For pension plans that already own the stock:
Buy more if the EPS increased 10% or more from 2019 to 2020
Sell if the EPS decreased from 2019 to 2020 or if the 2020 PE ratio is below 15
Add columns with formulas that determine the stock recommendations for each client type for each stock. The formulas MUST use the IF function to determine the recommendation based on the above recommendation rules.
There are no user input cells in Part A.
At Curtiss College, students who register for 12 or more credit hours are Full-Time and pay $9,500 in tuition. Students who register for less than 12 credit hours are Part-Time and pay $850 per credit hour. Create a worksheet with user input cells for the student’s name and the number of credit hours for which the student is registering. The worksheet must then determine (1) whether the student is Full-Time or Part-Time and (2) the amount of tuition that the student owes.
Pretend that you are a student at Curtiss College and input your name and the number of credit hours for which you are registering.
You MUST solve both (1) and (2) by using the IFS function.
Enter the following data in a worksheet (this are data NOT user input cells):
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year
Sales $65,400 $68,900 $93,500 $49,100
Advertising Expense $9,100 $10,200 $15,000 $1,900
Rent Expense $1,900 $1,900 $2,200 $2,200
Wage Expense $20,000 $21,000 $25,000 $28,000
Total Operating Expenses
Complete all of the following required items:
Add formulas that calculate the Total Operating Expenses and Operating Income for each quarter
Add formulas in the Year column that sum the total for the year by adding up the four quarters for each row
Conditionally format the quarterly rent expense cells so that every cell containing $2,200 is formatted with Yellow Fill with Dark Yellow Text
Conditionally format the quarterly operating income cells so that the three smallest quarterly operating income amounts are formatted with Red Text
Conditionally format the year total amounts for advertising expense, rent expense, and wage expense with a solid fill data bar (your choice of color)
Conditionally format the four quarterly sales amounts with an icon set of your choice
In the cell below the total operating income for the year, create a formula using an IF function with an IS function as the logical test. If the total operating income is even, then display “Great Income!” If the income is odd, then display “This is an odd income.”