Hello,

Can you show me how to calculate "Ending Finished Goods." I attempted to upload my file, but it was taking forever to upload. Some tutors who know the case study: "Cash is King: Master Budgets to inform a credit decision" have been able to help me even with scattered data. Thank you!!

Little Annin Flagmakers Cost of Goods Sold Budget (USD) Cost of Goods Sold(FIFO) Units Cost/Unit Total Cost Beg finished goods inv. 410 75 30750 Add: Cost of goods manuf. 11500 75 862500 Good available for sale 893250 Less: Ending finished goods 500 75 37500 855750

Yellow-use only cell references Little Annin Flagmakers Blue-may type numbers here Sales Budget (US$) Expected April May June Quarter 2,500 Budgeted sales (units) 2,500 6,000 3,000 11,500 6,000 Selling price per unit $120 $120 $120 $120 3,000 Total Sales $300,000 $720,000 $360,000 $1,380,000 $1,380,000 2,500 2,000 Little Annin Flagmakers $120.00 Schedule of Expected Cash Collections (US$) Notes April May June Quarter A/R March 31st = 132,000 Accounts receivable April's collection 300,000*.4 120000 40% Beginning balance $132,000 $132,000 Secon collection for April on May 132,000*.55 165000 55% 5% April sales $120,000 $165,000 $285,000 May sales $288,000 $396,000 $684,000 40% 60% June sales $144,000 $144,000 Total Cash Collections $252,000 $453,000 $540,000 $1,245,000 $1,245,000 5 $7.00 Accounts Receivable as of June 30 $198,000 $198,000 0.5 $40.00 Little Annin Flagmakers $10.00 Production Budget $57,950 April May June Quarter July August $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 Total needs 3,700 6,600 3,500 12,000 2,900 2,400 $1.25 Less: Beginning inventory 410 1,200 600 410 500 400 $63,000 Required Production 3,290 5,400 2,900 11,590 2,400 $10,000 11,590 Little Annin Flagmakers Direct Materials Budget (US$) $15,000 April May June Quarter Notes $47,820 Required production in units 3,290 5,400 2,900 11,590 Desired ending inv $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 Add: Desired ending inventory 10,800 5,800 4,800 4,800 June:2500*5*.4 = 4800 20% Total needs 27,250 32,800 19,300 62,750 40% Less: Beginning inventory 4600 10,800 5,800 4,600 $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 Total Cost of Raw Materials $158,550 $154,000 $94,500 $407,050 $407,050 $37,745 $132,000 $30,750 Little Annin Flagmakers $75.00 Schedule of Expected Cash Disbursements for Material (US$) 410 April May June Quarter $32,200 Accounts payable 4,600 Beginning balance $55,000 $55,000 $55,000 April purchases $63,420 $95,130 $158,550 $520,000 $1,800,000 May purchases $61,600 $92,400 $154,000 ($750,000) $500,000 June purchases $37,800 $37,800 $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 Little Annin Flagmakers Budgeted Income Statement(US$) Quarter Ending June 30 Net sales Less: Cost of goods sold Gross margin Less: S&A expenses Net operating income Less: Interest expense Net income $243,125 Computation of Net Sales Sales Less uncollectible amounts Net Sales $1,311,000 Little Annin Flagmakers Budgeted Balance Sheet (US$) Ending March 31 Ending June 30 Current assets Cash $37,745 Accounts receivable $132,000 Raw materials inventory Finished goods inventory $30,750 Plant and equipment 2320 Land $520,000 1570 Buildings and equipment $1,800,000 Accumulated depreciation $(750,000) Total Assets Liabilities Accounts payable $55,000 $55,000 Stockholder's equity Common stock $500,000 $500,000 Retained earnings $1,247,695 $1,747,695 Total Liabilities and Stockholder's Equity $1,802,695 $1,802,695 $2,002,520