Hello, I need help with the bank credit line on the Budgeted Balance sheet, and to check if the rest of the balance sheet is correct all correct, Also some help and insight on how to do the rest of the statements
LOAN DETAILS LAF has requested a line of credit of US$60,000 to cover production costs during the seasonal increase in business. Kent Bank uses the following terms on its lines of credit. All borrowing is done at the beginning of the month in whole dollar increments. All repayments are made at the end of the month in whole dollar increments. The full line of credit is expected to be paid off by the end of the quarter with all the interest repaid at the end of the quarter. The interest rate on this loan is 16% per year. nenunen Input Data (all currency in USS) Yellow.use only cell references Blue-may type numbers here Budgeted Sales April (units) May (units) June (units) July (units August (units) Selling Price Unit April Expected 2,500 6,000 3,000 2,500 2,000 Little Annin Flagmakers Sales Budget (USS) May June Quarter 2,500 6,000 3,000 11,500 $120 $120 $120 $120 $ 300,000 $20,000 $360,000 $1,380,000 Budgeted sales (units) Selling price per unit Total Sales $120.00 Little Annin Flagmakers Schedule of Expected Cash Collections (USS) April May June $132,000 Quarter Accounts receivable Beginning balance 5132,000 40% 55% 596 $ 120,000 $ 165,000 5 285,000 Cash Collection Pattern 3 Month of sale Following month Uncollectible Cash Payments for Materials 3 Month of purchase Following month April sales May sales $285,000 $ 396,000 S 684,000 4096 60% June sales $ 144.000 $ 144,000 Total Cash Collections 5252,000 5 453,000 $ 540,000 51,245,000 Accounts Receivable as of June 30 $ 195,000 Production Requirements Raw material per unit (lb) Raw material cost per lb. Direct labor hours per unit Direct labor rate per hour 3 Variable MOHD per direct labor hour Fixed MOHD per month 3 Depreciation in fixed MOHD Selling & Administrative (S&A) Costs Variable S&A cost per unit sold Fixed S&A cost per month Depreciation in fixed S&A cost 5 $7.00 03 $40.00 $10.00 $57,950 $20,000 August 2.000 Budgeted sales Add: Desired ending inventory Total needs Less: Beginning inventory Required Production Little Annin Flagmakers Production Budget April May 2,500 6,000 1,200 600 3.700 6,600 410 1,200 3,290 5,400 June 3.000 500 3,500 600 2.900 Quarter 11,500 500 12,000 410 11,590 July 2,500 400 2.900 500 2.400 $125 $63,000 $10,000 E G H F 1.200 31 Variable S&A cost per unit sold 32 Fixed S&A cost per month 33 Depreciation in fixed S&A cost B $1.25 $63,000 $10,000 Less: Beginning inventory Required Production 410 3,290 600 2,900 5,400 1 500 2,400 410 11,590 $15,000 $47,820 $154,600 35 Other Cash Outflows 36 Cash dividends paid each month 37 Equipment purchases May 38 Equipment purchases June 39 40 Desired Ending Inventory 41 Finished goods 42 Raw materials 43 Cash Little Annin Flagmakers Direct Materials Budget (USS) ) April May June Required production in units 3,290 5,400 2,900 Raw materials per unit (lbs.) Production needs (lbs.) 28 16,450 27,000 14,500 Add: Desired ending inventory 10.800 5.800 4.800 Total needs 27,250 32,800 19,300 Less: Beginning inventory 4,600 10.800 5,800 Raw materials to be purchased 22,650 22,000 13,500 Cost of raw materials $7.00 $7.00 $7.00 Total Cost of Raw Materials $ 158,550 $ 154,000 $ 94,500 $ Check figure for April Cost of Raw Materials $158,550 Quarter 11,590 5 57,950 4.800 62,750 4,600 58,150 $7.00 407,050 2098 40% $30,000 Little Annin Flagmakers Schedule of Expected Cash Disbursements for Material (USS) April May June Quarter 45 Beginning Account Balances as of March 31 46 Cash $37,745 47 Accounts receivable $132,000 48 Finished goods inventory $30,750 49 Finished goods cost per unit $75.00 50 Finished goods inventory (units) 410 51 Raw materials inventory $32.200 52 Raw materials (Ib.) 4,600 53 Accounts payable $55,000 54 55 Land $520.000 56 Buildings and equipment $1,800,000 57 Accumulated depreciation (5750,000) 58 Common stock $500.000 $ 59 Retained earnings $1,247,695 Accounts payable Beginning balance $55,000 $55,000 April purchases $ 63,420 $ 95,130 $ 158,550 May purchases $ 61.600 $ 92.400 S 154,000 June purchases $ 37,800 $ 37,800 Total Cash Disbursements for Materials $118,420 $ 156,7305 130,200 $405,350 Accounts Payable as of June 30 $ 56,700 60 61 62 63 64 65 66 67 68 69 70 71 Units to be produced Direct labor hours per unit Total direct labor hours needed Direct labor cost per hour Total Direct Labor Cost Little Annin Flagmakers Direct Labor Budget (USS) April May 3.290 5.400 0.5 0.5 1.645 2.700 $40.00 $40.00 65,800 $ 108,000 $ June 2,900 0.5 1,450 $40.00 58,000 $ Quarter 11,590 0.5 5,795 $40.00 231,800 72 S Budgeted direct labor hours Variable MOHD rate Total variable MOHD Fixed MOHD expense Total MOHD expense Less: Depreciation Cash Disbursements for MOHD Little Annin Flagmakers Manufacturing Overhead Budget (USS) April May June Quarter 1,645 2,700 1,450 5,795 $10 $10 $10 $10 $ 16,450 $ 27,000 $ 14,500 5 57,950 $ 57,950 $ 57,950 $ 57,950 $ 173,850 74,400 $ 84,950 $ 72,450 $ 231,800 $ 20,000 $ 20,000 $ 20,000 $ 60,000 $ 54,400 $ 64,950 $ 52,450 $ 171,800 $52,450 Check Figure for June Cash Disbursements for MOHD $40.00 direct labor hour ll MOHD rate Absorption cost per unit Direct materials Direct labor Manufacturing overhead Unit Product Cost Little Annin Flagmakers Unit Product Cost (USS) Quantity Cost Cost unit 5 $7.00 $35.00 0.50 $40.00 $20.00 0.50 $40.00 $20.00 $75.00 Cost of Goods Sold (FIFO) Beginning finished goods inventory Add: Cost of goods manufactured Good available for sale Less: Ending finished goods inventory Cost of Good Sold Little Annin Flagmakers Cost of Goods Sold Budget (USD) Units Cost/unit Total Cost 410 $75.00 $ 30,750 11,500 $75.00 $ 862,500 'S 893,250 500 $75.00 $ 37,500 S 855,750 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 Selling and Administrative Expense Budget (USS) April May June 2,500 6,000 3,000 $1.25 $1.25 $1.25 $ 3,125 5 7,500 $ 3,750 $ S 63,000 $ 63,000 $ 63,000 $ $ 66,125 5 70,500 $ 66,750 $ $ 10,000 $ 10,000 $ 10,000 $ $ 56,125 $ 60,500 $ 36,750 $ Quarter 11,500 $1.25 14,375 189,000 203,375 30,000 173,375 DOO Cash Budget April $37,745 May $30,000 June $30,000 Quarter $37,745 Particulars Beginning cash balance Add: Receipts Cash collections Total cash available $252,000 $289,745 $453,000 $483,000 $540,000 $570,000 $1,245,000 $1,282,745 Less: Disbursements Payments for materials Payments for labor Payments for manufacturing overhead Payments for selling and administrative expense Dividends Equipment purchases Total disbursements $118,420 $65,800 $54,400 $56,125 $15,000 $156,730 $108,000 $64,950 $60,500 $15,000 $47,820 $453,000 $130,200 $58,000 $52.450 $56.750 $15,000 $154,600 $467,000 $405,350 $231,800 $171,800 $173,375 $45,000 $202,420 $1,229,745 $309,745 Excess (Deficiency) of cash ($20,000) $30,000 $103,000 $53,000 Financing Beginning of month loan balance Borrowing during month ($30,000 + $20,000) Subtotal due before interest calculations Interest accrual at month end Loan balance before loan repayment Loan repayment Total loan balance at month end SO $50.000 $50,000 $667 $50,667 SO $50.667 $50,667 SO $50,667 $667 $51,333 SO $51.333 $51,333 SO $51,333 5667 $52,000 ($52,000) SO SO $50,000 $50,000 $2.000 $52,000 ($52,000) SO Ending cash balance $30,000 $30,000 $51,000 $51,000 Schedule 13 Little Annin Flagmakers Budgeted Balance Sheet ($) Ending March 31 Ending June 30 Current assets Cash Accounts Receivable Raw Material (note 1) Finished goods inventory $ 37,745 $ 51,000 $ 132,000 $198,000 $ 32,200 $ 33,600 $ 30,750 $ 232,695 $ 37,500 $ 320,100 Plant and equipment Land Building and equipment Accumulated depreciation (note 2) Total Assets $ 520,000 $ 520,000 $1,800,000 $ 1,800,000 $ (750,000) $1,570,000 $ (660,000) $ 1,660,000 $1,802,695 $ 1,980,100 $ 56,700 Liabilities Accounts Payable Bank credit line Total Liabilities Stockholders equity Common stock Retained earnings $ 55,000 0 $ 55,000 $ 56,700 $ 500,000 $ 500,000 $1,247,695 $1,747,695 $ 1,452,570 $ 1,952,570 Total liabilities and stock holders equity $1,857,695 $ 2,009,270 Cash Flow Statement (indirect method): Budgeted Cash Flow Statement (indirect method) Little Annin Flagmakers For the Quarter Ending June 30 Cash Flow from Operations: Net Income: Add Back Depreciation Property, Plant, and Equipment Depreciation (month x 3) Selling and Administrative Depreciation (month x 3) Increase in Accounts Receivable Decrease in Raw Material Inventory Increase in Finished Goods Inventory Decrease in Accounts Payable Increase in Bank Credit Line Cash flow from Operations: Cash Flow from Investing Activities Purchased Equipment (May) Purchased Equipment (June) Cash Flow from Investing Activities Cash Flow from Financing Activities Paid Dividends (month x 3) Net Change in Cash Beginning Cash Balance Ending Cash Balance Break-Even Point, Margin of Safety, and Percent Drop to Break-Even Break-Even Point Monthly basis Sales Price per Unit Variable Raw Materials per Unit Variable Direct Labor per Unit Variable MOHD per Unit Variable S&A cost per Unit Total Variable Costs Contribution Margin Fixed MOHD per Month Fixed S&A Cost per Month Total Fixed Costs Monthly Break-Even Point in Units Monthly Break-Even Point in Sales Dollars Quarterly Break-Even Point in Sales Dollars Margin of Safety Budgeted Quarterly Net Sales ($) (Reduce sales by 5% uncollectable) Quarterly Break-Even Point in Sales Dollars Margin of Safety Percentage Drop to Break-Even for the Quarter Margin of Safety Budgeted Quarterly Net Sales ($) Percentage Drop to Break-Even LOAN DETAILS LAF has requested a line of credit of US$60,000 to cover production costs during the seasonal increase in business. Kent Bank uses the following terms on its lines of credit. All borrowing is done at the beginning of the month in whole dollar increments. All repayments are made at the end of the month in whole dollar increments. The full line of credit is expected to be paid off by the end of the quarter with all the interest repaid at the end of the quarter. The interest rate on this loan is 16% per year. nenunen Input Data (all currency in USS) Yellow.use only cell references Blue-may type numbers here Budgeted Sales April (units) May (units) June (units) July (units August (units) Selling Price Unit April Expected 2,500 6,000 3,000 2,500 2,000 Little Annin Flagmakers Sales Budget (USS) May June Quarter 2,500 6,000 3,000 11,500 $120 $120 $120 $120 $ 300,000 $20,000 $360,000 $1,380,000 Budgeted sales (units) Selling price per unit Total Sales $120.00 Little Annin Flagmakers Schedule of Expected Cash Collections (USS) April May June $132,000 Quarter Accounts receivable Beginning balance 5132,000 40% 55% 596 $ 120,000 $ 165,000 5 285,000 Cash Collection Pattern 3 Month of sale Following month Uncollectible Cash Payments for Materials 3 Month of purchase Following month April sales May sales $285,000 $ 396,000 S 684,000 4096 60% June sales $ 144.000 $ 144,000 Total Cash Collections 5252,000 5 453,000 $ 540,000 51,245,000 Accounts Receivable as of June 30 $ 195,000 Production Requirements Raw material per unit (lb) Raw material cost per lb. Direct labor hours per unit Direct labor rate per hour 3 Variable MOHD per direct labor hour Fixed MOHD per month 3 Depreciation in fixed MOHD Selling & Administrative (S&A) Costs Variable S&A cost per unit sold Fixed S&A cost per month Depreciation in fixed S&A cost 5 $7.00 03 $40.00 $10.00 $57,950 $20,000 August 2.000 Budgeted sales Add: Desired ending inventory Total needs Less: Beginning inventory Required Production Little Annin Flagmakers Production Budget April May 2,500 6,000 1,200 600 3.700 6,600 410 1,200 3,290 5,400 June 3.000 500 3,500 600 2.900 Quarter 11,500 500 12,000 410 11,590 July 2,500 400 2.900 500 2.400 $125 $63,000 $10,000 E G H F 1.200 31 Variable S&A cost per unit sold 32 Fixed S&A cost per month 33 Depreciation in fixed S&A cost B $1.25 $63,000 $10,000 Less: Beginning inventory Required Production 410 3,290 600 2,900 5,400 1 500 2,400 410 11,590 $15,000 $47,820 $154,600 35 Other Cash Outflows 36 Cash dividends paid each month 37 Equipment purchases May 38 Equipment purchases June 39 40 Desired Ending Inventory 41 Finished goods 42 Raw materials 43 Cash Little Annin Flagmakers Direct Materials Budget (USS) ) April May June Required production in units 3,290 5,400 2,900 Raw materials per unit (lbs.) Production needs (lbs.) 28 16,450 27,000 14,500 Add: Desired ending inventory 10.800 5.800 4.800 Total needs 27,250 32,800 19,300 Less: Beginning inventory 4,600 10.800 5,800 Raw materials to be purchased 22,650 22,000 13,500 Cost of raw materials $7.00 $7.00 $7.00 Total Cost of Raw Materials $ 158,550 $ 154,000 $ 94,500 $ Check figure for April Cost of Raw Materials $158,550 Quarter 11,590 5 57,950 4.800 62,750 4,600 58,150 $7.00 407,050 2098 40% $30,000 Little Annin Flagmakers Schedule of Expected Cash Disbursements for Material (USS) April May June Quarter 45 Beginning Account Balances as of March 31 46 Cash $37,745 47 Accounts receivable $132,000 48 Finished goods inventory $30,750 49 Finished goods cost per unit $75.00 50 Finished goods inventory (units) 410 51 Raw materials inventory $32.200 52 Raw materials (Ib.) 4,600 53 Accounts payable $55,000 54 55 Land $520.000 56 Buildings and equipment $1,800,000 57 Accumulated depreciation (5750,000) 58 Common stock $500.000 $ 59 Retained earnings $1,247,695 Accounts payable Beginning balance $55,000 $55,000 April purchases $ 63,420 $ 95,130 $ 158,550 May purchases $ 61.600 $ 92.400 S 154,000 June purchases $ 37,800 $ 37,800 Total Cash Disbursements for Materials $118,420 $ 156,7305 130,200 $405,350 Accounts Payable as of June 30 $ 56,700 60 61 62 63 64 65 66 67 68 69 70 71 Units to be produced Direct labor hours per unit Total direct labor hours needed Direct labor cost per hour Total Direct Labor Cost Little Annin Flagmakers Direct Labor Budget (USS) April May 3.290 5.400 0.5 0.5 1.645 2.700 $40.00 $40.00 65,800 $ 108,000 $ June 2,900 0.5 1,450 $40.00 58,000 $ Quarter 11,590 0.5 5,795 $40.00 231,800 72 S Budgeted direct labor hours Variable MOHD rate Total variable MOHD Fixed MOHD expense Total MOHD expense Less: Depreciation Cash Disbursements for MOHD Little Annin Flagmakers Manufacturing Overhead Budget (USS) April May June Quarter 1,645 2,700 1,450 5,795 $10 $10 $10 $10 $ 16,450 $ 27,000 $ 14,500 5 57,950 $ 57,950 $ 57,950 $ 57,950 $ 173,850 74,400 $ 84,950 $ 72,450 $ 231,800 $ 20,000 $ 20,000 $ 20,000 $ 60,000 $ 54,400 $ 64,950 $ 52,450 $ 171,800 $52,450 Check Figure for June Cash Disbursements for MOHD $40.00 direct labor hour ll MOHD rate Absorption cost per unit Direct materials Direct labor Manufacturing overhead Unit Product Cost Little Annin Flagmakers Unit Product Cost (USS) Quantity Cost Cost unit 5 $7.00 $35.00 0.50 $40.00 $20.00 0.50 $40.00 $20.00 $75.00 Cost of Goods Sold (FIFO) Beginning finished goods inventory Add: Cost of goods manufactured Good available for sale Less: Ending finished goods inventory Cost of Good Sold Little Annin Flagmakers Cost of Goods Sold Budget (USD) Units Cost/unit Total Cost 410 $75.00 $ 30,750 11,500 $75.00 $ 862,500 'S 893,250 500 $75.00 $ 37,500 S 855,750 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 Selling and Administrative Expense Budget (USS) April May June 2,500 6,000 3,000 $1.25 $1.25 $1.25 $ 3,125 5 7,500 $ 3,750 $ S 63,000 $ 63,000 $ 63,000 $ $ 66,125 5 70,500 $ 66,750 $ $ 10,000 $ 10,000 $ 10,000 $ $ 56,125 $ 60,500 $ 36,750 $ Quarter 11,500 $1.25 14,375 189,000 203,375 30,000 173,375 DOO Cash Budget April $37,745 May $30,000 June $30,000 Quarter $37,745 Particulars Beginning cash balance Add: Receipts Cash collections Total cash available $252,000 $289,745 $453,000 $483,000 $540,000 $570,000 $1,245,000 $1,282,745 Less: Disbursements Payments for materials Payments for labor Payments for manufacturing overhead Payments for selling and administrative expense Dividends Equipment purchases Total disbursements $118,420 $65,800 $54,400 $56,125 $15,000 $156,730 $108,000 $64,950 $60,500 $15,000 $47,820 $453,000 $130,200 $58,000 $52.450 $56.750 $15,000 $154,600 $467,000 $405,350 $231,800 $171,800 $173,375 $45,000 $202,420 $1,229,745 $309,745 Excess (Deficiency) of cash ($20,000) $30,000 $103,000 $53,000 Financing Beginning of month loan balance Borrowing during month ($30,000 + $20,000) Subtotal due before interest calculations Interest accrual at month end Loan balance before loan repayment Loan repayment Total loan balance at month end SO $50.000 $50,000 $667 $50,667 SO $50.667 $50,667 SO $50,667 $667 $51,333 SO $51.333 $51,333 SO $51,333 5667 $52,000 ($52,000) SO SO $50,000 $50,000 $2.000 $52,000 ($52,000) SO Ending cash balance $30,000 $30,000 $51,000 $51,000 Schedule 13 Little Annin Flagmakers Budgeted Balance Sheet ($) Ending March 31 Ending June 30 Current assets Cash Accounts Receivable Raw Material (note 1) Finished goods inventory $ 37,745 $ 51,000 $ 132,000 $198,000 $ 32,200 $ 33,600 $ 30,750 $ 232,695 $ 37,500 $ 320,100 Plant and equipment Land Building and equipment Accumulated depreciation (note 2) Total Assets $ 520,000 $ 520,000 $1,800,000 $ 1,800,000 $ (750,000) $1,570,000 $ (660,000) $ 1,660,000 $1,802,695 $ 1,980,100 $ 56,700 Liabilities Accounts Payable Bank credit line Total Liabilities Stockholders equity Common stock Retained earnings $ 55,000 0 $ 55,000 $ 56,700 $ 500,000 $ 500,000 $1,247,695 $1,747,695 $ 1,452,570 $ 1,952,570 Total liabilities and stock holders equity $1,857,695 $ 2,009,270 Cash Flow Statement (indirect method): Budgeted Cash Flow Statement (indirect method) Little Annin Flagmakers For the Quarter Ending June 30 Cash Flow from Operations: Net Income: Add Back Depreciation Property, Plant, and Equipment Depreciation (month x 3) Selling and Administrative Depreciation (month x 3) Increase in Accounts Receivable Decrease in Raw Material Inventory Increase in Finished Goods Inventory Decrease in Accounts Payable Increase in Bank Credit Line Cash flow from Operations: Cash Flow from Investing Activities Purchased Equipment (May) Purchased Equipment (June) Cash Flow from Investing Activities Cash Flow from Financing Activities Paid Dividends (month x 3) Net Change in Cash Beginning Cash Balance Ending Cash Balance Break-Even Point, Margin of Safety, and Percent Drop to Break-Even Break-Even Point Monthly basis Sales Price per Unit Variable Raw Materials per Unit Variable Direct Labor per Unit Variable MOHD per Unit Variable S&A cost per Unit Total Variable Costs Contribution Margin Fixed MOHD per Month Fixed S&A Cost per Month Total Fixed Costs Monthly Break-Even Point in Units Monthly Break-Even Point in Sales Dollars Quarterly Break-Even Point in Sales Dollars Margin of Safety Budgeted Quarterly Net Sales ($) (Reduce sales by 5% uncollectable) Quarterly Break-Even Point in Sales Dollars Margin of Safety Percentage Drop to Break-Even for the Quarter Margin of Safety Budgeted Quarterly Net Sales ($) Percentage Drop to Break-Even
