4

usa94
Assignment4-4Worksheet.xlsx
Home>Business & Finance homework help>Accounting homework help>4

#1

Assignment 4.4 Exercises
Problem 1: Creating Proforma Financial Statements 5 Points
A pro forma Income Statement for Monroe Products Co. is displayed below:
Monroe Products Company .
Income Statements, 2019 and Pro Forma 2020
Years Ended December 31st
2019 Actual 2020 Forecast
Net Sales $ 1,000,000 $ 1,250,000
Cost of Goods Sold 800,000 1,000,000
Gross Profit $ 200,000 $ 250,000
Operating Expenses 100,000 125,000
Depreciation Expense 50,000 62,500
Operating Income (EBIT) $ 50,000 $ 62,500
Interest Expense 10,000 12,500
Taxable Income $ 40,000 $ 50,000
Income Tax Expense 15,000 18,750
Net Income $ 25,000 $ 31,250
Use the information given to construct the Pro Forma Balance Sheet for 2020.
a) Sales growth is projected to be 25% in 2020, and Current Assets is expected to grow with Sales. What should Current Assets be in 2020?
b) Monroe Products plans to purchase $100,000 of fixed assets in 2020, but will not dispose of any existing fixed assets. What should be its forecast for ending Net Fixed Assets in 2020?
c) If Monroe Products pays out 40% of its Net Income in 2020 (payout ratio) and will neither sell nor repurchase equity during the year, what should its forecast for owner's equity be at the end of 2020?
d) Given all the above information, What is Monroe Products' projected external funding required for 2020?
Use the Template Provided Below to Create Your Solution - Be sure to show or explain your work to get full credit!
Input / Output area:
Monroe Products Company
Balance Sheets, 2019 and Pro Forma 2020
December 31st
2019 2020 Forecast 2019 Common Size
Current Assets $ 500,000 Current Liabilities $ 200,000 $ 250,000
Long-Term Debt $ 100,000 $ 125,000
Net Fixed Assets $ 400,000 Owner's Equity $ 600,000
Total Assets $ 900,000 $ - Total Liabilities and Owners' Equity $ 900,000 $ 375,000
External Financing Needed: $ (375,000)
This is the Student Template, provided in the assignment instructions October 2019

#2

Assignment 4.4 Exercises
Problem 2: Creating Proforma Financial Statements 10 Points
The 2019 Income Statement and Balance Sheet are given below for Van Buren Enterprises. Using the assumptions provided, create the 2020 Pro Forma Income Statement and Balance Sheet. What is the External Funding Required?
Van Buren Enterprises . Van Buren Enterprises
Income Statement ($ thousands) Balance Sheets
Year Ended December 31st, 2019 Year Ended December 31st, 2019 ($ thousands)
2019 Actual
Net Sales $ 9,870 2019 Actual 2019 Actual
Cost of Goods Sold 8,093 Current Assets Current Liabilities
Gross Profit $ 1,777 Cash and Securities $ 540 Accounts Payable $ 843
Accounts Receivable 1,485 Accured Wages 48
Operating Expenses 986 Inventory 810 Total $ 891
Depreciation Expense 182 Total $ 2,835
Operating Income (EBIT) $ 609 Long-Term Debt $ 1,600
Interest Expense 83 Net Fixed Assets $ 850 Owner's Equity
Taxable Income $ 526 Common Stock $ 200
Retained Earnings 994
Income Tax Expense 110 Total $ 1,194
Net Income $ 416 Total Assets $ 3,685 Total Liabilities and Owners' Equity $ 3,685
Van Buren Enterprises Assumptions for 2020
Growth rate in Net Sales 30% Long-Term Debt ($ estimate) $ 1,400
Cost of Goods Sold (% of Sales) 82% Cash/Securities (Days of Sales) 20
Operating Expenses (% of Sales) 10% Accounts Receivable (% of Sales) 15%
Depreciation Expense (estimate $) $ 192 Inventory Turnover 10
Interest Expense (Total estimate $) $ 159 Accounts Payable Period (Days) 38
Tax Rate 21% Accrued Wages ($ estimate) $ 63
Dividend Payout Ratio 50% Net Fixed Assets ($ estimate) $ 950
Assume the Interest Expense estimate includes the cost of any new debt required, and Common Stock will not change. Create the 2020 Pro Forma Balance Sheet and Income Statements. What is the External Funding Required?
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 4.4 Exercises
Problem 3: Preparing a Cash Budget 5 Points
Fillmore Lawn Mowers Company's estimated sales and purchases for the past three months, plus projected sales and purchases for thenext three months, are shown below:
Actual Forecast
April May June July August September
Sales $ 493,200 $ 575,400 $ 1,644,000 $ - $ 822,000 $ 328,800 $ 328,800
Purchases $ 698,700 $ 739,800 $ 1,644,000 $ - $ 411,000 $ 164,400 $ 164,400
Only 20% of Filmore's sales are for cash. The company's Average Collection Period from customers is 90 days. It's standard pay terms with suppliers is 45 days.
a) What are the company's expected cash receipts in September?
b) What would the company's Accounts Receivable balance be at the end of September?
c) What are the company's expected cash expenditures for purchases in September?
d) What would be the company's Accounts Payable balance at the end of September?
Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs.
Input area:
Cash Flows Actual Forecast
April May June July August September
Sales ($)
Purchases ($)
Percent Sales in Cash (%)
Collection Period (Days)
Payables Period (Days)
Output area:
Actual Forecast
April May June July August September
Cash Sales $ - $ - $ - $ - $ - $ -
Credit Sales $ - $ - $ - $ - $ - $ -
Receipts:
Cash from Current Month Sales $ - $ - $ - $ - $ - $ -
Cash from Prior Months Sales $ - $ - $ -
Total Cash Receipts $ - $ - $ -
Accounts Receivable Balance $ - $ - $ - $ -
Credit Purchases $ - $ - $ - $ - $ - $ -
Cash for Prior Months Purchases $ -0 $ - $ - $ - $ -
Accounts Payable Balance $ - $ - $ - $ - $ -
This is the Student Template, provided in the assignment instructions October 2019

#4

Assignment 4.4 Exercises
Problem 4: Preparing a Cash Budget 10 Points
Hayes, Inc. is growing steadily, causing some concerns about its cash flow. The company's estimated sales and purchases for the past three months, plus projected sales and purchases for the next three months, are shown below:
Actual Forecast
October November December January February March
Sales $ 250,000 $ 275,000 $ 300,000 $ - $ 325,000 $ 350,000 $ 400,000
Purchases $ 175,000 $ 200,000 $ 225,000 $ - $ 250,000 $ 275,000 $ 300,000
Half of Hayes' sales are for cash. The company's Average Collection Period from customers is 60 days. It's standard pay terms with suppliers is 30 days.
a) What are the company's expected cash receipts in March?
b) What would the company's Accounts Receivable balance be at the end of March?
c) What are the company's expected cash expenditures for purchases in March?
d) What would be the company's Accounts Payable balance at the end of March?
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 4.4 Exercises
Problem 5: Preparing a Cash Budget 5 points
Cleveland Twice Industrials Co. is projecting a potential cash shortfall in the next three months. The company's estimated sales and purchases for the past three months, plus projected sales and purchases for the next three months, are shown below:
Actual Forecast
October November December January February March
Sales $ 240,000 $ 280,000 $ 800,000 $ - $ 400,000 $ 160,000 $ 160,000
Purchases $ 340,000 $ 360,000 $ 600,000 $ - $ 150,000 $ 80,000 $ 80,000
30% of Cleveland's sales are for cash. The company's Average Collection Period from customers is 90 days. It's standard pay terms with suppliers is 60 days. In addition, the company is planning the following cash expenditures:
Wages Payable Each Month $ 120,000
Taxes Payable End of March $ 110,000
Interest Payable Each Month $ 30,000
Equipment Purchase in February $ 175,000
The company's cash balance on January 1st is $200,000. It desires a minimum cash balance of $100,000 at all times.
a) Construct a monthly cash budget for January through March.
b) How large a loan does the company need to sustain its minimum cash balance?
Use the Template Provided Below to Create Your Solution - Be sure to show or explain your work to get full credit!
Input area:
Cash Flows Actual Forecast
October November December January February March
Sales ($) $ -
Purchases ($) $ -
Other Cash Expenditures ($)
Percent Sales in Cash (%) Starting Cash Balance:
Collection Period (Days) Minimum Cash Balance:
Payables Period (Days)
Output area:
Forecast
January February March
Cash Receipts:
Sales for Cash $ - $ - $ -
Collections from Credit Sales $ - $ - $ -
Total Cash Receipts $ - $ - $ -
Cash Disbursements:
Payment for Purchases $ - $ - $ -
Other Cash Disbursements: $ - $ - $ -
Total Cash Disbursements $ - $ - $ -
Net Receipts (Disbursements) $ -0 $ -0 $ -0
Determination of Cash Needs:
Beginning Cash $ -0 $ -0 $ -0
Net Receipts (Disbursements) $ -0 $ -0 $ -0
Ending Cash $ -0 $ -0 $ -0
Minimum Cash Desired $ -0 $ -0 $ -0
Cash Surplus (Deficit) $ - $ - $ -
This is the Student Template, provided in the assignment instructions October 2019

#6

Assignment 4.4 Exercises
Problem 6: Preparing a Cash Budget 10 points
Taft, Inc. is planning its financing needs for the next three months. The company's estimated sales and purchases for the past three months, plus projected sales and purchases for the next three months, are shown below:
Actual Forecast
October November December January February March
Sales $ 450,000 $ 550,000 $ 800,000 $ - $ 400,000 $ 450,000 $ 450,000
Purchases $ 400,000 $ 500,000 $ 350,000 $ - $ 200,000 $ 300,000 $ 300,000
40% of Taft's sales are for cash. The company's Average Collection Period from customers is 60 days. It's standard pay terms with suppliers is 90 days. In addition, the company is planning the following cash expenditures:
Wages Payable Each Month $ 85,000
Taxes Payable End of March $ 80,000
Interest Payable Each Month $ 15,000
Dividend Payment in March $ 75,000
The company's cash balance on January 1st is $350,000. It desires a minimum cash balance of $200,000 at all times.
a) Construct a monthly cash budget for January through March.
b) Does the company need a loan to sustain its minimum cash balance? If so, how large should it be?
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 4.4 Exercises
Problem 7: Growth Management Analysis 5 Points
Coolidge, Inc. provides private policing and security services, primarily for American Indian reservations. Following are some selected financial data for the company for the period 2015-2019:
2015 2016 2017 2018 2019
Profit Margin (%) 5.0% 6.0% 3.6% 4.2% 6.2%
Retention Ratio (%) 99.5% 100.0% 100.0% 100.0% 100.0%
Total Asset Turnover Ratio 1.20 1.90 1.80 2.00 2.50
Financial Leverage (Assets/Equity) 1.70 1.80 1.70 1.70 1.70
Actual Growth Rate in Sales (%) 36.8% 74.6% 3.1% 15.9% 47.1%
a) Calculate the company's sustainable growth rate for the years 2015 - 2019.
b) Compare the company's sustainable growth rate with its actual growth rate in sales. What growth management challenge did the company face over this period?
c) How did the company cope with this challenge?
Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs.
Input area:
2015 2016 2017 2018 2019
Profit Margin (%)
Retention Ratio (%)
Total Asset Turnover Ratio
Financial Leverage (Assets/Equity)
Actual Growth Rate in Sales (%)
Output area:
2015 2016 2017 2018 2019
Sustainable Growth Rate 0.0% 0.0% 0.0% 0.0% 0.0%
b) Intepretation: What growth management challenge did the company face over this period?
(briefly explain here)
c) Intepretation: How did the company cope with this challenge?
(briefly explain here)
This is the Student Template, provided in the assignment instructions October 2019

#8

Assignment 4.4 Exercises
Problem 8: Growth Management Analysis 10 Points
Eisenhower Co. has a storied history, but recently shareholders have been dissatisfied with its anemic growth. Following are selected financial data for the period 2015-2019:
2015 2016 2017 2018 2019
Profit Margin (%) 13.5% 13.2% 13.7% 13.1% 12.4%
Retention Ratio (%) 50.0% 50.0% 50.0% 50.0% 50.0%
Total Asset Turnover Ratio 1.90 1.85 1.70 1.75 1.65
Financial Leverage (Assets/Equity) 1.50 1.50 1.50 1.50 1.50
Actual Growth Rate in Sales (%) 6.2% 3.0% -2.1% 4.7% 3.5%
a) Calculate the company's sustainable growth rate for the years 2015 - 2019.
b) Compare the company's sustainable growth rate with its actual growth rate in sales. What growth management challenge did the company face over this period?
c) What could the company do to cope with this challenge?
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