3.4 Assignment: Spreadsheet Exercises

usa94
Assignment3-4Worksheet.xlsx
Home>Business & Finance homework help>Accounting homework help>3.4 Assignment: Spreadsheet Exercises

#1

Assignment 3.4 Exercises
Problem 1: Calculating Liquidity Ratios 5 Points
Flugel, Inc., has Net Working Capital of $8,920, current liabilities of $11,380, and inventory of $16,750.
a) What is the current ratio?
b) What is the quick (acid test) ratio?
c) If the company's Current Ratio is unusually high, what might this indicate?
Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs.
Input area:
Net Working Capital
Current Liabilities
Inventory
Output area:
Current Assets $ - 0
Current Ratio ERROR:#DIV/0!
Quick Ratio ERROR:#DIV/0!
Intepretation: What might an unusually high Current Ratio indicate?
(briefly explain here)
This is the Student Template, provided in the assignment instructions October 2019

#2

Assignment 3.4 Exercises
Problem 2: Calculating Profitability Ratios 5 Points
Sousa, Inc., has Sales of $37.3 million, Total Assets of $26.5 million, and Total Debt of $11.3 million. The company's Profit Margin is 6 percent.
a) What is the company's Net Income?
b) What is the ROA?
c) What is the ROE?
Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers.
This is the Student Template, provided in the assignment instructions October 2019

#3

Assignment 3.4 Exercises
Problem 3: Calculating Inventory Turnover 5 Points
The Piccolo Corporation has ending inventory of $3,720,180. Material costs for the year just ended were $4,573,820.
a) What is the inventory turnover?
b) What is the days' sales in inventory?
c) If the company's Inventory Turnover is unusually low, what might this indicate?
Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs.
Input area:
Ending Inventory
Cost of Goods sold
Output area:
Inventory Turnover ERROR:#DIV/0!
Days' Sales in Inventory ERROR:#DIV/0!
Intepretation: What might an unusually low Inventory Turnover indicate?
(briefly explain here)
This is the Student Template, provided in the assignment instructions October 2019

#4

Assignment 3.4 Exercises
Problem 4: Dupont Identity 5 Points
The famous Dupont Identity breaks Return on Equity (ROE) into three components: Profit Margin, Total Asset Turnover, and Financial Leverage (Assets/Equity).
French Corp. has an Asset/Equity ratio of 1.55. Their current Total Asset Turnover has recently fallen to 1.20, bringing their ROE down to 9.1%.
a) What is this firm's Profit Margin?
B) If the company were able to improve its Total Asset Turnover to 1.8, what would be their new ROE?
Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers.
This is the Student Template, provided in the assignment instructions October 2019

#5

Assignment 3.4 Exercises
Problem 5: Calculating Market Value Ratios 5 Points
Euphonium Corp. had additions to retained earnings for the year just ended of $595,000. The firm paid out $395,000 in cash dividends, and it had ending total equity of $18.3 million. The company has 370,000 shares of common stock outstanding, and the stock sells for $47 per share.
a) What are earning per share?
b) What are dividends per share?
c) What is the book value per share?
d) What is the price-earnings ratio?
e) Based on this data, would you consider purchasing this stock? Why or why not? Is it a good investment?
Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs.
Input area:
Addition to retained earnings
Cash dividends
Total equity
Common shares outstanding
Share price
Output area:
Net Income $ - 0
Earnings per Share ERROR:#DIV/0!
Dividends per Share ERROR:#DIV/0!
Book Value per Share ERROR:#DIV/0!
P/E ratio ERROR:#DIV/0!
Intepretation: Given this data, is this stock a good investment? Why or why not?
(briefly explain here)
This is the Student Template, provided in the assignment instructions October 2019

#6

Assignment 3.4 Exercises
Problem 6: Calculating Average Payables Period 5 Points
Saxhorn, Inc., had a Cost of Goods Sold of $138,572 last year. At the end of the year, the Accounts Payable balance was $32,681.
a) How long on average did it take the company to pay its suppliers (what is the Payables Period)?
b) What might a large value for this ratio imply?
Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers.
This is the Student Template, provided in the assignment instructions October 2019

#7

Assignment 3.4 Exercises
Problem 7: Trend Analysis 5 Points
Financial information is provided below for Saxabut Inc. Prepare the 2018 and 2019 balance sheets, and then use 2018 as the base-year to create a horizontal analysis.
Account Category 2018 2019
Cash $ 11,135 $ 13,407
Accounts Receivable $ 28,419 $ 30,915
Inventory $ 51,163 $ 56,295
Accounts Payable $ 45,166 $ 48,185
Notes Payable $ 17,773 $ 18,257
Net Plant and Equipment $ 326,456 $ 357,560
Long-Term Debt $ 44,000 $ 39,000
Common Stock and Paid-in Surplus $ 50,000 $ 50,000
Retained Earnings $ 260,234 $ 302,735
Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs.
Input / Ouput area:
Saxabut, Inc.
Balance Sheet
December 31st
Percentage Change Percentage Change
2018 2019 2018 2019
Current Assets Current Liabilities
Cash ERROR:#DIV/0! Accounts Payable ERROR:#DIV/0!
Accounts Receivable ERROR:#DIV/0! Notes Payable ERROR:#DIV/0!
Inventory ERROR:#DIV/0! Total $ - $ - ERROR:#DIV/0!
Total $ - $ - ERROR:#DIV/0!
Long-Term Debt ERROR:#DIV/0!
Owners' Equity
Common Stock and Paid-In Surplus ERROR:#DIV/0!
Retained Earnings ERROR:#DIV/0!
Net Plant and Equipment ERROR:#DIV/0! Total $ - $ - ERROR:#DIV/0!
Total Assets $ - $ - ERROR:#DIV/0! Total Liabilities and Owners' Equity $ - $ - ERROR:#DIV/0!
This is the Student Template, provided in the assignment instructions October 2019

#8

Assignment 3.4 Exercises
Problem 8: Common-Size Financial Statements 5 Points
Use the financial information provided below to perform a vertical analysis on Mello Inc. Prepare the 2019 common-size balance sheet and income statement.
Item 2019
Cash $ 110,000
Accounts Receivable $ 30,000
Inventory $ 40,000
Short-Term Investments $ 20,000
Accounts Payable $ 75,000
Unearned Revenue $ 25,000
Net Plant and Equipment $ 50,000
Long-Term Debt $ 50,000
Common Stock $ 80,000
Retained Earnings $ 20,000
Net Sales $ 120,000
Cost of Goods Sold $ 60,000
Operating Expenses $ 5,500
Depreciation Expense $ 3,600
Salaries Expense $ 5,400
Utilities Expense $ 2,500
Interest $ 2,000
Income Tax $ 6,000
Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs.
Input / Ouput area:
Mello Inc.
Comparitive Year-End Balance Sheet
December 31st
2019 Common Size 2019 Common Size
Current Assets Current Liabilities
Cash ERROR:#DIV/0! Accounts Payable ERROR:#DIV/0!
Accounts Receivable ERROR:#DIV/0! Unearned Revenue ERROR:#DIV/0!
Inventory ERROR:#DIV/0! Total $ - ERROR:#DIV/0!
Short-Term Investments ERROR:#DIV/0!
Total $ - ERROR:#DIV/0! Long-Term Debt ERROR:#DIV/0!
Owners' Equity
Common Stock ERROR:#DIV/0!
Retained Earnings ERROR:#DIV/0!
Total $ - ERROR:#DIV/0!
Net Plant and Equipment ERROR:#DIV/0!
Total Assets $ - ERROR:#DIV/0! Total Liabilities and Owners' Equity $ - ERROR:#DIV/0!
Mello Inc.
Comparitive Year-End Income Statement
December 31st
2019 Common Size
Net Sales ERROR:#DIV/0!
Cost of Goods Sold ERROR:#DIV/0!
Gross Profit $ - ERROR:#DIV/0!
Operating Expenses ERROR:#DIV/0!
Depreciation Expense ERROR:#DIV/0!
Salaries Expense ERROR:#DIV/0!
Utilities Expense ERROR:#DIV/0!
Operating Income (EBIT) $ - ERROR:#DIV/0!
Interest Expense ERROR:#DIV/0!
Taxable Income $ - ERROR:#DIV/0!
Income Tax Expense ERROR:#DIV/0!
Net Income $ - ERROR:#DIV/0!
This is the Student Template, provided in the assignment instructions October 2019

#9

Assignment 3.4 Exercises
Problem 9: Financial Statement Analysis 10 Points
Use the financial information provided below to perform both a vertical and horizontal analysis of French Corp.
Item 2018 2019
Cash $ 150,000 $ 85,000
Accounts Receivable $ 580,000 $ 630,000
Inventory $ 360,000 $ 430,000
Accounts Payable $ 410,000 $ 625,000
Net Plant and Equipment $ 1,250,000 $ 1,490,000
Long-Term Debt $ 1,470,000 $ 1,530,000
Common Stock and Paid-in Surplus $ 400,000 $ 400,000
Retained Earnings $ 60,000 $ 80,000
Net Sales $ 2,150,000 $ 2,550,000
Cost of Goods Sold $ 1,350,000 $ 1,590,000
Operating Expenses $ 125,000 $ 165,000
Depreciation Expense $ 240,000 $ 250,000
Management Salaries $ 220,000 $ 330,000
Interest $ 140,000 $ 165,000
Income Tax $ 15,500 $ 13,500
a) Prepare the 2018 and 2019 common-sized Balance Sheets.
b) Prepare the 2018 and 2019 common-sized Income Statements.
c) Using 2018 as the base year, perform a horizontal analysis on the Balance Sheets.
d) Using 2018 as the base year, perform a horizontal analysis on the Income Statements.
E) What do you observe? Are there any interesting trends or changes? (answer with a few sentences)
Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers.
This is the Student Template, provided in the assignment instructions October 2019