3.4 Assignment: Spreadsheet Exercises

profileusa94
WorkshopThreePracticeProblems.xlsx
Home>Business & Finance homework help>Accounting homework help>3.4 Assignment: Spreadsheet Exercises

Example 1

Workshop Three Practice Exercises
Example 1: Calculating Liquidity Ratios
Hosea, Inc., has Net Working Capital of $198,290, current assets of $463,830, and inventory of $169,750.
a) What is the current ratio?
b) What is the quick (acid test) ratio?
c) If the company's Current Ratio is unusually low, what might this indicate?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 1 Solution

Workshop Three Practice Exercises
Example 1: Calculating Liquidity Ratios
Hosea, Inc., has Net Working Capital of $198,290, current assets of $463,830, and inventory of $169,750.
a) What is the current ratio?
b) What is the quick (acid test) ratio?
c) If the company's Current Ratio is unusually low, what might this indicate?
Check below for a detailed solution to this problem.
Input area: Be careful you complete all parts of all problems! This includes any interpretation questions, like Part C above.
To complete this problem, you have to recall Net Working Capital = Current Assets - Current Liabilities.
Net Working Capital $ 198,290
Current Assets $ 463,830 The Current Ratio is = Current Assets / Current Liabilities, but we aren't given Current Liabilties.
Inventory $ 169,750 However, we can back into it using Net Working Capital and a little algebra:
NWC = CA - CL
CL + NWC = CA
Output area: CL = CA - NWC = $463,830 - $198,290
CL = $265,540
Current Liabilities $ 265,540
Current Ratio 1.75 Current Ratio = CA / CL = $463,830 / $265,540 = 1.75 times
Quick Ratio 1.11 Quick Ratio = (CA - Inventory) / CL = ($463,830 - $169,750) / $265,540 = 1.11 times
Note Inventory is part of Current Assets, but we have to separate it out to calculate the Quick (Acid Test) Ratio
Usually, a high current ratio is preferred. It signifies high liquidity (a position of safety).
If the current ratio is lower than normal, it could signify the company has low liquidity, and could even be in danger of not being able to pay its immediately-due bills.
However, this isn't necessarily true. It could also just mean the company is managing its assets very efficiently.
Current Assets tend to have very low rates of return, so if the company is stable, it may be able to redeploy those assets elsewhere safely.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 2

Workshop Three Practice Exercises
Example 2: Calculating Profitability Ratios
Joel, Inc., has Sales of $9 million, Total Assets of $4.5 million, and Total Debt of $2.3 million. The company's Return on Assets is 11%.
a) What is the company's Net Income?
b) What is the company's Profit Margin?
c) What is the ROE?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 2 Solution

Workshop Three Practice Exercises
Example 2: Calculating Profitability Ratios
Joel, Inc., has Sales of $9 million, Total Assets of $4.5 million, and Total Debt of $2.3 million. The company's Return on Assets is 11%.
a) What is the company's Net Income?
b) What is the company's Profit Margin?
c) What is the ROE?
Check below for a detailed solution to this problem.
Input area:
These types of problems often have little puzzles to solve, designed to test your understanding of the relationships between ratios.
Sales $ 9,000,000 To find Net Income, you need to know the relationship between Net Income and the other information given.
Total Assets $ 4,500,000 The formula for Return on Assets is = Net Income / Total Assets
Total Debt $ 2,300,000 We are given ROA and Total Assets, so with a little algebra can find Net Income
Return on Assets 11% We are given ROA and Total Assets, so with a little algebra can find Net Income
ROA = NI / TA
NI = ROA X TA
Output area: NI = 0.11 X $4,500,000
NI = $495,000
Net Income $ 495,000 We can then use this information to find the Profit Margin…
Profit Margin = Net Income / Sales = $495,000 / $9,000,000
Profit Margin 5.50% Profit Margin = 0.055 or 5.5%
Total Equity $ 2,200,000 The second puzzle is that Equity is not given, and it is needed to calculate ROE. We can find it with the Balance Sheet Equation.
Total Equity = Total Assets - Total Liabilities
Return on Equity 22.50% Total Equity = $4,500,000 - $2,300,000 = $2,200,000
ROE = Net Income / Total Equity = $495,000 / $2,200,000 = 0.2250 or 22.50%
It is worthwhile to note ROE will always be higher than ROA (if the company has even $1 of liabilities in any form)
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 3

Workshop Three Practice Exercises
Example 3: Calculating Receivables Period
Amos, Inc., had Sales of $958,272 last year. At the end of the year, the Accounts Receivable balance was $193,861.
a) How long on average did it take the company to receive payment from its customers (what is the Receivables Period)?
b) What might a large value for this ratio imply?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 3 Solution

Workshop Three Practice Exercises
Example 3: Calculating Receivables Period
Amos, Inc., had Sales of $958,272 last year. At the end of the year, the Accounts Receivable balance was $193,861.
a) How long on average did it take the company to receive payment from its customers (what is the Receivables Period)?
b) What might a large value for this ratio imply?
Check below for a detailed solution to this problem.
Input area:
There are a couple of small mental leaps necessary to perform the calculations here.
Credit Sales $ 958,272 The first is that we don't know credit sales, so we have to use total sales as a surrogate.
Accounts Receivable Balance 193,861 This is discussed in the Higgins textbook (see Table 2.4 on page 68).
The second is that first we have to find the Receivables Turnover in order to calculate Days' Sales in Receivables.
In the Higgins textbook, it suggests first finding the Credit Sales per Day, which accomplishes the same goal.
Output area:
Receivables Turnover 4.94 Receivables Turnover = Sales / Accounts Receivable = $958,272 / $193,861 = 4.94 times
Days' Sales in Receivables 73.84 DSR = 365 / Receivables Turnover = 365 / 4.94 = 73.84 days
The Days Sales in Receivables is the average length of time it is taking the company to collect from its customers.
It is also known as the Receivables Period or Collection Period.
A large ratio indicates it is taking the company a long time to receive cash payments from its customers for its goods and services.
This can put a significant strain on the company, making it difficult for them to maintain sufficient cash flow to pay their suppliers, employees, etc.
It could indicate the receivables are "bad"… That their customers aren't going to pay.
Or it could simply mean the customer negotiated lengthy payment terms with their supplier.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 4

Workshop Three Practice Exercises
Example 4: Dupont Identity
The famous Dupont Identity breaks Return on Equity (ROE) into three components: Profit Margin, Total Asset Turnover, and Financial Leverage (Assets/Equity).
Obadiah Corp. has a Profit Margin of 6%. Their current Total Asset Turnover has increased to 1.95, bringing their ROE up to 14.6%.
a) What is this firm's Debt-Equity ratio?
B) If the company were able to improve its Profit Margin to 7%, what would be their new ROE?
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 4 Solution

Workshop Three Practice Exercises
Example 4: Dupont Identity
The famous Dupont Identity breaks Return on Equity (ROE) into three components: Profit Margin, Total Asset Turnover, and Financial Leverage (Assets/Equity).
Obadiah Corp. has a Profit Margin of 6%. Their current Total Asset Turnover has increased to 1.95, bringing their ROE up to 14.6%.
a) What is this firm's Debt-Equity ratio?
B) If the company were able to improve its Profit Margin to 7%, what would be their new ROE?
Check below for a detailed solution to this problem.
Input area: This is another problem that contains a bit of a puzzle. You have to think carefully about what information you have, and what information you need to get to the answer.
Hopefully the hints are strong enough you can see you need to use the Dupont Identity formula to solve this problem!
This problem gives two of the three variables in the Dupont Identity. It then gives you Return on Equity, which is what the Dupont Identity solves for.
Profit Margin 6.0% We can use that information to find the third variable, the Equity Multiplier.
Total Asset Turnover 1.95
Return on Equity 14.6% Some simple algebra is required to perform this calculation.
Improved Profit Margin 7.0%
Dupont Identity: ROE = Profit Margin X Total Asset Turnover X Equity Multiplier
0.146 = 0.06 X 1.95 X EM
Output area: 0.146 = 0.117 X EM
EM = 0.146 / 0.117 = 1.248
Equity Multiplier 1.248 Once we have the Equity Multiplier (also called the Assets-Equity ratio), it is a simple step from there to the Debt-Equity ratio.
The Equity Multiplier = Assets / Equity, but Assets = (Debt - Equity). With a little math, you discover the Equity Multiplier = Debt-Equity + 1
Debt-Equity 24.79% So we simply subtract 1 from the Equity Multiplier to find the Debt-Equity ratio.
Improved ROE 17.03% To find the new ROE, we then just need to use the Dupont Identity with the imporved Profit Margin, the given Total Asset Turnover, and the calculated Equity Multiplier.
ROE = 0.07 X 1.95 X 1.248 = 0.1703 or 17.03%
Note how a 1% increase in Profit Margin results in a 2.5% increase in ROE! This is the result of the magnifying effect of financial leverage (debt).
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 5

Workshop Three Practice Exercises
Example 5: Financial Statement Analysis
Use the financial information provided below to perform both a vertical and horizontal analysis of Jonah Corp.
Item 2018 2019
Cash and Securities $ 5,000 $ 6,500
Accounts Receivable $ 23,000 $ 29,500
Supplies $ 3,500 $ 4,300
Accounts Payable $ 7,300 $ 8,400
Unearned Revenue $ 8,500 $ 11,500
Equipment $ 35,000 $ 58,000
Intangible Long-Term Assets $ 70,000 $ 60,000
Bank Loan (Long-Term Debt) $ 85,000 $ 78,000
Common Stock $ 30,000 $ 30,000
Retained Earnings $ 5,700 $ 30,400
Sales $ 350,000 $ 480,000
Cost of Goods Sold $ 125,000 $ 170,000
Direct Labor $ 85,000 $ 115,000
Depreciation and Amortization Expense $ 13,500 $ 15,000
Management Salaries $ 85,000 $ 110,000
Interest on Bank Loan $ 9,600 $ 8,900
Income Tax Rate (effective) 21% 21%
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)
Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution.
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 5 Solution

Workshop Three Practice Exercises
Example 5: Financial Statement Analysis
Use the financial information provided below to perform both a vertical and horizontal analysis of Jonah Corp.
Item 2018 2019
Cash and Securities $ 5,000 $ 6,500
Accounts Receivable $ 23,000 $ 29,500
Supplies $ 3,500 $ 4,300
Accounts Payable $ 7,300 $ 8,400
Unearned Revenue $ 8,500 $ 11,500
Equipment $ 35,000 $ 58,000
Intangible Long-Term Assets $ 70,000 $ 60,000
Bank Loan (Long-Term Debt) $ 85,000 $ 78,000
Common Stock $ 30,000 $ 30,000
Retained Earnings $ 5,700 $ 30,400
Sales $ 350,000 $ 480,000
Cost of Goods Sold $ 125,000 $ 170,000
Direct Labor $ 85,000 $ 115,000
Depreciation and Amortization Expense $ 13,500 $ 15,000
Management Salaries $ 85,000 $ 110,000
Interest on Bank Loan $ 9,600 $ 8,900
Income Tax Rate (effective) 21% 21%
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)
Check below for a detailed solution to this problem.
Input / Ouput area: This is a complex, multi-part problem. Take it systematically, step by step.
First build the Balance Sheets and Income Statements, using the data given, just like in Workshop One.
Jonah Corporation Then you can create the Common-Sized statements.
Comparitive Year-End Balance Sheets Remember a common-sized Balance Sheet is created by dividing everything by Total Assets.
December 31st A common-sized Income Statement is created by dividing everything by Sales.
Lastly, use 2018 as a base year to find the percentage changes in each line item (the horizontal analysis).
2018 Common Size 2019 Common Size Percentage Change Try to make your statements as clean and clear as possible!
Current Assets
Cash and Securities $ 5,000 3.7% $ 6,500 4.1% 30.0% Note what you are looking at here is typical financials for a small start-up company. Cash is very low (perhaps dangerously so).
Accounts Receivable 23,000 16.8% 29,500 18.6% 28.3% Accounts Receivable is much larger than Accounts Payable.
Supplies 3,500 2.6% 4,300 2.7% 22.9% This is typical for a small company with large customers and powerful suppliers.
Total $ 31,500 23.1% $ 40,300 25.5% 27.9% However, it can lead to major cash flow problems.
Long-Term Assets
Intangible Assets $ 70,000 51.3% $ 60,000 37.9% -14.3% Intangible assets like customer lists or non-compete agreements with the prior owners are common with small businesses.
Equipment $ 35,000 25.6% $ 58,000 36.6% 65.7% As the company grows and is succesful, they purchase new equipment and their intangible assets decline in value over time.
Total $ 105,000 76.9% $ 118,000 74.5% 12.4% Note intangible assets are "amortized" (tangible assets are "depreciated").
Total Assets $ 136,500 100.0% $ 158,300 100.0% 16.0% Total Assets grew significantly, but most of the growth came from equipment purchases.
Current Liabilities
Unearned Revenue $ 8,500 6.2% $ 11,500 7.3% 35.3%
Accounts Payable $ 7,300 5.3% $ 8,400 5.3% 15.1%
Total $ 15,800 11.6% $ 19,900 12.6% 25.9% Short-term liabilities normally grow with increased Sales.
Bank Loan $ 85,000 62.3% $ 78,000 49.3% -8.2% The bank loan (probably a mortgage on the owner's home) is slowly being paid off.
Owners' Equity
Common Stock $ 30,000 22.0% $ 30,000 19.0% 0.0% The company was started with a small equity investment from the owner.
Retained Earnings 5,700 4.2% 30,400 19.2% 433.3% Almost all of the profits are being plowed back into the business, which is typical for a small, growing company.
Total $ 35,700 26.2% $ 60,400 38.2% 69.2% The company wouldn't pay dividends (which would be taxed), but instead simply give the owner / operator a raise!
Total Liabilities and Owners' Equity $ 136,500 100.0% $ 158,300 100.0% 16.0%
Jonah Corporation
Comparitive Year-End Income Statements
December 31st
2018 Common Size 2019 Common Size Percentage Change
Net Sales $ 350,000 100.0% $ 480,000 100.0% 37.1% Here we see the significant sales growth
Cost of Goods Sold $ 125,000 35.7% $ 170,000 48.6% 36.0% Sometimes Cost of Goods Sold only includes materials, and sometimes it also includes Direct Labor.
Direct Labor $ 85,000 24.3% $ 115,000 24.0% 35.3% Sometimes Direct Labor is counted as part of Cost of Goods Sold… It is the labor that was necessary to produce the company's goods and services.
Gross Profit $ 140,000 40.0% $ 195,000 40.6% 39.3% Gross Profit grew a little faster than Sales. This is a good sign… It indicates good cost control.
Depreciation and Amortization Expense 13,500 3.9% 15,000 4.3% 11.1%
Management Salaries 85,000 24.3% 110,000 22.9% 29.4% Here we see the significant pay raise the owner / operator gave himself.
Operating Income (EBIT) $ 41,500 11.9% $ 70,000 14.6% 68.7%
Interest Expense 9,600 2.7% 8,900 1.9% -7.3%
Taxable Income $ 31,900 9.1% $ 61,100 12.7% 91.5%
Income Tax Expense (21%) $ 6,699 1.9% $ 12,831 2.7% 91.5%
Net Income $ 25,201 7.2% $ 48,269 10.1% 91.5% Very nice!
Be sure you answer all parts of the problem, especially when it asks for interpretation!
This is the student Practice Problem file, provided in the assignment instructions October 2019