Prepare your excel worksheet, as follows to support the tables in the Connect Ca
ID: 2593667 • 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.
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):
100,000
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.
Monthly operating expenses for the company are given below:
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.
A listing of the company’s ledger accounts 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.
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
Requirement 1a: Sales Budget: April May June Quarter Sales in units 65000 100000 50000 215000 Sales in value 650000 1000000 500000 2150000 Requirement 1b: Schedule of Expected Cash Collections: April May June Quarter February 26000 26000 March 280000 40000 320000 April 130000 455000 65000 650000 May 200000 700000 900000 June 100000 100000 436000 695000 865000 1996000 Requirement 1c: Merchandise Purchases Budget: April May June Quarter Sales In units 65000 100000 50000 215000 Ending Inventory 40000 20000 12000 12000 Less: Beginning Inventory 26000 40000 20000 26000 Purchases in units 79000 80000 42000 201000 Purchases in dollars 316000 320000 168000 804000 Requirement 1d: Expected Cash Disbursements: April May June Quarter March 100000 100000 April 158000 158000 316000 May 160000 160000 320000 June 84000 84000 258000 318000 244000 820000