small business excel sheet exercise

ESLSCA – MIBA Essentials of Entrepreneurship and Small Business Management

Fall 2018

Case 6 Chapters 10 & 12 James Confectioners Squeezed by rising costs,

a confectioner struggles to cope ————————————————————————————

Questions and Responses 1. Calculate the twelve ratios for James Confectioners for this year.

RATIO James Percent Confectioners Confectionary Variation Current Last Industry from Year Year Mean* Ind. Mean

Liquidity Ratios Current Ratio 2.01 1.86 1.7 18.0% Quick Ratio 1.16 1.07 0.8 45.5%

Leverage Ratios Debt Ratio 0.62 0.64 0.7 -11.5% Debt-to-Net Worth Ratio 1.60 1.71 1.7 -6.0% Times Interest Earned Ratio 2.38 2.49 2.3 3.5%

Operating Ratios Average Inventory Turnover Ratio 4.39 4.75 4.9 -10.4% Average Collection Period Ratio 47.6 34.6 23.0 days 107.2% Average Payable Period Ratio 34.4 31.1 33.5 days 2.8% Net Sales to Total Assets Ratio 1.93 2.17 2.1 -7.9%

Profitability Ratios Net Profit on Sales Ratio 4.24% 7.40% 7.0% -39.4% Net Profit to Assets Ratio 8.20% 9.20% 5.6% 46.4% Net Profit to Equity Ratio 21.29% 29.21% 16.5% 29.0%

* From Risk Management Association Annual Statement Studies 2. How do the ratios you calculated for this year compare to those Ivey

calculated for the company last year? What factors are most likely to account for those changes?

RATIO James Confectioners Current Last Percent Year Year Variation

Liquidity Ratios Current Ratio 2.01 1.86 8.1%

Dr. Abdel Moneim Hafez Page 1 This study source was downloaded by 100000779904269 from CourseHero.com on 09-19-2021 13:26:20 GMT -05:00

https://www.coursehero.com/file/42255134/James-Confectioners-Case-Answerdocx/

Th is

stu dy

re so

ur ce

w as

sh ar

ed v

ia C

ou rs

eH er

o. co

m

ESLSCA – MIBA Essentials of Entrepreneurship and Small Business Management

Fall 2018

Quick Ratio 1.16 1.07 8.4%

Leverage Ratios Debt Ratio 0.62 0.64 -3.1% Debt-to-Net Worth Ratio 1.60 1.71 -6.4% Times Interest Earned Ratio 2.38 2.49 -4.4%

Operating Ratios Average Inventory Turnover Ratio 4.39 4.75 -7.8% Average Collection Period Ratio 47.6 34.6 37.6% Average Payable Period Ratio 34.4 31.1 10.6% Net Sales to Total Assets Ratio 1.93 2.17 -11.1%

Profitability Ratios Net Profit on Sales Ratio 4.24% 7.40% -42.7% Net Profit to Assets Ratio 8.20% 9.20% -10.9% Net Profit to Equity Ratio 21.29% 29.21% -27.1%

The ratio comparisons reveal the following: Liquidity  The Current Ratio is above last year by 8.1%.  The Quick Ratio has improved from last year by 8.4%. Leverage Ratios  The Debt Ratio is slightly lower than last year by -3.1%.  The Debt-to-Net Worth Ratio is less than last year by -6.4%.  The Times Interest Earned Ratio is slightly less than last year by

-4.4%. Operating Ratios  The Average Inventory Turnover Ratio is less than last year by -7.8%.  The Average Collection Period Ratio is up from 34.6 last year to 47.6,

an increase of 37.6%. This is a significant concern.  The Average Payable Period Ratio is up from last year by 10.6%.  Net Sales to Total Assets Ratio is down from last year by -11.1%. Profitability Ratios  Net Profit on Sales Ratio is down from last year by -42.7%.  Net Profit to Assets Ratio is slightly down from last year by -10.9%.  Net Profit to Equity Ratio is down from last year -27.1%.

Downward trends from last year’s performance exist in the areas of leverage, operations, and profitability. The increase in the cost of goods is one of the primary reasons. Another significant concern is an increase in the collection period. This may be a sign of future difficulties with customers paying late.

Dr. Abdel Moneim Hafez Page 2 This study source was downloaded by 100000779904269 from CourseHero.com on 09-19-2021 13:26:20 GMT -05:00

https://www.coursehero.com/file/42255134/James-Confectioners-Case-Answerdocx/

Th is

stu dy

re so

ur ce

w as

sh ar

ed v

ia C

ou rs

eH er

o. co

m

ESLSCA – MIBA Essentials of Entrepreneurship and Small Business Management

Fall 2018

Liquidity, however, shows a positive trend and continues to be relatively strong.

3. How do the ratios you calculated for this year compare to those of the typical company in the industry. Do you spot any areas that could cause the company problems in the future? Explain. The ratio comparison reveals:

Liquidity  The Current Ratio is above the industry mean by 18.0%.  The Quick Ratio is 45% greater than the industry mean Leverage Ratios  The Debt Ratio is below the industry mean by 11.5%.  The Debt-to-Net Worth Ratio is below the industry mean by 6.0%.  The Times Interest Earned Ratio is slightly less than last year and

above the industry mean by 3.5%. Operating Ratios  The Average Inventory Turnover Ratio is 10.4% below the industry

mean.  The Average Collection Period Ratio is more than twice the industry

mean.  The Average Payable Period Ratio is up slightly from last year and

2.8% above the industry mean.  Net Sales to Total Assets Ratio is 8.1% below the industry mean. Profitability Ratios  Net Profit on Sales Ratio -39.4% of the industry mean.  Net Profit to Assets Ratio is above the industry mean by 46.4%.  Net Profit to Equity Ratio above the industry mean by 29.0%.

In summary the year-to-year trends and comparisons to industry ratios reveal the following:

Liquidity: Strong with an increasing trend Leverage: Low due to disproportionate debt – a potential concern

for the future Operations: The Average Collection Period Ratio is a significant

concern. Inventory turnover is declining and worth watching. Profitability: Profitability is weak compared to industry sales figures,

but relatively strong in relation to assets and equity.

Problem areas appear in operations, specifically the Average Collection Period Ratio which could erode future profitability. The company shows some positive ratios in comparison to the industry mean figures.

Dr. Abdel Moneim Hafez Page 3 This study source was downloaded by 100000779904269 from CourseHero.com on 09-19-2021 13:26:20 GMT -05:00

https://www.coursehero.com/file/42255134/James-Confectioners-Case-Answerdocx/

Th is

stu dy

re so

ur ce

w as

sh ar

ed v

ia C

ou rs

eH er

o. co

m

ESLSCA – MIBA Essentials of Entrepreneurship and Small Business Management

Fall 2018

4. Develop a set of recommendations for improving the financial performance of James Confectioners using the analysis you conducted in questions 1-3. Student recommendations may include a selection from the following:

 Increase prices to account for the increase in cost of goods.  Attempt to lock in long term pricing on cocoa in an effort to stabilize

future prices.  Review operations to look for opportunities to reduce cost of goods

sold for non-cocoa products.  Increase attention on collections to reduce collection days.

5. What pricing recommendations can you make to Telford and Ivey James? In order to keep profitability strong, James Confectioners will have to increase prices. Ideally, the implementation of this price increase may not impact sales negatively. Communication is essential. Conveying these price increases to customers in advance with a sound rationale behind them may reduce potential negative ramifications of this price increase.

Dr. Abdel Moneim Hafez Page 4 This study source was downloaded by 100000779904269 from CourseHero.com on 09-19-2021 13:26:20 GMT -05:00

https://www.coursehero.com/file/42255134/James-Confectioners-Case-Answerdocx/

Th is

stu dy

re so

ur ce

w as

sh ar

ed v

ia C

ou rs

eH er

o. co

m

Powered by TCPDF (www.tcpdf.org)