Hello,

Can you please show me how to calculate "Ending Cash Balance" for the case study: "Cash is King: Master Budgets to Inform a Credit Decision" ? My excel spreadsheet was taking forever to upload, so I had to cancel it, in order to email my question. However, some tutors, who are familiar with this case study, have been able to help me, even with scattered data. Thank you!

Financing Borrowing Repayments Interest Total Financing Ending Cash Balance

Input Data (all currency in US$) Yellow-use only cell references Little Annin Flagmakers Blue-may type numbers here Sales Budget (US$) Budgeted Sales Expected April May June Quarter April (units) 2,500 Budgeted sales (units) 2,500 6,000 3,000 11,500 May (units) 6,000 Selling price per unit $120 $120 $120 $120 June (units) 3,000 Total Sales $300,000 $720,000 $360,000 $1,380,000 $1,380,000 July (units) 2,500 August (units) 2,000 Little Annin Flagmakers Selling Price/Unit $120.00 Schedule of Expected Cash Collections (US$) Notes April May June Quarter A/R March 31st = 132,000 Cash Collection Pattern Accounts receivable April's collection 300,000*.4 120000 Month of sale 40% Beginning balance $132,000 $132,000 Secon collection for April on May 132,000*.55 165000 Following month 55% Uncollectible 5% April sales $120,000 $165,000 $285,000 Cash Payments for Materials May sales $288,000 $396,000 $684,000 Month of purchase 40% Following month 60% June sales $144,000 $144,000 Production Requirements Total Cash Collections $252,000 $453,000 $540,000 $1,245,000 $1,245,000 Raw material per unit (lb.) 5 Raw material cost per lb. $7.00 Accounts Receivable as of June 30 $198,000 $198,000 Direct labor hours per unit 0.5 Direct labor rate per hour $40.00 Little Annin Flagmakers Variable MOHD per direct labor hour $10.00 Production Budget Fixed MOHD per month $57,950 April May June Quarter July August Depreciation in fixed MOHD $20,000 Budgeted sales 2,500 6,000 3,000 11,500 2,500 2,000 Add: Desired ending inventory 1,200 600 500 500 400 400 Selling & Administrative (S&A) Costs Total needs 3,700 6,600 3,500 12,000 2,900 2,400 Variable S&A cost per unit sold $1.25 Less: Beginning inventory 410 1,200 600 410 500 400 Fixed S&A cost per month $63,000 Required Production 3,290 5,400 2,900 11,590 2,400 Depreciation in fixed S&A cost $10,000 11,590 Little Annin Flagmakers Other Cash Outflows Direct Materials Budget (US$) Cash dividends paid each month $15,000 April May June Quarter Notes Equipment purchases May $47,820 Required production in units 3,290 5,400 2,900 11,590 Desired ending inv Equipment purchases June $154,600 Raw materials per unit (lbs.) 5 5 5 5 April:27,000*.4 = 10800 Production needs (lbs.) 16,450 27,000 14,500 57,950 May:14,500*.4 = 5800 Desired Ending Inventory Add: Desired ending inventory 10,800 5,800 4,800 4,800 June:2500*5*.4 = 4800 Finished goods 20% Total needs 27,250 32,800 19,300 62,750 Raw materials 40% Less: Beginning inventory 4600 10,800 5,800 4,600 Cash $30,000 Raw materials to be purchased 22,650 22,000 13,500 58,150 Cost of raw materials $7.00 $7.00 $7.00 $7.00 Beginning Account Balances as of March 31 Total Cost of Raw Materials $158,550 $154,000 $94,500 $407,050 $407,050 Cash $37,745 Accounts receivable $132,000 Finished goods inventory $30,750 Little Annin Flagmakers Finished goods cost per unit $75.00 Schedule of Expected Cash Disbursements for Material (US$) Finished goods inventory (units) 410 April May June Quarter Raw materials inventory $32,200 Accounts payable Raw materials (lb.) 4,600 Beginning balance $55,000 $55,000 Accounts payable $55,000 April purchases $63,420 $95,130 $158,550 Land $520,000 Buildings and equipment $1,800,000 May purchases $61,600 $92,400 $154,000 Accumulated depreciation ($750,000) Common stock $500,000 June purchases $37,800 $37,800 Retained earnings $1,247,695 Total Cash Disbursements for Materials $118,420 $156,730 $130,200 $405,350 $405,350 Accounts Payable as of June 30 $56,700 $56,700 Little Annin Flagmakers Direct Labor Budget (US$) April May June Quarter Units to be produced 3,290 5,400 2,900 11,590 Direct labor hours per unit 0.5 0.5 0.5 0.5 Total direct labor hours needed 1,645 2,700 1,450 5,795 Direct labor cost per hour $40.00 $40.00 $40.00 $40.00 Total Direct Labor Cost $65,800 $108,000 $58,000 $231,800 $231,800 Little Annin Flagmakers Manufacturing Overhead Budget(US$) April May June Quarter Budgeted direct labor hours 1,645 2,700 1,450 5,795 Variable MOHD rate $10 $10 $10 $10 Total variable MOHD $16,450 $27,000 $14,500 $57,950 Fixed MOHD expense $57,950 $57,950 $57,950 $173,850 Total MOHD expense $74,400 $84,950 $72,450 $231,800 Less: Depreciation $(20,000) $(20,000) $(20,000) $(60,000) Cash Disbursements for MOHD $54,400 $64,950 $52,450 $171,800 $171,800 MOHD rate (per DLH) $40.00 $40.00 Little Annin Flagmakers Unit Product Cost (US$) Absorption cost per unit Quantity Cost Cost/unit Direct materials 5 $7.00 $35.00 Direct labor 0.50 $40.00 $20.00 Manufacturing overhead 0.50 $40.00 $20.00 Unit Product Cost $75.00 $75.00 Little Annin Flagmakers Cost of Goods Sold Budget (USD) Cost of Goods Sold (FIFO) Units Cost/unit Total Cost Note Unit product cost found in two locations Beginning finished goods inventory 410 $75.00 $30,750 Add: Cost of goods manufactured 11,500 $75.00 $862,500 Good available for sale $893,250 $900,000 Less: Ending finished goods inventory $75.00 Cost of Good Sold Little Annin Flagmakers Selling and Administrative Expense Budget(US$) April May June Quarter Budgeted sales in units 2,500 6,000 3,000 11,500 Variable S&A per unit $1.25 $1.25 $1.25 $1.25 Total variable S&A $3,125 $7,500 $3,750 $14,375 Total fixed S&A $63,000 $63,000 $63,000 $189,000 Total S&A expense $66,125 $70,500 $66,750 $203,375 Less: Depreciation $10,000 $10,000 $10,000 $30,000 Cash Disbursements for S&A $56,125 $60,500 $56,750 $173,375 $173,375 Little Annin Flagmakers Cash Budget(US$) April May June Quarter Beginning Cash Balance $37,745 $30,000 $30,000 $97,745 Note Challenge: Desire ending balance. The way the quation works is, If we are short Add: Receipts relative to the desired ending cash balance, will borrow up to that amount, such as tha we Cash collections $252,000 $453,000 $540,000 $1,245,000 we end up the period with total of 30,000 on hand ** That will be a plug under financing - blue line Total Cash Available $289,745 $483,000 $570,000 $1,342,745 $1,282,745 The blue lines are going to say, how short are we. Let's top it up to get 30,000 Less disbursements Direct materials $118,420 $156,730 $130,200 $405,350 Direct labor $65,800 $108,000 $58,000 $231,800 Manufacturing overhead $54,400 $64,950 $52,450 $171,800 Selling and administrative 56,125 60,500 56,750 173,375 Dividends 15,000 15,000 15,000 45,000 Equipment purchases 47,820 154,600 202,420 Total Disbursements $309,745 $453,000 $467,000 $1,229,745 $1,229,745 Excess (deficiency) of cash available $(20,000) $30,000 $103,000 $113,000 Financing Borrowing Repayments Interest Total Financing Ending Cash Balance $51,000