Problem 4-24 Problem 4-26 Problem 4-28
Spreadsheet Templates by Block, Hirt and Danielsen
Copyright © 2011 McGraw-Hill/Irwin and ANSR Source India Pvt. Ltd. (www.ansrsourceindia.com)
Spreadsheet Templates
Foundations of Financial Management
Financial Forecasting
MAIN MENU – CHAPTER 4
Problem 4-24
Objective: Cash budget
Student Name:
Course Name:
Student ID:
Course Number:
Lansing Auto Parts, Inc., has projected sales of $25,000 in October, $35,000 in November, and $30,000
in December. Of the company’s sales, 20 percent are paid for by cash and 80 percent are sold on credit.
The credit sales are collected one month after sale. Determine collections for November and December.
Also assume the company‘s cash payments for November and December are $30,400 and $29,800,
respectively.The beginning cash balance in November is $6,000, which is the desired minimum balance.
Prepare a cash budget with borrowing needed or repayments for November and December. (You will need
to prepare a cash receipts schedule first).
Block, Hirt and Danielsen
Foundations of Financial Management
Problem 4-24
Instructions
Enter cell references, data, and formulas to complete the cash receipts schedule and the cash budget.
October November December
$25,000 $35,000 $30,000
7,000 6,000
20,000 28,000
$27,000 $34,000
November December
Cash receipts $27,000 $34,000
30,400 29,800
Net Cash Flow -3,400 4,200
Beginning Cash Balance 6,000 6,000
Cumulative Cash Balance 2,600 10,200
Monthly Loan or (Repayment) 3,400 -3,400
Cumulative Loan Balance 3,400 –
Ending Cash Balance $6,000 $6,800
Cash payments
Cash Budget
Total cash receipts
Cash sales (20%)
Collections (80% of previous month’s
sales)
Lansing Auto Parts, Inc.
Sales
Solution
Problem 4-26
Objective: Complete cash budget
Student Name:
Course Name:
Student ID:
Course Number:
Archer Electronics Company’s actual sales and purchases for April and May are shown here along with forecasted
sales and purchases for June through September.
Sales Purchases
April (actual) $320,000 $130,000
May (actual) 300,000 120,000
June (forecast) 275,000 120,000
July (forecast) 275,000 180,000
August (forecast) 290,000 200,000
September (forecast) 330,000 170,000
The company makes 10 percent of its sales for cash and 90 percent on credit. Of the credit sales, 20
percent are collected in the month after the sale and 80 percent are collected two months later.
Archer pays for 40 percent of its purchases in the month after purchase and 60 percent two months after.
Labor expense equals 10 percent of the current month’s sales. Overhead expense equals $12,000 per
month. Interest payments of $30,000 are due in June and September. A cash dividend of $50,000 is
scheduled to be paid in June. Tax payments of $25,000 are due in June and September. There is a
scheduled capital outlay of $300,000 in September.
Archer Electronics’s ending cash balance in May is $20,000. The minimum desired cash balance is $10,000.
Prepare a schedule of monthly cash receipts, monthly cash payments, and a complete monthly cash budget
with borrowing and repayments for June through September. The maximum desired cash balance is $50,000.
Excess cash (above $50,000) is used to buy marketable securities. Marketable securities are sold before
borrowing funds in case of a cash shortfall (less than $10,000).
Foundations of Financial Management
Block, Hirt and Danielsen
Problem 4-26
Instructions
April May June July Aug Sept
Sales $320,000 $300,000 $275,000 $275,000 $290,000 $330,000
Credit Sales (90%) 288,000 270,000 247,500 247,500 261,000 297,000
Cash Sales (10%) 32,000 30,000 27,500 27,500 29,000 33,000
Collections (month
after sale) 20%
57,600 54,000 49,500 49,500 52,200
Collections (second
month after sale)
80%
230,400 216,000 198,000 198,000
Total Cash
Receipts
$311,900 $293,000 $276,500 $283,200
April May June July Aug Sept
Purchases $130,000 $120,000 $120,000 $180,000 $200,000 $170,000
Payments (month
after
purchase—40%)
52,000 48,000 48,000 72,000 80,000
Payments (second
month after
purchase—60%)
78,000 72,000 72,000 108,000
Labor Expense
(10% of sales)
27,500 27,500 29,000 33,000
Overhead 12,000 12,000 12,000 12,000
Interest Payments 30,000 30,000
Total Cash
Payments
Archer Electronics
Cash Payments Schedule
Solution
Archer Electronics
Cash Receipts Schedule
June July August September
Cash Receipts $311,900 $293,000 $276,500 $283,200
Cash Payments 270,500 159,500 185,000 588,000
Net Cash Flow 41,400 133,500 91,500 -304,800
Beginning Cash Balance 20,000 50,000 50,000 50,000
Cumulative Cash Balance 61,400 183,500 141,500 -254,800
Monthly Borrowing or (Repayment) 28,400
Cumulative Loan Balance 28,400
Marketable Securities Purchased 11,400 133,500 91,500
(Sold) -236,400
Cumulative Marketable Securities 11,400 144,900 236,400
Ending Cash Balance $50,000 $50,000 $50,000 $10,000
Cash Budget
Archer Electronics
Problem 4-28
Objective: Percent-of-sales method
Student Name:
Course Name:
Student ID:
Course Number:
The Manning Company has financial statements, which are representative of the company’s historical average.
The firm is expecting a 20 percent increase in sales next year, and management is concerned about the
company’s need for external funds. The increase in sales is expected to be carried out without any expansion
of fixed assets, but rather through more efficient asset utilization in the existing store. Among liabilities, only
current liabilities vary directly with sales.
Using the percent-of-sales method, determine whether the company has external financing needs or a surlpus
of funds. (Hint: A profit margin and payout ratio must be found from the income statement.)
Sales $200,000
Expenses 158,000
Earnings before interest and taxes
$42,000
Interest 7,000
Earnings before taxes $35,000
Taxes 15,000
Earnings after taxes $20,000
Dividends $6,000
Cash $5,000 $25,000
Accounts receivable 40,000 1,000
Inventory 75,000 2,000
Current assets $120,000 Current liabilities $28,000
Fixed assets 80,000 7,000
15,000
120,000
30,000
Total assets $200,000
Total liabilities and stockholders’ equity
$200,000
Foundations of Financial Management
Block, Hirt and Danielsen
INCOME STATEMENT
Assets
Liabilities and Stockholders’ Equity
Accounts payable
BALANCE SHEET
Notes payable
Long-term debt
Common stock
Retained earnings
Accrued wages
Accrued taxes
Problem 4-28
Instructions
Using cell references and formulas, calculate the financial items below to ultimately determine the
external funds that will be needed.
Profit margin 10%
Payout ratio 30%
Change in sales ($) $40,000
Change in spontaneous assets $24,000
Change in spontaneous liabilities $5,600
Change in retained earnings $16,800
External funds needed $1,600
Solution