Please help with the Cash Budget, Budgeted Income Statement, and Budgeted Balance Sheet. Please help with the excel cells with "XXXXXX". Thank you so much!
worked with accounting and other areas to gather the information assembled below. The concentration of sales before and during May is due to Mother's Day. Sufficient inventory should be on hand at the end of each month to supply 40% of the earrings sold in the following month. nonth of sale. An additional 70% is collected in the following month, and the remaining 10% is collected in the second month following sale. Bad debts have been negligib. Monthly operating expenses for the company are given below: of the following quarter. The company's balance sheet as of March 31 is given below: The company maintains a minimum cash balance of $50,000. All borrowing is done at the beginning of a month; any repayments are made at the end of a month. Required: Prepare a master budget for the three-month period ending June 30. Include the following detailed schedules: 1. a. A sales budget, by month and in total. b. A schedule of expected cash collections, by month and in total. c. A merchandise purchases budget in units and in dollars. Show the budget by month and in total. d. A schedule of expected cash disbursements for merchandise purchases, by month and in total. 2. A cash budget. Show the budget by month and in total. Determine any borrowing that would be needed to maintain the minimum cash balance of $50,000. 3. A budgeted income statement for the three-month period ending June 30. Use the contribution approach. 4. A budgeted balance sheet as of June 30 . Earrings Unlimited Cash Budget For the Three Months Ending June 30 Beginning cash balance Add collections from customers Total cash available Less cash disbursements: Merchandise purchases Advertising Rent Salaries Commissions (4\% of sales) Utilities Equipment purchases Dividends paid Total cash disbursements Excess (deficiency) of cash available over disbursements Financing: Borrowings Repayments Interest ($XXX,XXXX%X+$XX,XXXX%X) Total financing Ending cash balance \begin{tabular}{|c|c|c|c|} \hline$170,000 & XXXXX & XXXXXX & XXXX \\ \hline$50,000 & XXXXX & $94,700 & $94,700 \\ \hline \end{tabular} 3 Earrings Unlimited Budgeted Income Statement For the Three Months Ended June 30 Sales (see requirement 1a.) Variable expenses: Cost of goods sold (\$4 per unit) Commissions @ 4\% of sales Contribution margin \begin{tabular}{|c|c|} \hline$860,000 & \\ \hline$86,000 & $946,000 \\ \hline & $1,204,000 \\ \hline \end{tabular} Fixed expenses: Advertising ($XXX,XXXX) Rent ($XX,XXXX) Salaries ($XXX,XXXX) Utilities ($X,XXXX) Insurance ($X,XXXX) Depreciation ($XX,XXXX) Net operating income Interest expense (see requirement X ) Net income \begin{tabular}{|c|c|} \hline XXXXXX & \\ \hline XXXXX & \\ \hline XXXXXX & \\ \hline XXXXX & \\ \hline XXXX & \\ \hline XXXXX & XXXXXXX \\ \hline & $160,000 \\ & XXXX \\ \hline & XXXXXX \\ \hline \hline \end{tabular} 4 Earrings Unlimited Earrings Unlimited Budgeted Balance Sheet For the Three Months Ended June 30 Assets Cash (see requirement 2) Accounts receivable (see below) Inventory (12,000 units @ \$4 per unit) Prepaid insurance ($XX,XXX$X,XXX) Property and equipment, net ($XXX,XXX+$XX,XXX$XX,XXX) Total assets Liabilities and Stockholders' Equity Accounts payable, purchases (XX%$XXX,XXX) Dividends payable Common stock Retained earnings (see below) Total liabilities and stockholders' equity \begin{tabular}{|c|} \hline$94,700 \\ \hline$500,000 \\ $48,000 \\ \hlineXXXXX \\ \hlineXXXXXX \\ \hline$1,618,700 \\ \hline \hline \\ \hline$84,000 \\ \hline$15,000 \\ \hline$800,000 \\ \hlineXXXXXX \\ \hline$1,618,700 \\ \hline \hline \end{tabular} Accounts receivable at June XX : XX% May sales of $X,XXX,XXX XX% June sales of $XXX,XXX Total XXXXXX XXXXXX XXXXXX Retained earnings at June XX : Balance, March X 31 Add net income (see requirement X ) Total Less dividends declared Balance, June 30 XXXXXX
