Question

You have just been hired as a new manager at Bracelets Unlimited. In the past the company has done very little in the way of budgeting. Since you are well rained in budgeting you have decided to prepare a comprehensive master budget for the upcoming year. You have worked with accounting and other areas of the company to compile the following information. The company sell its bracelets for $15 each. Actual sales for the last three months and budgeted sales for the next year follow: 2018 Actual Sales (Units) October 20,000 November 26,000 December 140,000 2019 Budgeted Sales (Units) 65,000 July January 20,000 February 100,000 August 50,000 50,000 September March 45,000 30,000 October April 30,000 May 28,000 November 20,000 25,000 December 147,000 June January-March 2020 bracelet sales are expected to increase 5% from the prior January-March sales. Sufficient inventory should be on hand at the end of each month to supply 40% of the bracelets sold 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. Direct materials for the bracelets consists of cording and beads. Each bracelet requires % yard of cording. The cording is purchased from the supplier for $7 per yard. Each bracelet also requires 24 beads. Beads are purchased in lots of 100 for $16 per lot. One-half of a month's purchases are paid for in the month of purchase; the other half is paid for in the following month. The company's policy is to have 20% of the next month's direct materials needs on hand at the end of each month. Bracelets Unlimited pays its bracelet assemblers $14 per hour. Fringe benefits are an additional 20%. Each bracelet requires 15 minutes to assemble. Manufacturing overhead consists of indirect materials of $0.10 per unit and indirect labor which is 5% of direct labor costs. Monthly operating expenses for the company are given below: Variable: 4% of sales Sales commissions Fixed: $50,000 Advertising 18,000 Rent 106,000 Salaries Utilities 7,000 Insurance 3,000 Depreciation 14,000 Insurance is paid on an annual basis, in November of each year. All other cash expenses are paid monthly. 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 accounts as of December 31, 2018 is given below: Assets Cash $74,000 346,000 Accounts receivable Raw materials inventory 88,322 Finished goods inventory 262,600 21,000 Prepaid insurance 950,000 Property and equipment (net) Total assets Liabilities and Stockholders' Equity $100,000 Accounts payable Interest payable Income tax payable Dividends payable 15,000 0 Note payable 800,000 Common stock Retained earnings 826,922 Total liabilities and stockholders' equity The accounts receivable balance is made up of $26,000 November sales and $320,000 December sales. The remaining November sales will be collected in January. The remaining December sales will be collected $300,000 in January and $20,000 in February. The accounts payable at year end relates to direct material purchases 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 at the beginning of each month. The interest rate on these loans is 1% per month and for simplicity we will assume interest is not compounded. At the end of the month, the company would pay the bank all the accumulated interest on the loan and as much of the loan as possible while still retaining at least $50,000 in cash The company's effective income tax rate is 30%. The company made quarterly estimated tax payments of $15,000 on April 15, June 15, September 15 and December 15. Budget Assignment Prepare the following budgets in Excel. Schedules need to be professionally formatted and use appropriate links and formulas. - Sales Budget . Production Budget Direct Materials Budget Direct Labor Budget . . Manufacturing Overhead Budget Operating Expense Budget . Budgeted Income Statement Budgeted Balance Sheet Cash Budget

Answer 1

1. Sales Budget

	January	February	March	April	May	June	July	August	September	October	November	December	Jan-20	Feb-20
Expected Sales Units	65000	100000	50000	30000	28000	25000	20000	50000	45000	30000	20000	147000	68250	105000
Unit Price	$               15.00	$                15.00	$                15.00	$                   15.00	$                15.00	$            15.00	$            15.00	$            15.00	$            15.00	$            15.00	$            15.00	$                15.00	$                15.00	$                15.00
Expected Sales	$    975,000.00	$ 1,500,000.00	$     750,000.00	$        450,000.00	$      420,000.00	$ 375,000.00	$ 300,000.00	$ 750,000.00	$ 675,000.00	$ 450,000.00	$ 300,000.00	$ 2,205,000.00	$ 1,023,750.00	$ 1,575,000.00

* Figures for Jan & Feb 20 provided for calculation of production and material purposes

2. Production Budget

	January	February	March	April	May	June	July	August	September	October	November	December	Jan-20	Feb-20
Expected Sales Units	65000	100000	50000	30000	28000	25000	20000	50000	45000	30000	20000	147000	68250	105000
Desired Ending Inventory	40000	20000	12000	11200	10000	8000	20000	18000	12000	8000	58800	27300	42000	0
Total Units Required	105000	120000	62000	41200	38000	33000	40000	68000	57000	38000	78800	174300	110250	105000
Less Opening Inventory	26000	40000	20000	12000	11200	10000	8000	20000	18000	12000	8000	58800	27300	42000
Total Production Required	79000	80000	42000	29200	26800	23000	32000	48000	39000	26000	70800	115500	82950	63000

3. Direct Material Budget

	January	February	March	April	May	June	July	August	September	October	November	December	Jan-20	Feb-20
Total Production Required	79000	80000	42000	29200	26800	23000	32000	48000	39000	26000	70800	115500	82950	63000
Cording
Yards per unit required	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25
Total Cording Required (Yards)	19750	20000	10500	7300	6700	5750	8000	12000	9750	6500	17700	28875	20737.5	15750
Desired Ending Inventory	4000	2100	1460	1340	1150	1600	2400	1950	1300	3540	5775	4147.5	3150	0
Total Units Required	23750	22100	11960	8640	7850	7350	10400	13950	11050	10040	23475	33022.5	23887.5	15750
Less Opening Inventory	3950	4000	2100	1460	1340	1150	1600	2400	1950	1300	3540	5775	4147.5	3150
Total Cording to be purchased	19800	18100	9860	7180	6510	6200	8800	11550	9100	8740	19935	27247.5	19740	12600
Unit Cost	$                 7.00	$                  7.00	$                  7.00	$                     7.00	$                   7.00	$               7.00	$               7.00	$               7.00	$               7.00	$               7.00	$               7.00	$                   7.00	$                   7.00	$                   7.00
Total Cording Cost	$    138,600.00	$     126,700.00	$        69,020.00	$          50,260.00	$        45,570.00	$    43,400.00	$    61,600.00	$    80,850.00	$    63,700.00	$    61,180.00	$ 139,545.00	$      190,732.50	$      138,180.00	$        88,200.00
Beads
Yards per unit required	24	24	24	24	24	24	24	24	24	24	24	24	24	24
Total Beads Required	1896000	1920000	1008000	700800	643200	552000	768000	1152000	936000	624000	1699200	2772000	1990800	1512000
Desired Ending Inventory	384000	201600	140160	128640	110400	153600	230400	187200	124800	339840	554400	398160	302400	0
Total Units Required	2280000	2121600	1148160	829440	753600	705600	998400	1339200	1060800	963840	2253600	3170160	2293200	1512000
Less Opening Inventory	379200	384000	201600	140160	128640	110400	153600	230400	187200	124800	339840	554400	398160	302400
Total Beads to be purchased	1900800	1737600	946560	689280	624960	595200	844800	1108800	873600	839040	1913760	2615760	1895040	1209600
Unit Cost	$                 0.16	$                  0.16	$                  0.16	$                     0.16	$                   0.16	$               0.16	$               0.16	$               0.16	$               0.16	$               0.16	$               0.16	$                   0.16	$                   0.16	$                   0.16
Total Beads Cost	$    304,128.00	$     278,016.00	$     151,449.60	$        110,284.80	$        99,993.60	$    95,232.00	$ 135,168.00	$ 177,408.00	$ 139,776.00	$ 134,246.40	$ 306,201.60	$      418,521.60	$      303,206.40	$      193,536.00
Total Material Cost	$    442,728.00	$     404,716.00	$     220,469.60	$        160,544.80	$      145,563.60	$ 138,632.00	$ 196,768.00	$ 258,258.00	$ 203,476.00	$ 195,426.40	$ 445,746.60	$      609,254.10	$      441,386.40	$      281,736.00

4. Direct labor Budget

	January	February	March	April	May	June	July	August	September	October	November	December
Total Production Required	79000	80000	42000	29200	26800	23000	32000	48000	39000	26000	70800	115500
Hours per unit	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25	0.25
Total Labor Hours	$      19,750.00	$       20,000.00	$        10,500.00	$             7,300.00	$          6,700.00	$      5,750.00	$      8,000.00	$    12,000.00	$      9,750.00	$      6,500.00	$    17,700.00	$        28,875.00
Labor rate per hour	$               16.80	$                16.80	$                16.80	$                   16.80	$                16.80	$            16.80	$            16.80	$            16.80	$            16.80	$            16.80	$            16.80	$                16.80
Total Labor	$    331,800.00	$     336,000.00	$     176,400.00	$        122,640.00	$      112,560.00	$    96,600.00	$ 134,400.00	$ 201,600.00	$ 163,800.00	$ 109,200.00	$ 297,360.00	$      485,100.00