Prepare your excel worksheet, as follows to support the tables in the Connect Ca
ID: 2567459 • Letter: P
Question
Prepare your excel worksheet, as follows to support the tables in the Connect Case 31: Beginning: open a new excel spreadsheet and create a section at the top where all of the assumptions are listed. I would shade the section with a different background, to distinguish it from the budget output. Assumptions Key assumptions begin with primary inputs: Opening balance sheet, if estimated Sales volumes in units Selling prices Target inventory stock levels Raw material needs for production required (commodity per unit) Manufacturing labor needs for production required (hours per unit) Cash conversion rates o How quickly are sales collected Cash needs from purchasing schedule o Anticipated credit with suppliers Other expense estimates o Those related to volume (sales commission rates) o Fixed expenses based on plant, other commitments o Depreciation Cash needs from other outlays (expenses excluding depreciation) Other significant investment o Equipment and property o R & D o Return of capital/dividends Borrowing terms Target Cash balance Once the assumptions are in, then there are nine tables to be completed. Don’t forget, assumptions include the prior ending balances of all balance sheet accounts and in some cases opening balances of more detailed areas such as RM stock and FG stock. Beginning AR and AP will factor into cash needed for purchases, and yielded from revenues. All of the table outputs will be used to provide a means of tracking actual activity in detail as compared to budget. However, it is important to remember that the critical outputs of the final budget, providing the voracity of the working model, will be the selfbalancing set of financial statement, which end balances in tables agree to. And the second most important and most-referred to table will be the summary of cash receipts and disbursement, ending balances by period. This table not only will be validating the amount of cash on the balance sheet, but justifying the level and timing of financing which will be needed. This, combination with the financial statements will likely be all that is needed to satisfy creditors, that the company has a credible plan. The tables: 1. Sales budget-units, priced to dollar volume The starting point for all tables will be the unit count sales. This will be used with expected pricing, to budget sales dollars. It is recommended this be done by product to establish mix and relative contribution. The amount of sales are really not useful without knowing how quickly cash will be realized. Estimated receipts, based on assumptions of collection rates will allow total cash to be received (don’t forget collection of opening AR) which carries to the cash flow. 2. Production budget, based on unit sales above Knowing unit sales, we can also combine this with starting inventory and desired stock levels to determine number of units which will need to be built during the period. Knowing units to be produced, will allow us to estimate materials, labor hours and general variable overhead supports to be required. 3. Raw materials budget, based on unit sales, adjusted for target stock level, which assumption of how much used per unit, then costed at estimated price Raw materials, based on the standard RM per unit to be produced is easily determined. Once this is known, and using desired RM stock levels, we can determine the RM which will need to be purchased by period. This multiplied by the known or standard RM price, will yield dollars purchased on account. An estimate of payment rate is used to determine cash payments for RM purchases by period (don’t forget payment of opening AP). Having modeled sales and collections, and purchases and payments, we also have what we need to model levels of AR and AP for the balance sheet. Also, inventory levels will be known as desired levels are modeled into the needs. 4. Direct labor budget based on production units above, using assumption for Hours per unit, then costed at assumed cost per hour (must adjust for salaried/minimum hourly workers.) Much like RM needed, labor hours are based on time per unit. However, minimum hours may not be achieved based on demand for build, while salary arrangements will require minimum payments. Conversely, a demand for labor hours in excess of available labor, may require additional hours requiring overtime premiums. 5. Manufacturing overhead budget, determine POHR based on estimated production and estimated MOH costs. Use of an accurate POHR, can suffice to estimate the OH to be incurred. 6. Selling and administrative expense budget These costs are usually somewhat fixed except for perhaps a commission component, and variables like the level of advertising and other marketing media planned. The costs are the input of the marketing department. The costs determined for labor, OH, Selling and Admin, are direct cash disbusements in each period, due to the quick payment expected for such expenditures. 7. Cash budget Cash budget line items should comprise roughly the following: Beginning cash balance Cash from collections Cash available Less Disbursements: Raw material payments Manufacturing labor Manufacturing OH costs Selling and administrative costs Capital outlays (equipment) Dividend payments Total disbursements Net cash available (deficit) Borrowing Line draw Line repayment Interest Net cash from (for) Borrowing Net cash balance 8. Income statement 9. Balance sheet/cash flow
After completing the tables to provide answers from your worksheet, submit you completed worksheet providing support for all your tables here. PLEASE PREPARE JUST THE EXCEL PORTION OF THE PROBLEM THE INFORMATION BELOW HAS BEEN FILLED OUT BUT IS PROVDED FOR REFERENCE
You have just been hired as a new management trainee by Earrings Unlimited, a distributor of earrings to various retail outlets located in shopping malls across the country. In the past, the company has done very little in the way of budgeting and at certain times of the year has experienced a shortage of cash.
Since you are well trained in budgeting, you have decided to prepare comprehensive budgets for the upcoming second quarter in order to show management the benefits that can be gained from an integrated budgeting program. To this end, you have worked with accounting and other areas to gather the information assembled below.
The company sells many styles of earrings, but all are sold for the same price—$10 per pair. Actual sales of earrings for the last three months and budgeted sales for the next six months follow (in pairs of earrings):
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.
Suppliers are paid $4 for a pair of earrings. One-half of a month’s purchases is paid for in the month of purchase; the other half is paid for in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 20% of a month’s sales are collected in the month 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 negligible.
The company plans to purchase $16,000 in new equipment during May and $40,000 in new equipment during June; both purchases will be for cash. The company declares dividends of $15,000 each quarter, payable in the first month of the following quarter.
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.
The company has an agreement with a bank that allows the company to borrow in increments of $1,000 at the beginning of each month. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $50,000 in cash.
A schedule of expected cash disbursements for merchandise purchases, by month and in total.
A cash budget. Show the budget by month and in total. (Cash deficiency, repayments and interest should be indicated by a minus sign.)
A budgeted income statement for the three-month period ending June 30. Use the contribution approach.
You have just been hired as a new management trainee by Earrings Unlimited, a distributor of earrings to various retail outlets located in shopping malls across the country. In the past, the company has done very little in the way of budgeting and at certain times of the year has experienced a shortage of cash.
Since you are well trained in budgeting, you have decided to prepare comprehensive budgets for the upcoming second quarter in order to show management the benefits that can be gained from an integrated budgeting program. To this end, you have worked with accounting and other areas to gather the information assembled below.
Explanation / Answer
Required budgets are as prepared below:
1a Earings unlimited Sales Budget For the quarter ended June 30 Month Particulars April May June Total Budgeted Unit sales 65,000 100,000 50,000 215,000 Sale Price 10 10 10 10 Budgeted sales 650,000 1,000,000 500,000 2,150,000 1b. Earings unlimited Schedule of expected Cash collections For the quarter ended June 30 Month Particulars April May June Total Beginning Accounts Receivable February sales (26,000*10*10%) 26,000 26,000 March sales (40,000*10*70%) 280,000 280,000 March sales (40,000*10*10%) 40,000 40,000 April Credit Sales 130,000 455,000 65,000 650,000 May Credit Sales 200,000 700,000 900,000 June Credit sales 100,000 100,000 Total collections 436,000 695,000 865,000 1,996,000 Account receivable for June Sale 400,000 Account receivable for May Sale 100,000 1c. Earings unlimited Merchandise Purchase Budget For the quarter ended June 30 Month Particulars April May June Total Budgeted Unit Sales 65,000 100,000 50,000 215,000 Add: Desired Ending merchandise inventory (40% of next month sales) 40,000 20,000 12,000 12,000 Total needs 105,000 120,000 62,000 227,000 Less: beginning merchandise inventory 26,000 40,000 20,000 26,000 Required purchase 79,000 80,000 42,000 201,000 Unit Cost 4.0 4.0 4.0 4.0 Required dollar purchases $316,000 $320,000 $168,000 $804,000 1d. Earings unlimited Schedule of expected Cash payments For the quarter ended June 30 Month Particulars April May June Total Beginning Accounts Payable (a) $100,000 $100,000 April Purchases (b) $158,000 $158,000 $316,000 May Purchases (c ) $160,000 $160,000 $320,000 June Purchases (d) $84,000 $84,000 Total payments (a+b+c+d) $258,000 $318,000 $244,000 $820,000 Earings unlimited Commission For the quarter ended June 30 Month Particulars April May June Total Budgeted Unit sales 65,000 100,000 50,000 215,000 Sale Price 10 10 10 10 Budgeted sales 650,000 1,000,000 500,000 2,150,000 Sales commisssions (4% of sales) 26,000 40,000 20,000 86,000 2 Earings unlimited Cash Budget For the quarter ended June 30 Month Particulars April May June Total Beginning Cash balance 74,000 50,000 50,000 74,000 Add: Collection from customers $436,000 $695,000 $865,000 $1,996,000 cash available for use $510,000 $745,000 $915,000 $2,070,000 Less: cash Disbursements Merchandise purchase $258,000 $318,000 $244,000 820,000 Advertising 200,000 200,000 200,000 600,000 Rent 18,000 18,000 18,000 54,000 Salaries 106,000 106,000 106,000 318,000 Commissions 26,000 40,000 20,000 86,000 Utilities 7,000 7,000 7,000 21,000 Equipment purchase 16,000 40,000 56,000 Dividend paid 15,000 15000 Total disbusrement 630,000 705,000 635,000 1,970,000 Cash surplus/Deficit -120,000 40,000 280,000 100,000 Financing Borrowing 170,000 10,000 180,000 Repayment -180,000 -180,000 Interest -3,500 -3,500 Net cash from Financing 170,000 10,000 -183,500 -3,500 Budgeted ending cash balance 50,000 50,000 96,500 96,500 3 Earings Unlimited Budgeted Income Statement For the three month ended June 30 Particulars Amount ($) Amount ($) Sales 2,150,000 Less: Cost of goods sold (215,000*4) 860,000 Variable expenses: Commissions 86,000 Interest expense 3,500 Insurance (3,000*3) 9,000 98,500 Contribution Margin 1,191,500 Fixed Expenses: Advertising 600,000 Rent 54,000 Salaries 318,000 Depreciation (14,000*3) 42,000 Utilities 21,000 1,035,000 Net operating Income 156,500 Dividend Paid 15,000 Net Income 141,500 4 Earings Unlimited Budgeted balance Sheet Jun-30 Assets Cash 96,500 Accounts Receivable 500,000 Inventory (12,000*4) 48,000 Property and equipment Net (950,000+16,000+40,000-42,000) 964,000 Prepaid insurance (21,000-9,000) 12,000 Total assets 1,620,500 Liabilities and Stockholders' Equity Accounts Payable purchases 84,000 Dividend payable 15,000 Common Stock 800,000 Retained earnings (580,000+141,500) 721,500 Total liabilities and stockholders' equity 1,620,500