how do I get part E, Maximum loan required? =B13-(F53) gave me wrong answers. Am I missing something simple?
F J January $150,000 B D E F G H I 4 Collections during 1st month after sale 75% 5 Collections during 2nd month after sale 15% 6 General and administrative salaries $36,000 7 Lease payments $12,000 8 Depreciation charges $48,000 9 Miscellaneous expenses $1,800 10 Income tax payments (Sep & Dec) $61,000 11 New design studio payment (Oct) $165,000 12 Cash on hand on July 1 $132,000 13 Minimum cash balance $85,000 14 Sales adjustment factor 0% 15 16 May June July August September October November December 17 Sales $150,000 $150,000 $300,000 $450,000 $600,000 $300,000 $300,000 $75,000 18 Costs of labor and raw materials $75,000 $75,000 $105,000 $735,000 $225,000 $195,000 $135,000 $75,000 19 20 a. Preparing a monthly cash budget for the last 6 months of 2021 21 The Cash Budget 22 May June July August September October November December 23 Collections and purchases worksheet 24 Sales (gross) $150,000 $150,000 $300,000 $450,000 $600,000 $300,000 $300,000 $75,000 25 Collections 26 During month of sale $30,000 $45,000 $60,000 $30,000 $30,000 $7,500 27 During 1st month after sale 112,500 225,000' 337,500 450,000 225,000 225,000 28 During 2nd month after sale 22,500 22,500 45,000 67,500 90,000 45,000 29 Total collections $165,000 $292,500 $442.500 $547,500 $345.000 $277,500 30 Purchases 31 Labor and raw materials $75,000 $75,000 $105,000 $735,000 $225,000 $195,000 $135,000 $75,000 32 Payments for labor and raw materials $75,000' $105,000' $735,000' $225,000' $195,000' $135,000 33 34 Cash gain or loss for month 35 Collections $165,000 $292,500 $442,500 $547,500 $345,000 $277,500 36 Payments for labor and raw materials $75,000 $105,000 $735,000 $225,000 $195,000 $135,000 37 General and administrative salaries 36,000 36,000 36,000 36,000 36,000 36,000 38 Lease payments 12,000 12,000 12,000 12,000 12,000 12,000 39 Miscellaneous expenses 1,800 1,800 1,800 1,800 40 Income tax payments 61,000 61,000 41 Design studio payment 165,000 42 Total payments $124,800 $154,800 $845,800 $439,800 $244,800 $245,800 43 Net cash gain (loss) during month $40,200 $137,700 $403,300 $107,700 $100,200 $31,700 44 45 Loan requirement or cash surplus 46 Cash at start of month $132,000 $172,200 $309,900 -$93,400 $14,300 $114,500 47 Cumulative cash $172,200 $309,900 -$93,400 $14,300 $114,500 $146,200 48 Target cash balance 85,000 85,000 85,000 85,000 85,000 85,000 Cumulative surplus cash or loans outstanding $87,200 $224,900 -$178,400 -$70,700 $29,500 $61,200 49 to maintain the target cash balance 50 51 b. Preparing monthly estimates of the required financing or excess funds 52 July August September October November December 53 Required financing or excess funds $87,200 $224,900 -$178,400 -$70,700 $29,500 $61,200 54 55 e. Doing a sensitivity analysis that shows the effects of the change in sales and the collection terms 56 Maximum loan required #N/A 1,800 1,800
