INTRO MGRL ACCT LL W CONNECT
8th Edition
ISBN: 9781266376771
Author: BREWER
Publisher: MCG
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter IE, Problem 10IE
Financial Statement Ratio Analysis
Refer to the information pertaining to Endless Mountain Company that is provided in Integration Exercise S. In addition to the budget schedules that you prepared in Integration Exercise 8, insert a new tab in your Microsoft Excel worksheet titled "Ratio Analysis."
Required (For all questions, be sure to use formulas that link to the other tabs in your Microsoft Excel worksheet when performing your calculations):
1. To help assess the company's liquidity, calculate the following at December 31, 2017:
a.
b.
2. To help assess the company's asset management, calculate the following for 2017:
a. Accounts receivable turnover.
b. Average collection period.
c.Inventory turnover.
d.Average sale period.
e. Operating cycle.
3. To help assess the company's debt management, calculate the following for 2017:
a. Times interest earned ratio.
b. Equity multiplier.
4. To help assess the company's profitability, calculate the following for 2017:
a.Net profit margin percentage.
b. Return on equity.
5. For each of the measures and ratios that you computed in requirements 1 through 4, indicate whether, generally speaking, management would prefer to see it increase or decrease over time. Support each answer with an explanation.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Your Task…
Using your assigned financial statements calculate the required ratios below
Indicate if the change from year to year is favorable or unfavorable.
All values should be accurate to at least two decimal places.
The expectation is to submit a professional report free of grammar and spelling errors and easy to read. Think of this as a menu you would be handing to a customer.
All calculations are to be represented.
Analysis of Profitability
Gross Profit Ratio
Operating Profit Ratio
Net Profit Ratio
Sales to Total Assets Ratio
Return on Total Assets
Return on Equity
Earnings Per Share
You will be working with the Income Statement and Balance Sheet of Dell Technologies, Inc.
Working with the attached Excel spreadsheet, you are to determine the efficiency ratios and cash conversion cycle for Dell Technologies, Inc. for the years 2021 and 2020.
All the data, as well as a template, are provided to you.
Common-Size Financial Statement Analysis Using Excel
Completing this activity will help you learn to:
1. create Excel formulas using cell references.
2. create absolute cell references to perform calculations efficiently and correctly.
3. apply conditional formatting to highlight cells based on criteria that you specify.
Case scenario: You are working at a small business that has been mostly stable. You want to analyze the financial statements of the past five years to examine the
trends over time and compare the percentages of income statement items relative to overall sales. You also want to highlight cells according to whether the calculated
ratio increases or decreases more than a specified amount.
Required:
Download spreadsheet CommonSizeCaseData-52d586.xlsx
You will build a spreadsheet that performs common-size analysis using five years of a company's income statements. Specifically, you will create two sheets within the
workbook, one for the horizontal analysis and one for the…
Chapter IE Solutions
INTRO MGRL ACCT LL W CONNECT
Ch. IE - INTEGRATION EXERCISE I Different Costs for...Ch. IE - Absorption Costing, Variable Costing, Cost,...Ch. IE - Cash Budget, Income Statement, Balance Sheet,...Ch. IE - Prob. 4IECh. IE - Plantwide and Departmental Overhead Allocation;...Ch. IE - Normal Costing versus Actual Costing Darwin...Ch. IE - Prob. 7IECh. IE - Master Budgeting Endless Mountain Company...Ch. IE - Statement of Cash Flows Refer to the information...Ch. IE - Financial Statement Ratio Analysis Refer to the...
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.Similar questions
- Open the file MASTER from the website for this book at cengagebrain.com. Enter all the formulas where indicated on the worksheet. Check to be sure that your balance sheet balances. Enter your name in cell A1. Save the completed file as MASTER2. Print the worksheet when done. Also print your formulas using fit-to-1 page scaling. Check figures: Borrowing (cell B78), $7,280; forecasted net income (cell D95), $40,200; total assets (cell D105), $507,600.arrow_forwardUse the following information to answer the questions that follow. A. Calculate the operating income percentage for each of the courses. Comment on how your analysis has changed for each course. B. Perform a vertical analysis for each course. Based on your analysis, what accounts would you want to investigate further? How might management utilize this information? C. Which method of analysis (using a dollar value or percentage) is most relevant and/or useful? Explainarrow_forwardFinancial information for Lighthizer Trading Company for the fiscal year-ended September 30, 20xx, was collected. As part of a management training session, you have been asked to prepare an income statement format that will be used to distribute to management. Subtotals and totals are included in the information, but you will need to calculate the values. A. In the correct format, prepare the income statement using this information: B. Calculate the profit margin, return on investment, and residual income. Assume an investment base of $42,000 and 8% cost of capital. C. Prepare a short response to accompany the income statement that explains why uncontrollable costs are included in the income statement.arrow_forward
- Financial information for BDS Enterprises for the year-ended December 31, 20xx, was gathered from an accounting intern, who has asked for your guidance on how to prepare an income statement format that will be distributed to management. Subtotals and totals are included in the information, but you will need to calculate the values. A. In the correct format, prepare the income statement using the following information: B. Calculate the profit margin, return on investment, and residual income. Assume an investment base of $100,000 and 6% cost of capital. C. Prepare a short response to accompany the income statement that explains why uncontrollable costs are included in the income statement.arrow_forwardA company has completed the operating budget and the cash budget. It is now preparing the budgeted balance sheet. Please use the below image as your guide for the next 5 questions. Identify the document that contains equipment purchases A B C D Earrow_forwardSolar Solutions began operations on January 1, 2015, and is now in its sixth year of operations. It is a retail sales company with a large amount of online sales. The adjusted trial balance as of December 31, 2020 appears below, along with prior year balance sheet data and some additional transaction data for 2020. SOLAR SOLUTIONS Adjusted Trial Balance 12/31/2020 2020 2019 Account Title Adjusted Trial Balance Post-Closing Trial Balance Debit Credit Debit Credit Cash $ 122,200 55,000 125,600 Accounts Receivable 35,000 Prepaid Insurance 5,000 6,000 |Inventory Office Equipment Machinery & Tools Accumulated Depreciation Accounts Payable Salaries Payable Sales Tax Payable Note Payable-Long Term Common Stock, $10 par 46,000 15,600 63,000 47,000 59,000 21,000 21,000 16,000 11,200 16,800 2,600 2,700 2,000 4,000 31,000 22,100 240,000 160,000 Retained Earnings 28,600 28,600 Dividends 10,000 Sales Revenue Cost of Goods Sold Rent Expense Salaries Expense 235,000 127,600 20,000 36,000 Insurance…arrow_forward
- A company has completed the operating budget and the cash budget. It is now preparing the budgeted balance sheet. Please use the below image as your guide for the next 5 questions. Identify the document that contains accounts payable A B C D Earrow_forwardPrepare a vertical analysis scheduleHere is the operating data for Yalis Cleaning, Inc.:2021 2020Service revenue $523,000 $476,000Operating expenses 385,500 319,800Income tax expense 34,375 39,050Net income $103,125 $117,150PurposeThe purpose of this problem is to demonstrate an understanding of preparing a vertical analysis schedule.InstructionsAfter analyzing the transactions, prepare a vertical analysis schedule for the company for 2021 and 2020 using service revenueas the base amount. Round percentages to two decimal places.GuidanceFor formatting guidance, see the vertical analysis examples that are provided in Chapter 13 of the textbook.arrow_forwardAnswer Needed for Part B onlySALLAT Household Furnishings & Appliances is a family-owned business. You are themanagement accountant of the entity and have been given the task of preparing the cash budgetfor the business for the quarter ending September 30, 2022. Your data collection has yielded thefollowing:i) Extracts from the sales and purchases budgets are as follows:****Table attachedii) An analysis of the records shows that trade receivables (accounts receivable) for sales onaccount are settled according to the following credit pattern, in accordance with the creditterms 5/30, n90:50% in the month of sale35% in the first month following the sale15% in the second month following the saleiii) Accounts Payable are settled as follows, in accordance with the credit terms – 4/30, n60:70% in the month in which the inventory is purchased30% in the following monthiv) Computer equipment, which is estimated to cost $350,000, will be acquired in August. Themanager has planned with the…arrow_forward
- A company has completed the operating budget and the cash budget. It is now preparing the budgeted balance sheet. Please use the below image as your guide for the next 5 questions. Identify the document that contains cash balance.* A B C D Earrow_forwardHillyard Company, an office supplies specialty store, prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparing the master budget for the first quarter: a. As of December 31 (the end of the prior quarter), the company's general ledger showed the following account balances: Cash Accounts receivable Inventory Buildings and equipment (net) Accounts payable Common stock Retained earnings $ 47,000 205,600 58,800 357,000 $ 87,225 500,000 81,175 $ 668,400 $ 668,400 b. Actual sales for December and budgeted sales for the next four months are as follows: December(actual) January February March $ 257,000 $ 392,000 $ 589,000 $ 303,000 $ 200,000 April c. Sales are 20% for cash and 80% on credit. All payments on credit sales are collected in the month following sale. The accounts receivable at December 31 are a result of December credit sales. d. The company's gross margin is 40% of sales. (In other words, cost of goods sold is 60% of sales.) e.…arrow_forwardApply models of situations related to business administration using linear, polynomial, exponential and logarithmic functions. Correctly use mathematical notation or terminology. Make sure to include correct mathematical procedures and provide clear and complete explanations and interpretations. Remember to express the solution in the context of the situation. In the case that the result is decimal, you will round it to two decimal places. If (P) dollars are invested at the end of each year in an annuity that bears interest at an annual rate (r), the amount in the account will be (A) dollars after (n) years, where: n = log [Ar / P + 1] / log (1 + r) If $ 5,200 is invested each year in an annuity that earns 9% annual interest, when will the account be worth $ 25,000? Round to the nearest tenth.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Principles of Accounting Volume 2AccountingISBN:9781947172609Author:OpenStaxPublisher:OpenStax CollegeCentury 21 Accounting Multicolumn JournalAccountingISBN:9781337679503Author:GilbertsonPublisher:Cengage
Cornerstones of Cost Management (Cornerstones Ser...AccountingISBN:9781305970663Author:Don R. Hansen, Maryanne M. MowenPublisher:Cengage Learning
Excel Applications for Accounting PrinciplesAccountingISBN:9781111581565Author:Gaylord N. SmithPublisher:Cengage Learning
College Accounting, Chapters 1-27AccountingISBN:9781337794756Author:HEINTZ, James A.Publisher:Cengage Learning,
Principles of Accounting Volume 2
Accounting
ISBN:9781947172609
Author:OpenStax
Publisher:OpenStax College
Century 21 Accounting Multicolumn Journal
Accounting
ISBN:9781337679503
Author:Gilbertson
Publisher:Cengage
Cornerstones of Cost Management (Cornerstones Ser...
Accounting
ISBN:9781305970663
Author:Don R. Hansen, Maryanne M. Mowen
Publisher:Cengage Learning
Excel Applications for Accounting Principles
Accounting
ISBN:9781111581565
Author:Gaylord N. Smith
Publisher:Cengage Learning
College Accounting, Chapters 1-27
Accounting
ISBN:9781337794756
Author:HEINTZ, James A.
Publisher:Cengage Learning,
Responsibility Accounting| Responsibility Centers and Segments| US CMA Part 1| US CMA course; Master Budget and Responsibility Accounting-Intro to Managerial Accounting- Su. 2013-Prof. Gershberg; Author: Mera Skill; Rutgers Accounting Web;https://www.youtube.com/watch?v=SYQ4u1BP24g;License: Standard YouTube License, CC-BY