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