Electronic Spreadsheet: Logical Functions

Updated 2017.02.12

Open the file Stock.xlsx and follow the instructions below. Note that the value TRUE is not the same as the text "True". The value does not have quotation marks around it; the text does. When using logical functions, we always use the logical value TRUE. Note that in all of the following, you only need to use the IF function once in the entire worksheet.

        [1] Set the column width of all columns to 8.00.

        [1] Save it to your account with the name Stock-FirstName-LastName.xlsx using your first and last name.

        [2] P/E Ratio: Divides the price (Closing Price column) by this year's earnings (Earnings This Year column). Display with a single digit to the right of the decimal point.

        [2] P/E Under 10: Evaluates to the value TRUE if the Price/Earnings ratio is less than 10, FALSE otherwise.

        [2] P/E Under 15: Evaluates to the value TRUE if the Price/Earnings ratio is less than 15, FALSE otherwise.

        [2] P/E Over 20: Evaluates to the value TRUE if the Price/Earnings ratio is more than 20, FALSE otherwise.

        [3] 2-year Increase: This determines if the earnings have increased for two consecutive years. Evaluates to the value TRUE if the earnings have increased in both of the last two years (Earnings this Year is greater than Earnings Last Year, and Earnings Last Year is greater than Earnings Two Years Ago), FALSE otherwise.

        [3] 2-year Decrease: This determines if the earnings have decreased for two consecutive years. Evaluates to the value TRUE if the earnings have decreased in both of the last two years (Earnings this Year is less than Earnings Last Year, and Earnings Last Year is less than Earnings Two Years Ago), FALSE otherwise.

        [2] 1-Year Percent Increase: The percent that the stock earnings increased this year. Compute the increase in earnings and divide by last year's earnings. Format as a percent with 0 decimal places.

        [2] 2-Year Percent Increase: The percent that the stock earnings increased over the past two years. Compute the increase in earnings over two years and divide by the earnings from two years ago. Format as a percent with 0 decimal places.

        [2] Up 20% This Year: Evaluates to the value TRUE if the value in the 1-Year Percent Increase column is 20% or more, FALSE otherwise.

        [2] Up 30% in 2 Years: Evaluates to the value TRUE if the value in the 2-Year Percent Increase column is 30% or more, FALSE otherwise.

        [2] Up 30% in 2 Years and P/E Under 15: Evaluates to the value TRUE if the value TRUE is in both the Up 30% in 2 Years and P/E Under 15 column is TRUE and the P/E Under 15 column is TRUE.

 

In the Buy column, put the value TRUE if any of the following are true.

        [2] P/E is under 10 (see the P/E Ratio column).

        [2] Earnings have increased for two years in a row (see the 2-Year Increase column).

        [2] Earnings have increased by 20% this year (see the Up 20% This Year column).

        [3] Earnings have increased by 30% over two years ago and P/E is under 15 (see the Up 30% in 2 Years column and the P/E Under 15 column).

 

The Buy or Sell? Column:

        [4] In the Buy or Sell? Column, put the word "Buy" if the value in the Buy column is TRUE, otherwise, put the word "Sell".

 

In row 1:

        [1] Merge and center all of the cells from A1 to R1.

        [1] Format the text in the Title Cell Style.

 

Column formatting:

        [1] In columns B through E format the cells with the Accounting style.

        [1] Format the cells in column F with 3 decimal places.

        [1] Format the cells in columns G:K and N:R with center alignment.

        [1] Format the numbers in columns L and M with a percent sign and 0 decimal places.

 

Preparation for printing

        [1] Add your name to the right part of the header.

        [1] Set the Page Orientation to landscape.

        [1] Center the document vertically on the page.

        [1] Make sure that all columns will fit on a single page if printed.

 

49 points total