50.1K

Verified Solution

Link Copied!

Prepare a budgeted income statement for the coming JuneAugust quarter, a cash budget for each of the 3 months, and a budgeted balance sheet for August 31, 20X8. All operations are evaluated on a before-income-tax basis, so income taxes may be ignored here. Must be done in Microsoft Excel. Can you guys include the formulas for excel please.

In January, Flying Fish Kite is beginning just-in-time (JIT) deliveries from suppliers, which means that purchases will equal expected sales. On January 1, purchases will cease until inventory decreases to $22,000, after which time purchases will equal sales. Purchases during any given month are paid in full during the following month. Monthly operating expenses are as follows: Wages and salaries $80,000 Insurance expired 450 Depreciation 900 Miscellaneous 4,000 Rent $500/month + 5% of quarterly sales over $50,000 Cash dividends of $2,400 are to be paid quarterly, beginning January 15, and are declared on the fifteenth of the previous month. All operating expenses are paid as incurred, except insurance, depreciation, and rent. Rent of $500 is paid at the beginning of each month, and the additional 5% of sales is settled quarterly on the tenth of the month following the end of the quarter. The next rent settlement date is January 10. The company plans to buy some new fixtures for $4,000 cash in March. Money can be borrowed and repaid in multiples of $2,000. Management wants to minimize borrowing and repay rapidly. Simple interest of 9% per annum is computed monthly but paid when the principal is repaid. Assume that borrowing occurs at the beginning, and repayments at the end, of the months in question. Compute interest to the nearest dollar. $151,500 Assets as of December 31, 20X1 Cash Accounts receivable Inventory* Unexpired insurance Fixed assets, net $ 30,000 180,600 153,000 5,400 62,000 $431,000 Liabilities and Owners' Equities as of December 31, 20X1 Accounts payable (merchandise) Dividends payable Rent payable Owners' equity 2,400 27,950 249,150 $431,000 *November 30 inventory balance = $59,000. Recent and forecasted sales: October $280,000 December November 168,000 January April $280,000 $161,000 February 378,000 March $413,000 273,000 1. Prepare a master budget including a budgeted income statement, balance sheet, cash budget, and supporting schedules for the months January-March 20x2. 2. Explain why there is a need for a bank loan and what operating sources provide the cash for the repayment of the bank loan

Answer & Explanation Solved by verified expert

See Answer