Question

i need 5-9 done please

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

Answer 1

5. Manufacturing overhead budget Jul Der Jan Feb Mar May Jun Aug Sep Oct Nov AP $0.10 5 % of Direct Labor Cost ted Production 2,600 $ Indirect Material 2.300 $ 4.800 5 7,900 В.о0р 4,200 2,920 2,680 3,200 7,080 11,550 Indirert Labot 16.590 S BA20 S 10,080 $ 8.190 S 5,460 14,868 S 16.800 6.132 5,628 4.830 6,720 24 255 9,052 8,308 7,130 $ 9,920 $ 14,880 12,090 $ 8,060 21,948 $ Total Manufacturing Cost 24,800 $ 13,020 $ 24,490 35,805 Total Manufacturing Cost Per unit Rate 0.31 6. Operating Expense Budget Feb Ар Jul Jan Mar May Jun Auc Sep Oct Nov Dec Variable 39,000 S 12,000 30,000 27,000 $ 18,000: 4 % ot Sale 18.000 $ 16,800 15,000 Sales Commissions 60.000 30.000 S 12,000 S 88,200 18.000 $ 16.800 $ 15.00o $ 12.000 s 30.000 Ss 27,000 S 18.000 $ 12.000 s Total Variable Operating Expense 39,000 S 30,000 S 88.200 60,000 Fixcd: 50,000 50,000 50,000 50,000 50,000 50,000 $ 50,000 50,000 $ $ 50,000 $ 50,000 S 50,000 Advertising 50,000 106,000 $106,000 $106.000 $ 105,000 7,000 7,000 7,000 $ 7,000 3.000 3,000 105,000 106,000 106.000 $ 105.0op s 7,000 7,000 3.000 3.co0 s 14,000 14,000 14,000 14,000 14,000 14,000 14,000 14,000 $ Salaries 106.000 106,000 S 106,000 106,000 S Utilities 7,000 7,000 5 7,000 7,000 7,000 7,000 3.000 $ 3,000 S Insurance 3,000 S 3,000 S 3,000 S 3.000 3.000 3.000 $ 14.000 S 14,000 14,000 Depreciation 14,000 Total Fixed Operating expense 198.000 $198,000 $198,000 $198,000 198.000 $ 198,000 S 198,000 $198,000 S 198,000 $ 198,000 $198,000 $ 198,000 $ 237,000 $ 258,000 $ 228,000 Total Operating Expense 216,000 $214,800 $213,000 $210,000 228,000 225,000 $ 216,000 $ 210,000 s 286,200 7. Budgeted Income Statement Per unit Cost Sales Revenue 9,150,000 Less: Cost of Goods Sold 610.000 U.25 S7 610,000 24 $0.16 Cording $1067,500 1./5 Beads $2,342,400 3.84 610,000*0.25*$16.80 610,000 0.31 Direct Labar $2,562,000 4.20 s 189.100 S 6,161,000 Manufacturing overheads 0.31 10.10 Le Operatine Expense S-2 742 000 Operating Income Less Inserest Гхвепке 247,000 -36.227 Income Before Tax 210,773 less: Income Tax 30% Net Income -63,232 147,541

8. Balance Sheet S 218,000 Cash ts Receivable Raw Material Inventory (4,148 $7)+ (398,160 0.16) 92.738 Finished Goods inventory 275,730 27,300$10.10 Prepaid Insurance S 21.000 Property and Equipment $950,000+$56,000-$168,000 838.000 Total Assets 3,239,468 304,627 Accounts Payable Interest Payable Income Tax Payable 3,232 bles et Bauble 1 207 14G $ 800,000 S 914,463 Common Stock Retained Earnlng $826 9224NI-Oividend Total Uabilities and Equity 3,239,468 S 9, Cash Flow Felb Jul Accounts Receivable Jan Мar Ap May Jun Auc Sep Oct Nov Dе $162,000 S 176,000 $190,000 $ 204,000 231,420 $445,221 $431,499 348,671 Beginning Balance 74,000 S 64,000 78,000 S 193,151 Nov Recelvable 26.000 s 300,000 5 s 195,000 Dec Receivable 2p.o00 Same month Sale 20 % 90.000 $ 84.000 $ 75,000 50,000 300,000 5 150,000 5 150,000 135,000 S 90,000 S0,000 S 441,000 Next Manth Sale 70% 525,000 315,000 $294,000 $ 262,500 210,000 525,000 S 472,500 $ 315,000 s 210,000 682,500 S1,050,000 S 1,764,000 97,500 S 150,000 75,000 45,000 $ 42,000 37,500 30,000 75,000 $ 67,500 S Next to Next 10% 45,000 S 30,000 $ 595,000 996,420 $919.221 $845,499 $713,171 Total Cash Available 1,066,500 $1,375,500 $ 590,651 852,000 B13,500 $632,50D $ 900,00D $ 1,794,000 Cash Payment for Material Accounts Payable 100,000 S 221,361 S Accounts Payable 80,272 72,782 69,316 98,384 110,235 0,272 72,782 59,316 g8,384 129,129 101.73 97,713 222,873 129,129 101,738 97,713 222,873 S 301,627 50% Same Month 202,358 110,235 S 50% Next Month 221,364 202,358 S 304.627 ** 14 830 1 090 a00 21 948 9.052 8.308 7.130 9.920 Qverheads 24.490 24.800 13,070 S a5 905. 211,000 208,000 199,000$ 229,000 269,200 $ 220,000 S 241,000 211,000 S 199,000 $197,800 $196,000 $193,000 $ 16.000 $ 40,000 Operating Expense Equipment s 15.000 $ 15,000 Dividend 15,000 15.000 s15.000 $ 15.000 Income Tax 15.000 15.000 $ 1,025,522 Total Cash Payment 912.654 551199 $487,722 $ 496,828 $ 520,020 654,993 629,757 $530,711 $ 868,895 $ 1,332,605 713,013 445,221 $131,499 $348,671 193,151 -61,312 $ 222,213 $ 282,789 $-236,391 $ -132,606 -317,654 S Excess/(Deficit) 40,978 662.487 Hinancing: 5 1B5,509 5 Barrowing/(Repayrment) 228,628 43,956 -90,942 445,789 41.290 S-426.799 652,627 -2,2B6 -1,847 $ -12,021 S-3.855.09 S 4.267.99 Interest -4,268 s-2.286.28 5.395 S Total Financing 226.342 46.242 -92.789 $ 440.394 $ 650.606 381,654 431,067 $ 37,022 78 ооn Ending Balance $ 431.499 $ 348.671 $ 193.151 162,000 176,000 190,000 204,0ou S 218.000 ь4,000 $ 231,420 S 445.221