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 trained 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) 20,000 October 26,000 November 140,000 December 2019 Budgeted Sales (Units) 65,000 July 20,000 January 100,000 August February 50,000 50,000 September March 45,000 30,000 October 30,000 April 28,000 November May 20,000 25,000 December June 147,000 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 hand at the end of each month. materialsneeds on Bracelets Unlimited pays its bracelet assemblers S14 per hour. Fringe benefits are an additional 20%. Each bracelet requires 15 minutes to assemble. Manufacturing overhead consists of indirect materials of $o.10 per unit and indirect labor which is 5% of direct labor costs. Monthly operating expenses for the company are given below Variable Sales commissions 4% of sales Fixed: Advertising $50,000 Rent 18,000 Salaries 106,000 Utilities 7,000 3,000 Insurance 14,000 Depreciation 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 Prepare the following budgets in Excel. Schedules need to be professionally formatted and use appropriate links and formulas. Sales Budget don . Production Budget don Direct Materials Budget done e Direct Labor Budget done e Manufacturing Overhead Budget 5 Operating Expense Budget 6 Budgeted Income Statement ? e Budgeted Balance Sheet 8 fee Cash Budget lel lal da 1 1500 5000 1500 5 1500 5 ExpectedSales S 975,00000 $ LSOM000 $ 7500000 450,000.00 S Fiucs for Jan& Feb 20 provided for calclation of production and matcrial parposes 420,00000 $ 375,000.00 $ 300,00000 $ 7900000 S 675,00000 $ 450,000.00 S 3000000 S 2.206,000.00 8 1,023.750.00 s 1,575,000.00 07375 .00 7 00 19800 Tetal Conding to be pchas 00 S 16 15500 16 80 6.30 5
