Requirement A Complete the Master Budget (ALL parts plus schedules) in Excel. The budget information is provided in a separate document. To complete your Comprehensive Project 2 assignment, use the master budget example template. You may use the template provided in this workbook or the workbook used for the example completed in class. Using the example enables you to setup the budget in excel ensuring that you have formulas set up correctly and correctly calculated values. Your Comprehensive Project 2 Assignment will look exactly like the Royal Co. example with the exception of the budget specific standard numbers. You will simply need to change the appropriate numbers. These budget specific standard numbers and any other budget requirements will be provided in a separate document. There are some parts of the budget that will require you to make some decisions and/or understand how it relates to other/prior parts. The Cash Budget will require you to determine whether the company will need to borrow money in order to meet cash requirements. If you borrow money, you may need to pay some or all back with interest and you will have to calculate that interest. You will need to understand where how to calculate or where to get the numbers for the income statement and balance sheet. Read/Review all the information provided as well as chapter 22. This is not an assignment that can be successfully completed in a couple of hours the night it is due. Requirement B 1. What are the budgeted sales for February? 2. What are the expected cash collections for February? 3. What is the accounts receivable balance at the end of March? 4. What is the estimated cost of raw materials purchases for February? 5. What is the estimated accounts payable balance at the end of March? 6. What is the estimated finished goods inventory balance at the end of March? 7 What is the estimated cost of goods sold and gross margin for March? 8. What is the estimated total selling and administrative expense for February? 9. What is the estimated net operating income for March? 10. What is the estimated retained earnings balance for March? Part II (Chapter 25) Requirement C This question is assignment version specific. Please refer to the assignment document for further details. Requirement D This question is assignment version specific. Please refer to the assignment document for further details. Okay Co. Master Budget Input Section Sales Budget Expected unit sales in the first quarter January February March April May 8.700 6,000 8,300 5,300 6,200 Selling price $ 56 $ Cash Collections Beginning Balance Collected in month of sale Collected in the month following sale Uncollectible amount 60% 35% 5% Production Budget Des ending inventory Beginning Inventory % of next month's 20% sales 0 units Raw Materials Budget Raw materials per unit Cost per pound Desired ending inventory Beginning inventory 6 pounds $0.55 10% o pounds $ Cash payments Beginning balance Paid in month of purchase Paid in the month following purchase 60% 40% Direct Labor Budget Direct labor hours per unit Direct labor cost per hour 0.69 14.00 $ Manufacturing Overhead Budget Variable Manufacturing Overhead Rate $ Fixed Manufacturing Overhead Rate $ Depreciation 3.20 per hour 91,000.00 per month 15,000.00 per month $ Selling and Administrative Expense Budget Variable Selling & Admin Rate $ 1.10 per unit Fixed Selling & Admin Rate $ 62,000.00 per month Depreciation $ 8,000.00 per month Cash Budget Beginning balance Dividends Purchase of equipment Purchase of equipment Interest Rate $ 18,000.00 $ 34,000.00 first month $ 122,000.00 second month $ 110,000.00 third month 18% A B C D E F G Okay Co. Master Budget 2021 January February March April May 8,700 units 6,000 units 8,300 units 5,300 units 6,200 units SALES BUDGET Budgeted sales (units) Selling price per unit Total sales January February March 8,700 6,000 $ 56.00 $ 56.00 $ 487,200 336.000 8,300 56.00 $ 464,800 Quarter April 23,000 56.00 $ 1,288,000 May 5,300 56.00 $ 296,800 6,200 56.00 347,200 SCHEDULE OF EXPECTED CASH COLLECTIONS Quarter January $ February S March $ - $ 292,320 292,320 170,520 170,520 Accounts receivable beginning balance January sales January February February Sales February March March sales March T.A- T-A-...------ Instructions A. Input A Template 201,600 201,600 117,600 117,600 278,880 278,880 ASA LARRA A... - - - - - ARASA AAAAAAAA Parts B, C and D 278,880 396,480.00 S 278,880 1.060,920.00 S 292.320.00 $ 372,120.00 $ 26 March 27 Total cash collections 26 29 PRODUCTION BUDGET 30 31 32 6,200 33 January February 8,700 S 1.200,00 9,900 S 9,900 Budgeted sales Add desired ending inventory Total needs Less beginning inventory Required production March 6,000 1,660 7,660 1,200 6,460 Quarter April 23,000 1,060 24,060 8,300 1,060 9,360 1,660S 7,700 34 |May 5,300 1,240 6,540 1.060 5,480 35 36 24,060 37 36 DIRECT MATERIALS BUDGET 39 40 January 41 February 9,900 6 59,400 3,876 63,276 May 5,480 6 32.880 March 6,460 6 38,760 4,620 43,380 3,876 39,504 Required production in units 42 Raw materials per unit (pounds) 43 Production needs (pounds) Add desired ending inventory (pounds) Total needs (pounds) Less beginning inventory (pounds) Raw materials to be purchased (pounds) Cost of raw materials to be purchased at $0.40 per 48 pound Quarter April 24,060 6 144,360 2,740 147,100 44 7,700 6 46,200 2,740 48,940 4,620 s 44,320 45 46 S 47 63.276 147,100 34.802 21,727 24,376 80,905 49 50 SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIAL 51 March Quarter January $ February $ - $ $ 54 20,881 20,881 13.921 13,921 13,036 52 53 Accounts payable beginning balance January sales 55 January 56 February 57 February Sales 58 February March 60 March sales 61 March Total cash disbursements for materials 63 64 DIRECT LABOR BUDGET 65 13,036 8,691 59 8,691 14,626 23,316 14,626 71,155 62 20,881 26,957 66 January 67 Quarter 24,060 0.69 68 $ February 9,900 0.69 $ 6,831 14 95,634 Required production Direct labor-hours per unit Total direct labor-hours needed Direct labor cost per hour Total direct labor cost March 6,460 0.69 $ 4,457 14 62.404 69 70 7,700 0.69 $ 5,313 14 74,382 16,601 14 232,420 71 72 MANUFACTURING OVERHEAD BUDGET Budgeted direct labor-hours Variable manufacturing overhead rate Variable manufacturing overhead Fixed manufacturing overhead Total manufacturing overhead Less depreciation Cash disbursements for manufacturing overhead January February March 6,831 4.457 $ 3.20 $ 3.20 $ 21,859 14,264 91.000 91,000 112.859 105,264 15,000 15,000 $ 97,859.20 $ 90,263.68 $ 5,313 3.20 $ 17,002 91,000 108,002 15,000 93,001.60 $ Quarter 16,601 3.20 53,124 273,000 326,124 45,000 281.124.48 ENDING FINISHED GOODS INVENTORY BUDGET Computation of absorption unit product cost: $ Direct materials Direct labor Manufacturing overhead Unit product cost Quantity 6 pounds 0.69 hours 0.69 hours Cost 0.55 per pound 14 per hour 20 per hour* $ $ $ $ $ $ Total 3.30 9.66 13.55 26.51 Predetermined Overhead Rate = Total manufacturing overhead = 326,124 $ 19.64 Total direct labor hours 16,601 5 Budgeted ending finished goods inventory: 3. Ending finished goods inventory in units 2 Unit product cost o Ending finished goods inventory in dollars $ 1,060 26.51 28,105.51 $ 1 2 SELLING AND ADMINISTRATIVE EXPENSE BUDGET 3 4 January 5 6 $ February 8,700 1.10 $ 9,570 62,000 71,570 8,000 Budgeted sales in units Variable selling and administrative expense per unit 7 Variable selling and administrative expense Fixed selling and administrative expense Total selling and administrative expense Less depreciation Cash disbursements for selling and administrative expenses March 6,000 1.10 $ 6,600 62,000 68,600 8,000 8,300 1.10 $ 9,130 62,000 71,130 8,000 Quarter 23,000 1.10 25,300 186,000 211,300 24,000 8 9 0 1 $ 63,570.00 $ 60,600.00 $ 63,130.00 $ 187,300.00 CASH BUDGET 4 5 Okay Co. Cash Budget For the Quarter Ending May 31 5 7 3 January February March 30,376 Quarter 18,000 18,000 18,271 Cash balance, beginning Add receipts: Cash collections Total cash available 292,320 310,320 372.120 402.496 396,480 414,751 1,060,920 1,078,920 3 5 5 23,497 74,382 93,002 7 Less disbursements: Direct materials Direct labor Manufacturing overhead Selling & administrative Equipment purchases Dividends Total disbursements 20,881 95,634 97.859 63,570 122.000 3 26,957 62,404 90,264 60,600 110,000 34,000 384,224 63,130 71.155 232,420 281,124 187,300 232,000 34,000 1,038,179 e 399,944 254,011 2 Excess (deficiency) of cash available over B disbursements $ (89.624.00) $ 18,271.00 $ 160,740.00 $ 40,740.00 Financing: Borrowings Repayments $ $ 120,000.00 $ $ $ $ 7 $ (120,000.00) $ IE Ann Anil 120.000.00 (120,000.00) JEAAA NA Inter* d S 135 Financing: 136 Borrowings 137 Repayments 138 Interest 139 Total financing 140 141 Cash balance, ending 142 143 Interest $ S S 120,000.00 $ $ $ 50,000.00 $ S $ S $ $ (120,000.00) $ (5,400.00) $ (125,400.00 $ 120,000.00 (120,000.00) (5,400.00) (5,400.00) S 30,378.00 $ 18,271.00 $ 35,340.00 $ 35,340.00 $ 120,000.00 18% $ 0.25$ 5,400.00 144 145 146 BUDGETED INCOME STATEMENT 147 148 149 Okay CO. Budgeted Income Statement For the Quarter Ending May 31 150 151 152 153 154 Net sales Cost of goods sold Gross margin Selling & administrative expenses Net operating income Interest expense Net income Computation of net sales Sales Less uncollectible amounts Net sales 1,223,600 609,837 613.763 211,300 402,463 (2,250) 400,213 155 1,288,000 64,400 1,223,600 156 157 158 159 Computation of cost of goods sold Budgeted sales (units) Unit product cost Cost of goods sold 160 23,000 27 609,837 161 164 BEGINNING BALANCE SHEET (REQUIRED) (Based on information given, beginnin 165 166 167 Okay Co. Balance Sheet For the Quarter ending March 31 168 169 170 171 18,000 172 173 174 18,000 Current assets: Cash Accounts receivable Raw materials inventory Finished goods inventory Plant and equipment: Land Buildings and equipment Accumulated depreciation Total assets 175 176 300,000 162,000 177 178 179 180 181 182 183 Liabilities: Accounts payable Stockholders' equity: Common stock Retained earnings Total liabilities and stockholders' equity 184 480,000 185 186 187 188 189 Okay Co. Budgeted Balance Sheet May 31st 190 187 188 189 Okay Co. Budgeted Balance Sheet May 31st 190 191 192 193 194 38,671 162,680 1,507 28,106 195 196 230,964 Current assets: Cash Accounts receivable Raw materials inventory Finished goods inventory Plant and equipment: Land Buildings and equipment Accumulated depreciation Total assets 197 198 300,000 394,000 199 200 201 202 203 204 205 Liabilities: Accounts payable Stockholders' equity: Common stock Retained earnings Total liabilities and stockholders' equity 206 480,000 207 208 209 210 Requirement B 1. What are the budgeted sales for February? 2. What are the expected cash collections for February? 3. What is the accounts receivable balance at the end of March? 4. What is the estimated cost of raw materials purchases for February? 5. What is the estimated accounts payable balance at the end of March? 6. What is the estimated finished goods inventory balance at the end of March? 7 What is the estimated cost of goods sold and gross margin for March? 8. What is the estimated total selling and administrative expense for February? 9. What is the estimated net operating income for March? 10. What is the estimated retained earnings balance for March? 6,000 372, 120 278,880 21,727 23,316 28,106 1,223,600 68,600 402,463 Requirement C This question is assignment version specific. Please refer to the assignment document for further de Reference the DATA from your master budget. Alternate Solution 1 The profit impact is computed as follows: Units Decision Requirement D This question is assignment version specific. Please refer to the assignment document for further de Reference the DATA from your master budget. Make Buy Units Decision C. Assume that Okay expects to produce and sell 90,000 units during the current year. One of Okay's sales representatives has found a new customer that is willing to buy 8,000 additional units for a price of $43 per unit. If they accept the customer's offer, it will decrease unit sales to regular customers by 5,000 units. Should they accept this special order? Show all work/calculations to justify your decision. Label your work appropriately. If I can't understand it, I can't grade it. D. Assume that Okay expects to produce and sell 23,000 units during the current quarter. A supplier has offered to manufacture and deliver 23,000 units for a price of $25 per unit. Should Okay accept this offer? How much will profit's increase or decrease? Show all work/calculations to justify your decision. Label your work appropriately. If I can't understand it, I can't grade it