Question

Part II (10 Points): Using the data input provided in Exhibit 1 and the attached Excel sheet template, prepare LAF's master budgets in Excel. Do not hard-code numbers into the spreadsheet, except in the financing section of the cash budget. Part III (2.5 Points): Prepare a one-page business report that includes a synopsis of the case and your credit decision for LAF. Include in your report your justification for the credit decision (hint: the importance of the cash budget when determining a credit decision and the financial position and performance from Pro forma financial statements). Exhibit 1: Excel Data Input Section Input Data (USD) А Other cash outflows Cash dividends paid each month Equipment purchases May Equipment purchases June US$15,000 US$47,820 US$154,600 1 2 3 4 5 6 7 8 9 10 Budgeted sales April (units) May (units) June (units) July (units) August (units) Expected 2,500 6,000 3,000 2,500 2,000 Desired ending inventory Finished Goods Raw Materials Cash (USS) 20% 40% US$30,000 Selling Price/unit US$120.00 11 Cash collection pattern Month of sale Following month Uncollectible 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 Beginning account balances-Mar. 31 Cash (USS) Accounts Receivable (USS) FG inventory (@US$75/unit) FG cost per unit FG inventory (units) Raw Materials (@US$7.00/1b) Raw Materials (lb) Accounts Payable (US$) US$37,745 US$132,000 US$30,750 US$75.00 410 US$32,200 4,600 US$55,000 Cash payinents for inaterials Month of purchase Following month 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 400 Production requirements Raw material per unit (lb) Raw mat. cost per lb Direct labor hours per unit Direct labor rate per hour Variable MOHD per DL hour Fixed MOHD per month Depreciation in Fixed MOHD Land Buildings and equipment Accumulated Depreciation Common stock Retained eamings US$520,000 US$1,800,000 (US$750,000) US$500,000 US$1,247,6951 US$7.00 0.5 US$40.00 US$10 US$57,950 US$20,000 Selling & adıninistrative costs Variable S&A cost per unit sold Fixed S&A cost per month Depreciation in Fixed S&A cost US$1.25 US$63,000 US$10,000

		Little Annin Flagmakers
		Cost of Goods Sold Budget (USD)
Cost of Goods Sold (FIFO)	Units	Cost/unit	Total Cost
Beg. FG inventory
Add: Cost of Goods Mfg'd
Good Available for Sale
Less: Ending FG inventory
Cost of Good Sold
		Little Annin Flagmakers
		Selling and Administrative Expense Budget  (USD)
	April	May	June	Quarter
Budgeted sales in units
Variable S&A per unit
Total Variable S&A
Total Fixed S&A
Total S&A Expense
Less: Depreciation
Cash Disbursements for S&A
		Little Annin Flagmakers
		Cash Budget  (USD)
	April	May	June	Quarter
Cash Balance, Beginning:
Add: Receipts
Cash Collections
Total Cash Available
Less Disbursements:
Direct Materials
Direct Labor
Manufacturing Overhead
Selling and Administrative
Dividends
Equipment Purchases
Total Disbursements
Excess (deficiency) of cash available:
Financing:
Borrowing
Repayments
Interest
Total Financing
Cash Balance, ending:
		Little Annin Flagmakers
		Budgeted Income Statement  (USD)
		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
Computation of Net Sales:
Sales:
Less uncollectible amounts:
Net Sales:
			Little Annin Flagmakers
			Budgeted Balance Sheet (USD)
		Ending March 31st		Ending June 30th
Current Assets:
Cash
Accounts Receivable
Raw Materials Inventory
Finished Goods Inventory
Plant and Equipment:
Land
Buildings and Equipment
Accumulated Depreciation
Total Assets:
Liabilities:
Accounts Payable
Stockholder's Equity:
Common Stock
Retained Earnings
Total Liabilities and stockholder's equity:

Answer 1

Answer is given below

Please note interest rate is not given in question for the short term borrowing. Hence interest is not taken into consideration while repayment. If you have interest rate please give to revise answer. please do not down vote for it. Answer is done as per details available in question

Sales Budget May April 2,500 6,000 Sales Units Budgeted Selling price Total Budgeted Sales June Quarter 3,000 11,500 $120 $120 $360,000 $1,380,000 $120 $1201 $720,000 $300,000 Total Cash receipts from Customers April May June Quarter $132,000 $120,000 Collection March Month April Month May Month June Month Total collection- AR Uncollectible Amount $165,000 $288,000 $132,000 $285,000 $396,000 $684,000 $144,000 $144,000 $540,000 $1,245,000 $18,000 $69,000 $252,000 $15,000 $453,000 $36,000 Production Budget May April 2,500 1,200 June 3,000 6,000 5001 Budgeted Sales Add: Closing inventory - (20% of next month sales) Total Requirement Less: Opening Inventory Production Units Closing stock of month will be opening stock of next month Quarter 11,500 500 12,000 500 11,500 600 6,600 1,200 5,400 3,700 July 2,500 400 2,900 500 2,400 500 3,200 3,5001 600 2,900 3 Raw Material purchase budget Quarter 57,500 4,800 62,300 Consumed for production-5 units per output Add : Closing inventory - (40% of next month prod requirement) Total Needs Less: Opening Inventory Purchase Budget (Units) Purchase price per unit Purchase budget in value April 16,000 10,800 26,800 4,6001 22,200 $7.00 $155,400 May 27,000 5,800 32,8001 10,800 22,000 $7.00 $154,000 June 14,500 4,800 19,300 5,8001 13,500 $7.00 $94,500 4,600 57,700 $7.00 $403,900 Expected Cash disbursements for Materials Purchases April May | June Quarter $55,000 $62,160 Cash payments for purchase March Purchase April Purchase May Purchase March Purchase Total purchase payment $93,240 $61,600 $92,400 $37,800 $130,200 $55,000 $155,400 $154,000 $37,800 $402,200 | $117,160 $154,840 4 Direct Labor Cost Budget Quarter April 3,200 0.50 1,600 $40 $64,000 $64,000 Production Direct labor hour per unit Direct Labor needed Direct Labor rate Direct Labor budget Direct labor cost budget May 5,4001 0.500 2,700 $40 $108,000 $108,000 June 2,900 0.50 1,450 $40 $58,000 $58,000 $230,000 $230,000 5 Manufacturing overhead budget April Quarter 3,200 Production Direct Labor hours needed Variable Overhead rate per hour Variable overheads Fixed Overheads Total Manufacturing Overhead Less: Depreciation Manufacturing overhead budget in Cash 1,600 $10.00 $16,000 $57,950 $73,950 $20,000 $53,950 May 5,400 2,700 $10.00 $27,000 $57,950 $84,950 $20,000 $64,950 June 2,900 1,450 $10.00 $14,500 $57,950 $72,450 $20,000 $52,450 5,750 $10.00 $57,500 $173,850 $231,350 $60,000 $171,350 2,500 Selling Expense Budget April Budgeted Sales in units Variable S & A per unit $1.25 Variable Selling and administration expenses $3,125 Fixed Selling and administration expenses $63,000 Total Selling and administration expenses $66,125 Less: Depreciation $10,000 Cash disbursements for S & A $56,125 May 6,000 $1.25 $7,500 $63,000 $70,500 $10,000 $60,500 June Quarter 3,000 11,500 $1.251 $1.25 $3,750 $14,375 $63,000 $189,000 $66,750 $203,375 $10,000 $30,000 $56,750 $173,375 Cost of Goods Sold Budget Cost of Goods Sold (FIFO) Beg FG Inventory Add: Cost of Goods manufactured Good available for Sale Less: Finished Goods Inventory Cost of Goods sold Units 410 11,500 Cost/Unit Total Cost $75.00 $30,750 $75.12| $863,850 $894,600 $75.12 $37,559 $857,041 500 LITTLE ANNIN FLAGMAKERS Cash budget April, May and June 2019 April $37,745 May $30,510 June $32,400 Quarter $37,745 $252,000 $289,745 $453,000 $483,510 $540,000 $1,245,000 $572,400 $1,282,745 Beginning cash balance Add : Cash receipts Cash Receipts from Customers Total cash available Cash payments for Raw Materials Direct labor Manufacturing Overhead Selling and administration expenses Dividends Purchases of Equipment Total cash payments Preliminary cash balance Financing Borrowing Repayment Interest $117,160 $64,000 $53,950 $56,125 $15,000 $154,840 $108,000 $64,950 $60,500 $15,000 $47,820 $451,110 $32,400 $130,200 $402,200 $58,000 $230,000 $52,450 $171,350 $56,750 $173,375 $15,000 $45,000 $154,600 $202,420 $467,000 $1,224,345 $105,400 $58,400 $306,235 -$16,490 $47,000 - $47,000 $47,000 $47,000 50 $47,0001 $30,510 $0 $32,400 -$47,000 $58,400 Ending cash balance $58,400 LITTLE ANNIN FLAGMAKERS Budgeted Income Statement For the three month Ended June 30, 2019 $1,311,000 $857,041 $453,959 Sales Cost of Goods Sold Gross Margin Operating expenses S & A Expenses Net Operating Income Less: Interest expense Net Income $203,375 $250,584 $250,584 Computation of Net Sales Sales Less: Uncollectible amount Net Sales $1,380,000 $69,000 $1,311,000 LITTLE ANNIN FLAGMAKERS Budgeted Balance Sheet June 30, 2019 $58,400 $198,000 $33,600 $37,559 Assets Cash Accounts receivable Raw Materials inventory Finished Goods Inventory Total Current Assets Fixed Assets Land Building and Equipment Purchase $327,559 $520,000 $1,800,000 $202,420 $2,002,420 $840,000 $1,162,420 $2,009,979 Less : Accumulated Depreciation Total Assets Liabilities Accounts payable $56,700 Stockholders' Equity Common stock Retained earnings Total Stockholders' Equity Total Liabilities and Equity $500,000 $1,453,279 $1,953,279 $2,009,979 Statement of Retained Earnings for June 30, 2019 Opening Balance Add: Net profit Less: Dividend paid Closing Balance $1,247,695 $250,584 -$45,000 $1,453,279 Accounts receivable closing balance Opening Balance Add : Sales Less: Collection Closing Balance $132,000 $1,311,000 -$1,245,000 $198,000 Accounts payable closing balance Opening Balance Add: Purchases Less : Payment Closing Balance $55,000 $403,900 -$402,200 $56,700