Lake Athletics sells two distinct product lines, Apparel and Equipment, and has provided the Income Statement...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Lake Athletics sells two distinct product lines, Apparel and Equipment, and has provided the Income Statement for each product Iine. The Controller has asked you to perform the vertical analysis of each product line's Income Statement. Use the Information included In the Excel Simulation and the Excel functions described below to complete the task. • Cell Reference: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below, If in a blank cell, "=B9" was entered, the formula would output the result from cell B9, or 3,150,000 in this example. • Absolute Reference: Allows you to maintain the original cell reference when a formula is copied to another cell. The cell reference is "locked" by putting a dollar sign ($) before the column and row references. By default a cell reference is relative, so when you copy a formula to another cell the values update based on a relative reference. For example, if you copy the formula "=B8+B9" from cell C1 to cell C2, the formula in cell C2 will relatively update to be "=B9+B10" since the copied formula moved down one cell, the formula cell references also moved down one cell. Conversely, by adding the absolute cell reference "locks" to the equation, the formula will not change when copied to any other cell. For example, "=$B$8+$B$9" written in cell C1 is copied to cell C2, the formula in cell C2 will remain "=$B$8+$B$9". You can also use a mixed cell reference by only "locking" the column or row only ($B8 or B$8) which locks that specific column or row and the other reference becomes a relative reference and "moves" with the formula as its copled to another cell. For example, If you copy the formula "=B$8+B9" from cell C1 to cell C2, the formula in cell C2 will update to be "=B$8+B10" since the copied formula moved down one cell, the formula cell relative references also moved down one cell, but the absolute "locked" reference remained the same. • Basic Math functions: Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys: + (plus sign to add), - (minus sign to subtract), * (asterisk sign to multiply), and / (forward slash to divlide). From the Excel Simulation below, If in a blank cell "=B9-B10" was entered, the formula would subtract the values from those cells and output the result, or 1,750,000 in this example. If using the other math symbols the result would output an appropriate answer for its function. Perform Vertical Analysis - Excel 困 日 X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In 11 -A A % Calibri Alignment Number Conditional Format as Cell Formatting- Table Styles Paste в I BIU- * A- Cells Editing Clipboard Font Styles A1 fe Lake Athletics sells two distinct product lines: Apparel and Equipment. A B D E F 4 LAKE ATHLETICS 5 Income Statement 6 For the Year Ended December 31, 20X1 Apparel Equipment Amount Percent Amount Percent 9 Net sales 10 Cost of goods sold 11 Gross profit 12 Operating expenses 13 Operating income $3,150,000 1,400,000 1,750,000 800,000 $5,550,000 2,720,000 2,830,000 875,000 1,955,000 10,000 1,965,000 491,000 950,000 20,000 14 Other income 15 Income before tax 16 Income tax expense 970,000 241,250 $728,750 17 Net income $1,474,750 18 19 Required: 20 1. Use appropriate Excel formulas to perform vertical analysis and complete the "Percent" columns 21 for both product lines above. Express each amount as a percentage of net sales. 22 Note: You should use absolute references in the divisors of all formulas. 23 2. Use the Excel IF function and the completed vertical analysis above to answer each of the 24 questions below: 25 26 Which product line has the highest gross profit %? 27 Which product line has the highest net income %? Sheet1 .. READY 用 100% Attempt(s) Hint Lake Athletics sells two distinct product lines, Apparel and Equipment, and has provided the Income Statement for each product Iine. The Controller has asked you to perform the vertical analysis of each product line's Income Statement. Use the Information included In the Excel Simulation and the Excel functions described below to complete the task. • Cell Reference: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below, If in a blank cell, "=B9" was entered, the formula would output the result from cell B9, or 3,150,000 in this example. • Absolute Reference: Allows you to maintain the original cell reference when a formula is copied to another cell. The cell reference is "locked" by putting a dollar sign ($) before the column and row references. By default a cell reference is relative, so when you copy a formula to another cell the values update based on a relative reference. For example, if you copy the formula "=B8+B9" from cell C1 to cell C2, the formula in cell C2 will relatively update to be "=B9+B10" since the copied formula moved down one cell, the formula cell references also moved down one cell. Conversely, by adding the absolute cell reference "locks" to the equation, the formula will not change when copied to any other cell. For example, "=$B$8+$B$9" written in cell C1 is copied to cell C2, the formula in cell C2 will remain "=$B$8+$B$9". You can also use a mixed cell reference by only "locking" the column or row only ($B8 or B$8) which locks that specific column or row and the other reference becomes a relative reference and "moves" with the formula as its copled to another cell. For example, If you copy the formula "=B$8+B9" from cell C1 to cell C2, the formula in cell C2 will update to be "=B$8+B10" since the copied formula moved down one cell, the formula cell relative references also moved down one cell, but the absolute "locked" reference remained the same. • Basic Math functions: Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys: + (plus sign to add), - (minus sign to subtract), * (asterisk sign to multiply), and / (forward slash to divlide). From the Excel Simulation below, If in a blank cell "=B9-B10" was entered, the formula would subtract the values from those cells and output the result, or 1,750,000 in this example. If using the other math symbols the result would output an appropriate answer for its function. Perform Vertical Analysis - Excel 困 日 X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In 11 -A A % Calibri Alignment Number Conditional Format as Cell Formatting- Table Styles Paste в I BIU- * A- Cells Editing Clipboard Font Styles A1 fe Lake Athletics sells two distinct product lines: Apparel and Equipment. A B D E F 4 LAKE ATHLETICS 5 Income Statement 6 For the Year Ended December 31, 20X1 Apparel Equipment Amount Percent Amount Percent 9 Net sales 10 Cost of goods sold 11 Gross profit 12 Operating expenses 13 Operating income $3,150,000 1,400,000 1,750,000 800,000 $5,550,000 2,720,000 2,830,000 875,000 1,955,000 10,000 1,965,000 491,000 950,000 20,000 14 Other income 15 Income before tax 16 Income tax expense 970,000 241,250 $728,750 17 Net income $1,474,750 18 19 Required: 20 1. Use appropriate Excel formulas to perform vertical analysis and complete the "Percent" columns 21 for both product lines above. Express each amount as a percentage of net sales. 22 Note: You should use absolute references in the divisors of all formulas. 23 2. Use the Excel IF function and the completed vertical analysis above to answer each of the 24 questions below: 25 26 Which product line has the highest gross profit %? 27 Which product line has the highest net income %? Sheet1 .. READY 用 100% Attempt(s) Hint
Expert Answer:
Answer rating: 100% (QA)
LAKE ATHLETICS Income Statement For the Year Ended December 3120X1 Apparel Equipment Amount Percent ... View the full answer
Related Book For
Posted Date:
Students also viewed these accounting questions
-
The manager you work for has asked you to perform some research to determine what types of information public companies are providing on their Internet home pages. The public company you work for is...
-
The income statement for Electronic Wonders reports net sales of $91,758 million and cost of goods sold of $69,278 million. An examination of balance sheet amounts indicates accounts receivable...
-
The income statement for Electronic Wonders reports net sales of $127,245 million and cost of goods sold of $97,529 million. An examination of balance sheet amounts indicates accounts receivable...
-
You accepted a new job with starting salary of $52,000 per year. The salary is expected to increase 4% each year. Now it is time to make a retirement plan for the next 39 years you expect to work....
-
Select a market of interest and list different ways of segmenting demand in that market. Which of them can be considered objective, enabling them to be researched and quantified? Is subjective...
-
Do you or your supervisor have to deal with depreciation expense? If so, please describe.
-
The following stream at \(100^{\circ} \mathrm{F}\) and 250 psia is to be separated into the four indicated products. Also, given is the cost of each of the unique separators. Determine: (a) The best...
-
In getting ready to prepare the pro-forma statement of cash flows for May, the accountants at Karls Fish Hatchery have the following information available: Cash balance, April 30 . . . . . . . . . ....
-
Managerial accounting is important for small businesses in several ways. The planning and decision-making support are considered a critical aspect of managerial accounting (Dearman, Lechner, &...
-
Harper Morgan owns White Mountain Assessments in Laconia, New Hampshire. The standard workweek is 40 hours. For the weekly payroll ending September 9, 2022, checks dated September 14, 2022, complete...
-
O GASES Using the ideal equation of state A chemical engineer must calculate the maximum safe operating temperature of a high-pressure gas reaction vessel. The vessel is a stainless-steel cylinder...
-
Berkshire Hathaways A shares are trading at \($120,000.\) What split ratio would it need to bring its the stock price down to \($50?\)
-
How can you identify a real option in a decision tree?
-
Why can staging investment decisions add value?
-
If investors are holding optimal portfolios, how will the portfolios of a more risk averse and a more risk-tolerant investor differ?
-
Explain put-call parity.
-
Farah Snack Co. has earnings after taxes of $150,000. Interest expense for the year was $20,000; preferred dividends paid were $20,000; and common dividends paid were $30,000. Taxes were $22,500. The...
-
A police officer pulls you over and asks to search your vehicle because he suspects you have illegal drugs inside your car. Since he doesn't have reasonable suspicion to search your car, legally he...
-
ABC Corp. issues 10 million shares of common stock and 20,000 shares of 8 percent preferred stock at $50 par. The corporation makes an annual profit of $10 million, and the board of directors...
-
If a company had sales of $2,587,643 in 2008 and sales of $3,213,456 in 2013, by what percentage did sales change during this time period? If the company had a goal of increasing sales by 25% over a...
-
What is the importance of liquidity?
-
In Example 12.1, we introduced the Hong-Ou-Mandel interferometer and presented an analysis of thinking about the photons produced by the laser as classical electromagnetic waves. In this exercise, we...
-
The quaternions are three quantities i, j, and k such that, along with the real number 1, they form the basis of a four-dimensional space over the real numbers. The objects i, j, and k have...
-
We had stated in this chapter that the general Rnyi entropies do not satisfy subadditivity; only the von Neumann entropy does. In Example 12.2 , we constructed an explicit counterexample for the Rnyi...
Study smarter with the SolutionInn App