Cyrus Brown
Manufacturing (CBM) - To avoid any uncertainty regarding his business'
financing needs at the time when such needs may arise, Cyrus Brown wants to
develop a cash budget for his latest venture
2-page Excel
Spreadsheet (show all work)
Please read the
relevant parts of your textbook, which refer to cash flow and financial
planning.
To avoid any
uncertainty regarding his business' financing needs at the time when such needs
may arise, Cyrus Brown wants to develop a cash budget for his latest venture:
Cyrus Brown Manufacturing (CBM). He has estimated the following sales forecast
for CBM over the next 9 months:
March
|
$100,000
|
April
|
$275,000
|
May
|
$320,000
|
June
|
$450,000
|
July
|
$700,000
|
August
|
$700,000
|
September
|
$825,000
|
October
|
$500,000
|
November
|
$115,000
|
He has also gathered
the following collection estimates regarding the forecast sales:
Payment collection
within the month of sale = 25%
Payment collection the
month following sales = 55%Payment collection the second month following sales = 20%
Payments for direct
manufacturing costs like raw materials and labor are made during the month that
follows the one in which such costs have been incurred. These costs are
estimated as follows:
March
|
$187,500
|
April
|
$206,250
|
May
|
$375,000
|
June
|
$337,500
|
July
|
$431,250
|
August
|
$640,000
|
September
|
$395,000
|
October
|
$425,000
|
Additional financial
information is as follows:
Administrative
salaries will approximately amount to $35,000 a month.Lease payments around $15,000 a month.
Depreciation charges, $15,000 a month.
A one-time new plant
investment in the amount of $95,000 is expected to be incurred and paid in
June.
Income tax payments
estimated to be around $55,000 will be due in both June and September.And finally, miscellaneous costs are estimated to be around $10,000 a month.
Cash on hand on March 1 will be around $50,000, and a minimum cash balance of $50,000 shall be on hand at all times.
please show all work,
including formulas and calculations used to arrive at the financial values.
SOLUTION PREVIEW (The Solution is done in EXCEL SHEET)
a.
Cash Budget
|
Particulars
|
March
|
April
|
May
|
June
|
July
|
August
|
September
|
October
|
November
|
|
Receipts:
|
|||||||||
|
25% of current month's sales
|
25000
|
68750
|
80000
|
112500
|
175000
|
175000
|
206250
|
125000
|
28750
|
File name: Cyrus-Brown-Manufacturing-CBM.xls File type: application/vnd.ms-excel Price: $12
