Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Input Data Collections during month of sale 20% Collections during month after sale 60% Collections during second month after sale 20% Lease payments $7,000 Target

Input Data
Collections during month of sale 20%
Collections during month after sale 60%
Collections during second month after sale 20%
Lease payments $7,000
Target cash balance $30,000
General and administrative salaries $25,000
Depreciation charges $8,000
Income tax payments (Sep & Dec) $30,000
Miscellaneous expenses $5,000
New office suite payment (Oct) $95,000
Cash on hand July 1 $70,000
Sales, labor, and RM adjustment factor 0%
a. Prepare a monthly cash budget for the last six months of the year.
May June July August September October November December
Original sales estimates $75,000 $1,15,000 $1,45,000 $1,25,000 $1,20,000 $95,000 $75,000 $55,000
Original labor and raw mat. estimates $80,000 $75,000 $1,05,000 $85,000 $65,000 $70,000 $30,000 $35,000
Forecasted Sales
Sales (gross) $75,000 $1,15,000 $1,45,000 $1,25,000 $1,20,000 $95,000 $75,000 $55,000
Collections
During month of sale 1,45,000 *20 = 29,000 125000*20 = 25,000 120000*20= 24,000 95000*20=19,000 75000*20= 15,000 55000*20=11,000
During 1st month after sale 1,15,000* 60= 69,000 145000*60 = 87,000 125000*60=75,000 120000*60=72,000 95000*60= 57,000 75000*60=45,000
During 2nd month after sale 75,000 * 20 = 15,000 115000*20 = 23,000 145000*20= 29,000 125000*20=25,000 120000*20= 24,000 95000*20=19,000
Total collections $1,13,000 $135,000 $1,28,000 $1,16,000 $96,000 $75,000
Purchases
Labor and raw materials $80,000 $75,000 $1,05,000 $85,000 $65,000 $70,000 $30,000 $35,000
Payments for labor and raw materials $75,000 $1,05,000 $85,000 $65,000 $70,000 $30,000
Payments
Payments for labor and raw materials $75,000 $1,05,000 $85,000 $65,000 $70,000 $30,000
General and administrative salaries 25,000 25,000 25,000 25,000 25,000 25,000
Lease payments 7,000 7,000 7,000 7,000 7,000 7,000
Miscellaneous expenses 5,000 5,000 5,000 5,000 5,000 5,000
Income tax payments 30,000 30,000
Design studio payment 95,000
Total payments $1,12,000 $1,42,000 $1,52,000 $1,97,000 $1,07,000 $97,000
Net Cash Flows
Cash on hand at start of forecast period $70,000 $71,000 $64,000 $40,000
Net cash flow (NCF): Total collections - Total payments $1,000 ($7,000) ($24,000) ($81,000) ($11,000) ($22,000)
Cumulative NCF: Prior month cumulative + this month's NCF $71,000 $64,000 $40,000 ($41,000) ($52,000) ($74,000)
Cash Surplus (or Loan Requirement)
Target cash balance $30,000 $30,000 $30,000 $30,000 $30,000 $30,000
Surplus cash or loan needed: Cum NCF - Target cash $41000 $34,000 $10,000 ($71,000) ($82,000) ($1,04,000)
Max. Loan ($1,04,000)

d. If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if sales dropped off, this would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two factors on the max loan requirement. Assume the purchases of labor and raw material also vary by the sales adjustment factor.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamentals of Financial Management

Authors: Eugene F. Brigham, Joel F. Houston

12th edition

978-0324597714, 324597711, 324597703, 978-8131518571, 8131518574, 978-0324597707

More Books

Students also viewed these Accounting questions

Question

Redo Problem 12.16, given 84x (7x2-4)3 dx

Answered: 1 week ago