Auburn Career Center Income Statement for Lemonade Co Worksheet

User Generated

oq1102

Business Finance

Auburn Career Center Excel

Description

Simple excel homework using vlookup/hlookup and match. There are 3 sheets in the workbook that you must complete.

Unformatted Attachment Preview

Question 1 Input: Input your age: Input your younger sibling's age: Question Can you vote for president? For how many years have you been eligible to vote? Are both me and my younger sibling eligible to vote? 25.0 17.0 Answer IF Formula Input: Input expected company revenue: Input actual company revenue: 300.0 400.0 Input expected company EBITDA: Input actual company EBITDA: 50.0 60.0 Question Did the company exceed revenue expectations? Did the company exceed both revenue and EBITDA expectations? IFERROR - is "NM" Net earnings Shares outstanding Earnings per share (EPS) Answer 2014 2015 $1,500.0 $1,850.0 0.0 50.0 Combine IF and AND functions to answer this question: Criteria: If on the job 5 years or more AND received an 8 or higher for a performance rating, output a salary reflecting a 5% raise, otherwise, output "No raise" Employee Age Salary Years at Company Performance New Rating salary A 25.0 $53,140.0 0.0 10.0 B 48.0 $110,662.0 11.0 2.0 C 48.0 $117,294.0 17.0 8.0 D 34.0 $114,373.0 19.0 10.0 Combine IF and OR functions to answer this question: Criteria: If on the job 5 years or more OR received an 8 or higher for a performance rating, output a salary reflecting a 5% raise, otherwise, output "No raise" Employee Age Salary Years at Company Performance New Rating salary A 25.0 $53,140.0 0.0 10.0 B 48.0 $110,662.0 11.0 2.0 C 48.0 $117,294.0 17.0 8.0 D 34.0 $114,373.0 19.0 10.0 Input Company Name: Tax rate Income Statement for Lemonade Co. Revenues Cost of Goods Sold Gross Profit Operating costs Pretax Profit Taxes Net Profit Basic Shares Outstanding Basic EPS Exit year Select a metric: Lemonade Co. 35.0% 12/31/14 12/31/15 12/31/16 12/31/17 12/31/18 800.0 1,000.0 1,500.0 2,500.0 4,500.0 300.0 500.0 1,200.0 3,400.0 4,000.0 500.0 500.0 300.0 (900.0) 500.0 35.0 45.0 55.0 65.0 78.0 465.0 455.0 245.0 (965.0) 422.0 0.0 0.0 0.0 0.0 0.0 465.0 455.0 245.0 (965.0) 422.0 100.0 110.0 115.0 120.0 125.0 $4.65 $4.14 $2.13 ($8.04) $3.38 12/31/2016 Gross Profit Solution: Combining formulas to make more durable formulas without hardcoding Combination formulas: Accomplish using: HLOOKUP w/ MATCH VLOOKUP w/MATCH OFFSET w/ MATCH INDEX w/ MATCH CHOOSE w/ MATCH MATCH - the third argument - match type 0 = Exact match. You'll almost always input 0 for an exact match. 1 or omitted = Excel will find the largest value that is less than or equal to the lookup value, as long as data is in ascending order -1 = Excel will find the smallest value that is greater than or equal to the lookup value as long as data is in descending order HLOOKUP / VLOOKUP - the fourth argument - range lookup TRUE (1 or omitted): If an exact match is not found, the next largest value that is less than lookup_value is returned. FALSE (0): Only an exact match will be returned. If no exact match, will return #N/A Important: For text lookups, an exact match (0) is required 2014 2015 Output: 2016 #REF! 2017 2018
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Question 1
Input:
Input your age:
Input your younger sibling's age:
Question
Can you vote for president?
For how many years have you been eligible to vote?
Are both me and my younger sibling eligible to vote?

25,0
17,0
Answer
Yes
7,0
No

IF Formula
Input:
Input expected company revenue:
Input actual company revenue:

300,0
400,0

Input expected company EBITDA:
Input actual company EBITDA:

50,0
60,0

Question
Did the company exceed revenue expectations?
Did the company exceed both revenue and EBITDA expectations?

IFERROR - is "NM"
Net earnings
Shares outstanding
Earnings per share (EPS)

Answer
Ye...


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags