4

profileusa94
WorkshopFourPracticeProblems.xlsx
Home>Business & Finance homework help>Accounting homework help>4

Example 1

Workshop Four Practice Exercises
Example 1: Creating Proforma Financial Statements
The 2019 Income Statement and Balance Sheet are given below for Mentor Johnson Enterprises. Using the assumptions provided, create the 2020 Pro Forma Income Statement and Balance Sheet. What is the External Funding Required?
Mentor Johnson Enterprises . Mentor Johnson Enterprises
Income Statement ($ thousands) Balance Sheets
Year Ended December 31st, 2019 Year Ended December 31st, 2019 ($ thousands)
2019 Actual
Net Sales $ 11,231 2019 Actual 2019 Actual
Cost of Goods Sold 5,829 Current Assets Current Liabilities
Gross Profit $ 5,402 Cash and Securities $ 830 Accounts Payable $ 810
Accounts Receivable 2,120 Accured Wages 115
Sales Commisions $ 562 Inventory 830 Unearned Revenue 1,240
Warehouse Lease 2,986 Total $ 3,780 Total $ 2,165
Depreciation Expense 450
Operating Income (EBIT) $ 1,966 Long-Term Debt $ 1,830
Interest Expense 183 Net Fixed Assets $ 3,830 Owner's Equity
Taxable Income $ 1,783 Common Stock $ 100
Retained Earnings 3,515
Income Tax Expense 374 Total $ 3,615
Net Income $ 1,409 Total Assets $ 7,610 Total Liabilities and Owners' Equity $ 7,610
Mentor Johnson Enterprises Assumptions for 2020
Growth rate in Net Sales 10% Long-Term Debt ($ estimate) $ 1,830
Cost of Goods Sold (% of Sales) 52% Cash/Securities (Days of Sales) 25
Sales Commissions (% of Sales) 5% Accounts Receivable Period (Days) 70
Warehouse Lease (estimate $) $ 3,200 Inventory Turnover 7
Depreciation Expense (estimate $) $ 465 Accounts Payable Period (Days) 30
Interest Expense (Total estimate $) $ 247 Accrued Wages ($ estimate) $ 135
Tax Rate 21% Unearned Revenue (% of Sales) 10%
Dividend Payout Ratio 70% Net Fixed Assets ($ estimate) $ 4,180
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?
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 Four Practice Exercises
Example 1: Creating Proforma Financial Statements
The 2019 Income Statement and Balance Sheet are given below for Mentor Johnson Enterprises. Using the assumptions provided, create the 2020 Pro Forma Income Statement and Balance Sheet. What is the External Funding Required?
Mentor Johnson Enterprises . Mentor Johnson Enterprises
Income Statement ($ thousands) Balance Sheets
Year Ended December 31st, 2019 Year Ended December 31st, 2019 ($ thousands)
2019 Actual
Net Sales $ 11,231 2019 Actual 2019 Actual
Cost of Goods Sold 5,829 Current Assets Current Liabilities
Gross Profit $ 5,402 Cash and Securities $ 830 Accounts Payable $ 810
Accounts Receivable 2,120 Accured Wages 115
Sales Commisions $ 562 Inventory 830 Unearned Revenue 1,240
Warehouse Lease 2,986 Total $ 3,780 Total $ 2,165
Depreciation Expense 450
Operating Income (EBIT) $ 1,966 Long-Term Debt $ 1,830
Interest Expense 183 Net Fixed Assets $ 3,830 Owner's Equity
Taxable Income $ 1,783 Common Stock $ 100
Retained Earnings 3,515
Income Tax Expense 374 Total $ 3,615
Net Income $ 1,409 Total Assets $ 7,610 Total Liabilities and Owners' Equity $ 7,610
Mentor Johnson Enterprises Assumptions for 2020
Growth rate in Net Sales 10% Long-Term Debt ($ estimate) $ 1,830
Cost of Goods Sold (% of Sales) 52% Cash/Securities (Days of Sales) 25
Sales Commissions (% of Sales) 5% Accounts Receivable Period (Days) 70
Warehouse Lease (estimate $) $ 3,200 Inventory Turnover 7
Depreciation Expense (estimate $) $ 465 Accounts Payable Period (Days) 30
Interest Expense (Total estimate $) $ 247 Accrued Wages ($ estimate) $ 135
Tax Rate 21% Unearned Revenue (% of Sales) 10%
Dividend Payout Ratio 70% Net Fixed Assets ($ estimate) $ 4,180
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?
Check below for a detailed solution to this problem.
Output area:
Build the statements one line at a time, using the assumptions given.
Mentor Johnson Enterprises .
Income Statement ($ thousands)
Pro Form Year Ending December 31st, 2020
2020 Forecast
Net Sales $ 12,354 New Net Sales = Old Sales X (1 + growth rate)
Cost of Goods Sold 6,424 New Cost of Goods Sold = New Sales X 52% given assumption
Gross Profit $ 5,930
Sales Commissions 618 New Sales Commissions = New Sales X 5% given assumption
Warehouse Lease 3,200 Warehouse Lease cost is given as an estimate… This is common. Sometimes estimates are tied to sales, in other cases they are fixed costs that must be estimated.
Depreciation Expense 465 New Deprecation is given as an estimate
Operating Income (EBIT) $ 1,647
Interest Expense 247 Note Interest Expense is given… This is to simplify: It eliminates the recursive calculation described in the textbook.
Taxable Income $ 1,400 Otherwise you would have to iterate, calculating the external funding required, which changes the interest cost, which changes the external funding required, and so on…
Income Tax Expense 294 New Income Tax Expense = Taxable Income X 21% rate given
Net Income $ 1,106 Note the Net Income has fallen significantly, even though sales has grown 105!
Mentor Johnson Enterprises . Note in order to complete the calculations you will have to remember your ratio training.
Balance Sheets You have to be able to convert days or turnover ratios into Balance Sheet figures (see below)…
Pro Forma Year Ending December 31st, 2020 ($ thousands)
2019 Actual 2019 Actual
Current Assets Current Liabilities
New Cash = (Days Sales in Cash / 365) X Sales Cash and Securities $ 846 Accounts Payable $ 528 New A/P = (Payables Period / 365) X COGS
New Accounts Receivable = (Receivables Period / 365) X Sales Accounts Receivable 2,369 Accured Wages 135 Accrued Wages was given as an estimate
New Inventory = COGS / Inventory Turnover Inventory 918 Unearned Revenue 1,235 New Unearned Revenue = Sales X Proportion Given
Total $ 4,133 Total $ 1,898
Long-Term Debt $ 1,830 Long-Term Debt was given as an estimate
Net Fixed Assets is given as an estimate. Net Fixed Assets $ 4,180 Owner's Equity
The problem could also give you the Fixed Asset purchases… Common Stock $ 100 Common Stock given as no change
… and then the depreciation. Retained Earnings 3,847 New Retained Earnings = Old Retained Earnings + Net Income X (1 - Payout Ratio)
New Net Fixed Assets = Old + Purchases - Depreciation Total $ 3,947
Total Assets $ 8,313 Total Liabilities and Owners' Equity $ 7,675 This is simply calculated as the sum… Note in this case, the Balance Sheet doesn't balance!
We need external financing as the "plug variable" to make the Balance Sheet balance.
External Financing Needed: $ 638 External Financing Needed = Total Assets - Total Liabilities and Equity
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 2

Assignment 4.4 Exercises
Example 2: Cash Flow Timing (Preparing a Cash Budget)
Wheeler, Inc.'s sales has shown significant seasonality in the past, 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
January February March April May June
Sales $ 250,000 $ 350,000 $ 700,000 $ - $ 600,000 $ 400,000 $ 300,000
Purchases $ 250,000 $ 500,000 $ 450,000 $ - $ 285,000 $ 200,000 $ 200,000
Only 20% of Wheeler'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 June
b) What would the company's Accounts Receivable balance be at the end of June?
c) What are the company's expected cash expenditures for purchases in June?
d) What would be the company's Accounts Payable balance at the end of June?
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 Instructor's Confidential Solutions Manual, provided by D. Kendall October 2019
This is the student Practice Problem file, provided in the assignment instructions October 2019

Ex # 2 Solution

Assignment 4.4 Exercises
Example 2: Cash Flow Timing (Preparing a Cash Budget)
Wheeler, Inc.'s sales has shown significant seasonality in the past, 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
January February March April May June
Sales $ 250,000 $ 350,000 $ 700,000 $ - $ 600,000 $ 400,000 $ 300,000
Purchases $ 250,000 $ 500,000 $ 450,000 $ - $ 285,000 $ 200,000 $ 200,000
Only 20% of Wheeler'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 June
b) What would the company's Accounts Receivable balance be at the end of June?
c) What are the company's expected cash expenditures for purchases in June?
d) What would be the company's Accounts Payable balance at the end of June?
Check below for a detailed solution to this problem.
Input area:
The real challenge with these problems is the timing.
Cash Flows Actual Forecast You have to think carefully about when cash flows will actually occur in relation to the underlying transaction (sale or purchase).
January February March April May June It may help to think through examples: If a sale or purchase occurs on X day, what date will payment occur?
Sales ($) $ 250,000 $ 350,000 $ 700,000 $ - $ 600,000 $ 400,000 $ 300,000
Purchases ($) $ 250,000 $ 500,000 $ 450,000 $ - $ 285,000 $ 200,000 $ 200,000 Note a typical seasonal pattern here, with purchases occuring in advance of sales.
This is one common cause of cash flow problems in the real world!
Percent Sales in Cash (%) 20%
Collection Period (Days) 90 These periods aren't really used in the calculations below, but determine how the timing is set up.
Payables Period (Days) 45
Output area:
Actual Forecast
January February March April May June
Cash Sales $ 50,000 $ 70,000 $ 140,000 $ 120,000 $ 80,000 $ 60,000 Cash sales is 20% of the total
Credit Sales $ 200,000 $ 280,000 $ 560,000 $ 480,000 $ 320,000 $ 240,000 Credit sales is 80% of the total
Receipts:
Cash from Current Month Sales $ 50,000 $ 70,000 $ 140,000 $ 120,000 $ 80,000 $ 60,000 Cash received immediately due to cash sales
Cash from Prior Months Sales $ 200,000 $ 280,000 $ 560,000 Cash received with three month lag for credit sales due to 90-day pay terms
Total Cash Receipts $ 320,000 $ 360,000 $ 620,000 a) For instance, a January 15 purchase is paid for on April 15.
Accounts Receivable Balance $ 1,040,000 $ 1,320,000 $ 1,360,000 $ 1,040,000 b) A/R balance is the last 3 months of credit sales due to the 90-day payment terms
Credit Purchases $ 250,000 $ 500,000 $ 450,000 $ 285,000 $ 200,000 $ 200,000 Assumption is that all purchases are made on credit
The timing for purchases is a bit more complicated, given 45 days is not a multiple of one month.
Cash for Prior Months Purchases $ 375,000 $ 475,000 $ 367,500 $ 242,500 c) Cash is expended with a 1.5 month lag due to 45-day pay terms.
For instance, a January 1 purchase is paid for on February 15. A January 15 purchase is paid for on March 1.
So the March expenditure is the back half of January and the front half of February! Again be careful with the timing.
Accounts Payable Balance $ 625,000 $ 700,000 $ 510,000 $ 342,500 $ 300,000 d) A/P balance is the last month-and-a-half's purchases
For instance, the March end of month balance is the back half of February plus all of March purchases.
This is the student Practice Problem file, provided in the assignment instructions October 2019
This is the Instructor's Confidential Solutions Manual, provided by D. Kendall October 2019

Example 3

Assignment 4.4 Exercises
Example 3: Preparing a Cash Budget
Sherman, Inc. is planning its external 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 $ 1,200,000 $ 1,300,000 $ 1,450,000 $ - $ 1,200,000 $ 1,100,000 $ 1,250,000
Purchases $ 1,100,000 $ 1,200,000 $ 1,000,000 $ - $ 850,000 $ 900,000 $ 850,000
60% of Sherman's sales are for cash. The company's Average Collection Period from customers is 90 days. It's standard pay terms with suppliers is 30 days. In addition, the company is planning the following cash expenditures:
Wages Payable Each Month $ 75,000
Taxes Payable End of February $ 60,000
Interest Payable Each Month $ 8,000
Equipment Purchase in January $ 230,000
Dividend Payment in March $ 65,000
The company's cash balance on January 1st is $450,000. It desires a minimum cash balance of $400,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?
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

Assignment 4.4 Exercises
Example 3: Preparing a Cash Budget
Sherman, Inc. is planning its external 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 $ 1,200,000 $ 1,300,000 $ 1,450,000 $ - $ 1,200,000 $ 1,100,000 $ 1,250,000
Purchases $ 1,100,000 $ 1,200,000 $ 1,000,000 $ - $ 850,000 $ 900,000 $ 850,000
60% of Sherman's sales are for cash. The company's Average Collection Period from customers is 90 days. It's standard pay terms with suppliers is 30 days. In addition, the company is planning the following cash expenditures:
Wages Payable Each Month $ 75,000 Note some of these expenses occur every month, while some of them are one-time.
Taxes Payable End of February $ 60,000 You will have to add them up for each month.
Interest Payable Each Month $ 8,000
Equipment Purchase in January $ 230,000
Dividend Payment in March $ 65,000
The company's cash balance on January 1st is $450,000. It desires a minimum cash balance of $400,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?
Check below for a detailed solution to this problem.
Input area:
This problem is a bit more comprehensive than the last.
Cash Flows Actual Forecast We still have to figure out the timing of cash flows from sales and purchases, plus have a few other items to keep track of.
October November December January February March
Sales ($) $ 1,200,000 $ 1,300,000 $ 1,450,000 $ 1,200,000 $ 1,100,000 $ 1,250,000
Purchases ($) $ 1,100,000 $ 1,200,000 $ 1,000,000 $ 850,000 $ 900,000 $ 850,000 This company's cash flow are a little more steady than the last.
Other Cash Expenditures ($) $ 313,000 $ 143,000 $ 148,000 <--- Here we have to figure out which expenditures occur in each month from above!
Wages and Interest occur every month. The others are one-time in their respective months.
Percent Sales in Cash (%) 60% Starting Cash Balance: $ 450,000 The high percentage of sales made in cash should help with cash flow.
Collection Period (Days) 90 Minimum Cash Balance: $ 400,000 However, the collection period is much longer than the payables period.
Payables Period (Days) 30
Output area:
Forecast
January February March
Cash Receipts:
Sales for Cash $ 720,000 $ 660,000 $ 750,000 Cash sales is 60% of the total
Collections from Credit Sales $ 480,000 $ 520,000 $ 580,000 Credit sales is 40% of the total, but is lagged 3 months due to the 90-day pay terms.
Total Cash Receipts $ 1,200,000 $ 1,180,000 $ 1,330,000 For instance, an October 15 sale is paid for January 15.
Cash Disbursements:
Payment for Purchases $ 1,000,000 $ 850,000 $ 900,000 All purchases made on credit. Payments lagged 1 month due to 30-day pay terms.
Other Cash Disbursements: $ 313,000 $ 143,000 $ 148,000 For instance, a December 15 purchase is paid for on January 15.
Total Cash Disbursements $ 1,313,000 $ 993,000 $ 1,048,000
Net Receipts (Disbursements) $ (113,000) $ 187,000 $ 282,000
Determination of Cash Needs:
Beginning Cash $ 450,000 $ 337,000 $ 524,000 Follow the logic in this section carefully:
Net Receipts (Disbursements) $ (113,000) $ 187,000 $ 282,000 Ending = Beginning + Receipts - Disbursements
Ending Cash $ 337,000 $ 524,000 $ 806,000 Surplus = Ending - Minimum
Minimum Cash Desired $ 400,000 $ 400,000 $ 400,000
Cash Surplus (Deficit) $ (63,000) $ 124,000 $ 406,000 The company may need a small loan to get through January, but is then in great shape.
There is a substantial cash surplus at the end of March.
Another option would be to delay the equipment purchase until February.
This would keep the company at a consistent surplus (try it out!)
This is the student Practice Problem file, provided in the assignment instructions October 2019

Example 4

Assignment 4.4 Exercises
Example 4: Growth Management Analysis
Dawes, Inc. is a rapidly-growing company, and is struggling to maintain its cash flow. Following are some selected financial data for the company for the period 2015-2019:
2015 2016 2017 2018 2019
Profit Margin (%) 8.0% 9.5% 13.0% 16.0% 19.0%
Retention Ratio (%) 100.0% 100.0% 100.0% 100.0% 100.0%
Total Asset Turnover Ratio 1.20 1.15 1.20 1.25 1.25
Financial Leverage (Assets/Equity) 1.15 1.25 1.40 1.60 1.80
Actual Growth Rate in Sales (%) 35.0% 42.0% 45.0% 35.0% 25.0%
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? What could they do going forwards?
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

Assignment 4.4 Exercises
Example 4: Growth Management Analysis
Dawes, Inc. is a rapidly-growing company, and is struggling to maintain its cash flow. Following are some selected financial data for the company for the period 2015-2019:
2015 2016 2017 2018 2019
Profit Margin (%) 8.0% 9.5% 13.0% 16.0% 19.0%
Retention Ratio (%) 100.0% 100.0% 100.0% 100.0% 100.0%
Total Asset Turnover Ratio 1.20 1.15 1.20 1.25 1.25
Financial Leverage (Assets/Equity) 1.15 1.25 1.40 1.60 1.80
Actual Growth Rate in Sales (%) 35.0% 42.0% 45.0% 35.0% 25.0%
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? What could they do going forwards?
Check below for a detailed solution to this problem.
Input area:
2015 2016 2017 2018 2019
Profit Margin (%) 8.0% 9.5% 13.0% 16.0% 19.0% The calculations here are extremely easy (see equation on page 114).
Retention Ratio (%) 100.0% 100.0% 100.0% 100.0% 100.0% What is more challenging is the interpretation.
Total Asset Turnover Ratio 1.20 1.15 1.20 1.25 1.25 Pay careful attention to the discussions of this formula in the textbook!
Financial Leverage (Assets/Equity) 1.15 1.25 1.40 1.60 1.80
Actual Growth Rate in Sales (%) 35.0% 42.0% 45.0% 35.0% 25.0%
Output area:
2015 2016 2017 2018 2019
Sustainable Growth Rate 11.0% 13.7% 21.8% 32.0% 42.8% Sustainable Growth Rate = Profit Margin X Retention Ratio X Asset Turnover X Financial Leverage
b) Intepretation: What growth management challenge did the company face over this period?
For four out of five years, actual growth well exceeded the company's sustainable growth rate. It is thus not surprising they are facing cash flow problems. Essentially, the company is growing too quickly for its own good. If it cannot reduce the growth rate or find a better way to support it, the company risks bankruptcy!
c) Intepretation: How did the company cope with this challenge? What could they do going forwards?
To increase its sustainable growth rate, we can see the company has significantly improved its profit margins and financial leverage (it increased its debt). This continued until 2019, when the actual growth rate declined. In this scenario, the company has a few choices: They can sell equity, raise more debt (to a point), raise prices, further improve operations, or merge with a cash-rich company (see the discussion in the textbook).
This is the student Practice Problem file, provided in the assignment instructions October 2019