I did the first half of this, can I recieve help with the second half of this?

This is the beginning of the first part that was not provided. I'm providing it now.

INPUT SECTION SALES 4th Quarter 2020 1st Quarter 2nd Quarter 2021 48,100 3rd 4th Quarter Quarter 2021 2021 34,500 31,000 2021 43,000 Budgeted Sales in units 41,000 Budgeted Selling Price $ 530 per production unit (Finished Goods) RECEIVABLES Receivables Collection Schedule 83.00% quarter of sale 15.00% quarter following sale 2.00% uncollectible 100.00% Entire projected uncollectible receivables are written off each quarter Policy INVENTORY COSTS Direct Labor 4.5 hours 18.00 per direct labor hour $ Raw Materials 3 pounds per finished goods unit 85.00 per pound $ Variable Overhead $ 10.50 per Direct Labor Hour Fixed Overhead Depreciation $ Other Fixed Overhead $ 195,000 per quarter 950,000 per quarter Fixed Overhead Application Rate CALCULATE FROM PRODUCTION BUDGET INVENTORY ACCOUNTS 1 Production Budget 2 Projected for Year Ending 12/31/2021 3 4 Third 5 6 Budgeted Sales in units 7 Ending Inventory of Finished Goods 8 Total Need for Finished Goods 9 Beginning Inventory of Finished Goods 10 Budgeted Finished Goods Produced in units 11 First Quarter 48,100 15,000 63,100 0 Second Quarter 34,500 19,000 53,5001 15,000 38,5001 Quarter 31,000 20,000 51,000 19,000 32,0001 Fourth Quarter 43,000 16,000 59,000 20,000 39,000 Annual Total 156,600 16,000 172,600 0 63,100 172,600 1 Direct Materials Purchases Budget 2 3 4 5 Budgeted Finished Goods Produced (units) Raw Materials needed per unit 7 Raw Materials needed for production 8 Raw Material Ending Inventory (units) 9 Total Raw Materials needed 10 Raw Materials Beginning Inventory (units) 11 Raw Materials Purchases (units) 12 Cost per Unit 13 Raw Material Purchases Cost (dollars) 14 First Second Third Fourth Annual Quarter Quarter Quarter Quarter Total 63,100 38,500 32,0001 39,000 172,600 3 3 3 3 3 189,300 115,500 96,000 117,000 517,800 28,875 24,000 29,250 26,000 26,000 218,175 139,500 125,250 143,000 543,800 41,368 28,875 24,000 29,250 41,368 176,807 110,625 101,250 113,750 502,432 $ 85.00 $ 85.00 $ 85.00 $ 85.00 $ 85.00 $ 15,028,595 $ 9,403,125 $ 8,606,250 $ 9,668,750 $ 42,706,720 15 16 CHECK YOUR DIRECT MATERIALS PURCHASES BUDGET Does the sum of the Direct Material Purchases each quarter equal the total calculated from the YES 17 Annual Total column (F)? 18 19 20 21 22 23 24 Direct Labor Budget 25 26 First Second Third Fourth Annual 27 Quarter Quarter Quarter Quarter Total 28 Finished Goods Produced (units) 63,100 38,500 32,000 39,000 172,600 29 Direct Labor Hours Required per Unit 4.50 4.50 4.50 4.50 4.50 30 Total Direct Labor Hours Required 283,950 173,250 144,000 175,500 776,700 31 Hourly labor Rate 18.00 $ 18.00 $ 18.00 $ 18.00 $ 18.00 32 Direct Labor Cost (dollars) $ 5,111,100 $ 3,118,500 $ 2,592,000 $3,159.000 $ 13,980,600 1 Overhead Budget 2 3 First Second Third Fourth Annual 4 Quarter Quarter Quarter Quarter Total 5 Total Direct Labor Hours Budgeted 283,950 173,250 144,000 175,500 776,700 6 Variable Overhead Rate per hour $ 10.50 $ 10.50 $ 10.50 $ 10.50 $ 10.50 7 Variable Overhead Cost $ 2,981,475$ 1,819,125$ 1,512,000 $ 1,842,750$ 8,155,350 8 Fixed Overhead Cost without Depreciation $ 950,000 $ 950,000 $ 950,000 $ 950,000 $ 3,800,000 9 Depreciation Cost $ 195,000 $ 195,000 $ 195,000 $ 195,000 $ 780,000 10 Total Overhead Cost $ 4,126,475$ 2,964,125$ 2,657,000 $ 2,987,750 $ 12,735,350 11 12 13 CHECK YOUR OVERHEAD BUDGET Does the sum of the overhead costs each quarter equal the total calculated from the Annual Total YES 14 column (E)? 15 16 17 18 19 Selling & Administrative Expense Budget 20 21 First 22 Quarter Second Quarter 18,285,000 $ 3.25% $ 23 Budgeted Sales in Dollars 24 Variable S&A rate 25 Variable S&A Cost 26 Fixed S&A Cost without Depreciation 27 Depreciation Cost 28 Total S&A Cost 25,493,000 $ 3.25% 828,523$ 180,000 $ 55,000 $ 1,063,523 $ Third Fourth Quarter Quarter 16,430,000$ 22,790,000 $ 3.25% 3.25% 533,975$ 740,675$ 180,000 $ 180,000 $ 55,000 $ 55,000$ 768,975 $ 975,675$ Annual Total 82,998,000 3.25% 2,697,435 720,000 220,000 3,637,435 $ $ $ 594,263 $ 180,000 $ 55,000 $ 829,263 $ Annual Second Quarter 18,285,000 Third Quarter 16,430,000 Fourth Quarter 22,790,000S 82,998,000 1 SCHEDULE OF CASH COLLECTIONS 2 3 First 4 Quarter 5 Budgeted Sales in dollars S 25,493,000 $ 6 7 Collections in quarter of sale 21,159,190$ 8 Collections in quarter following sale $ 5,050,000 $ 9 Total Collections 5 26,209,1905 10 11 Net Accounts Receivable Balance $ 3,823,950 $ 12 13 CHECK YOUR SCHEDULE OF CASH COLLECTIONS 15.176,550 $ 3,823,950 S 19,000,500 13,636.900$ 2,742,750 S 16,379,650 $ 18.915.700$ 2,464,500 $ 21,380.2005 68,888,340 14 081200 82,969,540 2,742,750 2,464,500 3,418 500S 3,418 500 Annual YES 14 Have you found the amount of cash that will be collected in the first quarter from sales in 2020? 15 16 17 18 SCHEDULE OF CASH PAYMENTS 19 20 First Second Third Fourth 21 Quarter Quarter Quarter Quarter 22 23 Raw Material Purchases $ 15,028,595s 9,403 125$ 8,606 2505 9,668,7505 24 25 Payments in quarter of purchase S 12,774,305.75 S 7,992,656.25S 7,315,312.50 S 8,218,437.50 $ 26 Payments in quarter following purchase $ 8,100,000 $ 2,254,2895 1,410,4695 1,290,9385 27 Total payments s 20,874,3065 10,246,9465 8,725,7815 9,509,3755 28 29 Accounts payable balance $ 2,254,2895 1.410,469S 1.290,9385 1.450,3135 30 42.706, 720 36,300,712 13,055,696 49,356 408 1.450 313 Second Quarter Third Quarter Annual Fourth Quarter 15,176,550 $ 3,823,950 $ 19.000 500 13,636,900 $ 2,742,7505 16,379,650 $ 18,915,700S 2,464,500S 21,380,200 $ 68,888,340 14,081 200 82.969,540 olululului 7,992,6565 2,254,2895 3,118,500S 2,769,1255 774,263$ 16,908,8335 7,315,3135 1,410,469 $ 2,592,000$ 2,462,000 713,9755 14,493,756 $ 8,218,4385 1,290,9385 3,159,000$ 2,792,7505 920,6755 16,381,800 $ 36,300,712 13,055,696 13,980,600 11,955,350 3,417 435 78,709,793 36 CASH BUDGET 37 38 First 39 Quarter 40 Cash Collections: 41 Collections in quarter of sale $ 21,159,190$ 42 Collections in quarter following sale $ 5,050,000 $ 43 Total Cash Receipts $ 26,209,190$ 44 45 Cash Disbursements 46 Raw Materials - 47 Payments in quarter of purchase S 12,774,3065 48 Payments in quarter following purchase 8,100,000 $ 49 Direct Labor Costs 5,111,100 $ 50 Overhead Costs 3,931,475$ 51 Selling and Administrative Costs 1,008,5235 52 Total Cash Disbursements 30,925,4035 53 54 Net Operating Cash Flows $ (4,716,213) 55 Beginning Cash s 6,150,000 56 Net Cash Available 1,433,7875 57 Minimum cash balance 250,000 S 58 Cash Available for Discretionary Uses 1,183,7875 59 60 Discretionary Uses of Cash 61 Equipment Purchases $ 1,000 $ 62 Dividends 500S 63 64 Leave Financing section empty 65 Financing (should not be required) 66 Borrowing 67 Repayment 68 Interest Total Financing Cash Increase (Decrease 70 71 Net Cash Available After Dicretionary Cash Uses $ 1,432,2875 72 Interest Earned on Cash S 14,3235 73 Ending Cash Balance $ 1,446,6105 uluu 2,091,6675 1.446,6105 3,538,2775 250,000 $ 3,288,277 $ 1,885,8945 3543,359 $ 5,429,2535 250,000S 5,179,2535 4,998,400$ 5,433,0465 10,431,4465 250,000 $ 10,181,4465 4,259,748 6,150,000 10,409,748 250,000 10,159,748 wu 20,000 $ 10,000 $ 30,000 $ 20,000 $ 40,000 $ 30,000 91,000 60.500 3,508,277 $ 35,083 S 3,543 359 s 5,379,2535 53.7935 5,433,046 S 10,361,4465 103,6145 10,465,0605 10,258,248 206,813 10,465,060 74 1 Fixed Manufacturing Overhead Rate 2 3 Total Finished Goods Units Produced 4 Total Fixed Overhead 5 Fixed Manufacturing Overhead Rate 6 7 CHECK YOUR FIXED MANUFACTURING OVERHEAD RATE Have you found the Total Fixed Overhead and used the correct formula? MORE WORK TO DO 8 9 Use Per Unit Costs 10 11 12 Ending Finished Goods Inventory-Absorption 13 14 Direct Material 15 Direct Labor 16 Variable Overhead 17 Fixed Overhead 18 Total product cost per unit manufactured 19 20 Units in Ending Finished Goods Inventory 21 Finished Goods Ending Inventory Value 25 Ending Finished Goods Inventory - Variable Use Per Unit Costs 27 Direct Material 28 Direct Labor 29 Variable Overhead 30 Total product cost per unit maufactured 31 32 Units in Ending Finished Goods Inventory 33 Finished Goods Ending Inventory Value OH & S&A Exp Budget Cash Budget Finished Goods Inventory 1 Cost of Goods Manufactured - Absorption HINTS 2 For Year Ended 12/31/2021 3 4 Direct Materials: 5 Beginning Inventory of raw materials Beginn 6 Raw Material Purchases Raw m 7 Raw Materials Available 8 Ending Inventory of Raw Materials Ending 9 Direct Materials Used 10 Direct Labor: Direct L 11 Overhead: Overhea 12 Total Manufacturing Costs Added 13 Plus: Beginning Work in Process Inventory Beginnir 14 Total Manufacturing Costs 15 Less: Ending Work in Process Inventory Ending V 16 Cost of Goods Manufactured 17 18 CHECK YOUR COST OF GOODS MANUFACTURED - ABSORPTION MORE Have you calculated beginning inventory of raw materials from the 2020 information? 19 WORK 20 21 22 23 Cost of Goods Manufactured - Variable 24 For Year Ended 12/31/2021 25 26 Direct Materials Used 27 Direct Labor 28 Variable Overhead 29 Total Variable Manufacturing Costs 30 31 Graved cells not used OH & S&A Exp Budget Cash Budget Finished Goods Inventory Cost of Goods Manu 1 Cost of Goods Sold - Absorption 2 3 Beginning Finished Goods Inventory 4 Cost of Goods Manufactured 5 Ending Finished Goods Inventory 6 Cost of Goods Sold 7 8 9 Cost of Goods Sold - Variable 10 11 Beginning Finished Goods Inventory 12 Cost of Goods Manufactured 13 Ending Finished Goods Inventory 14 Cost of Goods Sold 1 B 1 Precision Propulsion Company 2 Absorption Costing Income Statement 3 For the Year Ended 12/31/2021 4 5 Revenue 6 Less: Cost of Goods Sold 7 Gross Margin 8 Less: Operating Expenses 9 Selling & Administrative Expense 10 Bad Debt Expense 11 Operating Income 12 Plus: Interest Revenue 13 Income Before Taxes 14 1 Precision Propulsion Company 2 Variable Costing Income Statement 3 For the Year Ended 12/31/2021 4 5 Revenue 6 Less: Variable Costs 7 Variable Cost of Goods Sold 8 Variable Selling & Administrative Costs 9 Bad Debt Expense 10 Contribution Margin 11 Less: Fixed Costs 12 *Fixed Manufacturing Cost 13 *Fixed Selling & Administrative Costs 14 Operating Income 15 Plus: Interest Revenue 16 Income Before Taxes 17 18 *Include cash and non-cash fixed costs 19 Grayed cells not used 1 2 Precision Propulsion Company Statement of Retained Earnings For the Year Ended 12/31/2021 3 4 5 Retained Earnings 12/31/2020 Plus: Income (Before Taxes) 7 Less: Dividends 8 Retained Earnings 12/31/2021 B 1 2 Precision Propulsion Company Balance Sheet For the Year Ended 12/31/2021 3 4 5 ASSETS 6 Cash 7 Raw Materials Inventory 8 Work in Process Inventory 9 Finished Goods Inventory 10 Accounts Receivable (net) 11 Plant and Equipment 12 Total Assets 13 14 LIABILITIES AND STOCKHOLDER'S EQUITY 15 Accounts Payable 16 Common Stock 17 Retained Earnings 18 Total Equity 19 4 1 Precision Propulsion Company 2 Statement of Cash Flows 3 For the Year Ended 12/31/2021 4 5 Cash Flow for Operations 6 Cash Inflows 7 From customers 8 From interest 9 Cash Outflows 10 For raw materials 11 for direct production salaries 12 for other operating activities 13 Net Cash Flow for Operations 14 15 Cash Flow for Investing Activities 16 Cash Outflows 17 Capital Expenditures 18 Net Cash Flow for Investing Activities 19 20 Cash Flow for Financing Activities 21 Cash Outflows 22 For Dividends 23 Net Cash Flow for Financing Activities 24 25 Change in Cash 26 Beginning Cash 27 Ending Cash 28 29 Reconciliation of Net Income to Net Cash Flow for Operations 30 Net Income 31 Adjustments for Non-cash expenses 32 Depreciation 33 Increase (Decrease) for Changes in Working Capital 34 Change in Net Accounts Receivable 35 Change in Inventory 36 Change in Accounts Payable 37 Net Cash Flow for Operations 38 2 1 Breakeven Analysis 2 Put your well-labelled breakeven analysis below. Remember that all of the calculations must be completed in EXCEL formulas and no new data is entered on this worksheet: You may add more rows to this section if 3 needed. Be sure to put your results below. 4 5 6 Variable Cost Per Unit 7 CO Quantity 10 Cost per 1 Price Per 1 Unit Finished Goods Label for Quantity of Quantity Unit hours per unit amount of Raw Materials per unit DL hours per unit percentage of sales price percentage of sales price 11 Description of cost Direct Labor Direct Materials Variable Overhead Variable Selling & Administrative Cost Bad Debts Expense Total Variable Costs per Finished Goods Unit 12 13 14 15 16 CHECK YOUR VARIABLE COSTS MORE WORK TO DO Per Unit Have you included ALL variable costs? 17 18 19 20 21 Contribution Margin and Fixed Expenses 22 23 24 Selling Price 25 Variable costs 26 Contribution Margin 27 28 29 Fixed S & A Costs 30 Fixed Overhead Costs 31 Total Fixed Cost 312 Total Fixed Expense E 31 A B D 22 23 Per Unit 24 Selling Price 25 Variable costs 26 Contribution Margin 27 28 Total Fixed Expense 29 Fixed S & A Costs 30 Fixed Overhead Costs Total Fixed Cost 32 33 Breakeven Results: 34 35 units 36 37 Table of information for Graph showing Breakeven Point 38 39 Units Produced Total Cost Revenue 40 OS 41 5,000 $ 50,000 $ 100,000 42 10,000 $ 100,000 $ 200,000 43 15,000 $ 150,000 $ 300,000 44 20,000 $ 200,000 $ 400,000 45 25,000 $ 250,000 $ 500,000 46 30,000 $ 300,000 $ 600,000 47 35,000 $ 350,000 $ 700,000 48 40,000 $ 400,000 $ 800,000 49 45,000 $ 450,000 $ 900,000 50 50,000 $ 500,000 $ 1,000,000 51 55,000 $ 550,000 $ 1,100,000 52 60,000 $ 600,000 $ 1,200,000 53 65,000 $ 650,000 $ 1,300,000 54 70,000 $ 700,000 $ 1,400,000 55 75,000 $ 750,000 $ 1,500,000 56 180,000 $ 1,800,000 $ 3,600,000 57 58 CREATE A GRAPH SHOWING REVENUE AND TOTAL COST BASED ON THE TABLE ABOVE A15 fi Grayed cells not used E F Flexible Budget Estimate Level 2 750 B D 1 Flexible Budget - Production Costs 2 3 Forecasted Flexible 4 Production Budget Estimate 5 Unit Costs Level 1 6 Units of Production 500 7 Budgeted Production Cost: 8 Direct Materials 9 Direct Labor 10 Variable Overhead 11 Total Variable Production Cost 12 Fixed Production Cost 13 Total Budgeted Production Cost 14 15 Grayed cells not used 16 Light yellow cells are the production levels you selected on the CUSTOMIZATION worksheet. 17 CHECK YOUR FIXED PRODUCTION COSTS Have you identified just the fixed production costs? MORE WORK TO DO 18 19 20 21 22 23 24 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 2021 2021 2021 2021 0 15,000 19,000 20,000 16,000 41,368 pounds 26,000 Ipounds 25.00% of next quarters' needs for production is in ending Raw Materials Inventory 231,720 231,720 36 INVENTORY ACCOUNTS 37 Finished Goods Inventory 38 4th Quarter 39 2020 40 Ending units 41 42 Raw Materials Inventory 43 Beginning units on 1/1/2021 44 Planned Ending units on 12/31/2021 45 Policy 46 47 Work In Process Inventory 48 Beginning value 1/1/2021 $ 49 Planned Ending value on 12/31/2021 $ 50 51 Payment for Raw Materials 52 Payment Policy 53 54 55 OPERATING EXPENSES 56 Wages and Salaries Payment 57 Payment Policy 58 59 60 Variable Selling & Administrative Exp. 61 62 Fixed Selling & Administrative Exp 63 Depreciation $ 64 Other Selling & Administrative Exp $ 65 66 CAPITAL EXPENDITURES 67 1st Quarter 85.00% quarter of purchase 15.00% quarter following purchase 2 times monthly on the 15th and 30th Fully paid each month 3.25% of selling price 55,000 per quarter 180,000 per quarter 2nd Quarter 2021 3rd Quarter 2021 4th Quarter 2021 2021 68 66 CAPITAL EXPENDITURES 67 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 68 2021 2021 2021 2021 69 Equipment Purchases $1,000.00 $20,000.00 $30,000.00 $40,000.00 70 71 TREASURY ACTIVITIES 72 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 73 2021 2021 2021 2021 74 Dividends to be Declared $500.00 $10,000.00 $20,000.00 $30,000.00 75 76 Minimum Cash Balance $ 250,000 Required at end of every quarter 77 78 Interest Rate for Investing (short-term) 4.00% Annually 79 Interest Policy All of the ending cash balance for the quarter earns interest for the entire quarter 80 All of the ending cash balance for the quarter remains in Cash at the beginning of the next quarter 81 82 Interest Rate for borrowing 12.00% Annually 83 Interest Policy S 10,000 increments are used in borrowing and repayment, maintaining the minimum cash balance 84 Borrowing occurs at the beginning of the quarter. 85 Repayments occur at the end of the year. 86 87 FLEXIBLE BUDGET Actual Units Produced USE VALUE FROM PRODUCTION BUDGET 89 90 Flexible Budget - Analyze at Additional Production Levels 91 Production level 1 500 units 92 Production level 2 750 units 93 94 PRIOR YEAR RESULTS 95 Precision Propulsion Company 96 Balance Sheet 97 12/31/2020 98 ASSETS CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget DM & DL Budge... 78 Interest Rate for Investing (short-term) 4.00% Annually 79 Interest Policy All of the ending cash balance for the quarter earns interest for the entire quarter 80 All of the ending cash balance for the quarter remains in Cash at the beginning of the next quarter 81 82 Interest Rate for borrowing 12.00% Annually 83 Interest Policy 10,000 increments are used in borrowing and repayment, maintaining the minimum cash balance 84 Borrowing occurs at the beginning of the quarter. 85 Repayments occur at the end of the year. 86 87 FLEXIBLE BUDGET 88 Actual Units Produced USE VALUE FROM PRODUCTION BUDGET 89 90 Flexible Budget - Analyze at Additional Production Levels 91 Production level 1 500 units 92 Production level 2 750 units 93 94 PRIOR YEAR RESULTS 95 Precision Propulsion Company 96 Balance Sheet 97 12/31/2020 98 ASSETS 99 Cash $ 6,150,000 100 Inventory $ 3,748,000 101 Accounts Receivable (net) $ 5,050,000 102 Plant and Equipment $ 29,400,000 103 S 44,348,000 104 105 LIABILITIES AND EQUITY 106 Accounts Payable $ 8,100,000 107 Common Stock S 26,234,500 108 Retained Earnings $ 10,013,500 109 $ 44,348,000 110 CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget DM & DL Budge... + + E 1 Sales Budget 2 Projected for Year Ending 12/31/2021 3 4 5 6 Budgeted Sales in Units 7 Selling Price 8 Budgeted Sales in Dollars 9 First Second Third Fourth Annual Quarter Quarter Quarter Quarter Total 48,1001 34,500 31,000 43,000 156600 $530 $530 $530 $530 $530 $ 25,493,000 $ 18,285,000$ 16,430,000$ 22,790,000$ 82,998,000 INPUT SECTION SALES 4th Quarter 2020 1st Quarter 2nd Quarter 2021 48,100 3rd 4th Quarter Quarter 2021 2021 34,500 31,000 2021 43,000 Budgeted Sales in units 41,000 Budgeted Selling Price $ 530 per production unit (Finished Goods) RECEIVABLES Receivables Collection Schedule 83.00% quarter of sale 15.00% quarter following sale 2.00% uncollectible 100.00% Entire projected uncollectible receivables are written off each quarter Policy INVENTORY COSTS Direct Labor 4.5 hours 18.00 per direct labor hour $ Raw Materials 3 pounds per finished goods unit 85.00 per pound $ Variable Overhead $ 10.50 per Direct Labor Hour Fixed Overhead Depreciation $ Other Fixed Overhead $ 195,000 per quarter 950,000 per quarter Fixed Overhead Application Rate CALCULATE FROM PRODUCTION BUDGET INVENTORY ACCOUNTS 1 Production Budget 2 Projected for Year Ending 12/31/2021 3 4 Third 5 6 Budgeted Sales in units 7 Ending Inventory of Finished Goods 8 Total Need for Finished Goods 9 Beginning Inventory of Finished Goods 10 Budgeted Finished Goods Produced in units 11 First Quarter 48,100 15,000 63,100 0 Second Quarter 34,500 19,000 53,5001 15,000 38,5001 Quarter 31,000 20,000 51,000 19,000 32,0001 Fourth Quarter 43,000 16,000 59,000 20,000 39,000 Annual Total 156,600 16,000 172,600 0 63,100 172,600 1 Direct Materials Purchases Budget 2 3 4 5 Budgeted Finished Goods Produced (units) Raw Materials needed per unit 7 Raw Materials needed for production 8 Raw Material Ending Inventory (units) 9 Total Raw Materials needed 10 Raw Materials Beginning Inventory (units) 11 Raw Materials Purchases (units) 12 Cost per Unit 13 Raw Material Purchases Cost (dollars) 14 First Second Third Fourth Annual Quarter Quarter Quarter Quarter Total 63,100 38,500 32,0001 39,000 172,600 3 3 3 3 3 189,300 115,500 96,000 117,000 517,800 28,875 24,000 29,250 26,000 26,000 218,175 139,500 125,250 143,000 543,800 41,368 28,875 24,000 29,250 41,368 176,807 110,625 101,250 113,750 502,432 $ 85.00 $ 85.00 $ 85.00 $ 85.00 $ 85.00 $ 15,028,595 $ 9,403,125 $ 8,606,250 $ 9,668,750 $ 42,706,720 15 16 CHECK YOUR DIRECT MATERIALS PURCHASES BUDGET Does the sum of the Direct Material Purchases each quarter equal the total calculated from the YES 17 Annual Total column (F)? 18 19 20 21 22 23 24 Direct Labor Budget 25 26 First Second Third Fourth Annual 27 Quarter Quarter Quarter Quarter Total 28 Finished Goods Produced (units) 63,100 38,500 32,000 39,000 172,600 29 Direct Labor Hours Required per Unit 4.50 4.50 4.50 4.50 4.50 30 Total Direct Labor Hours Required 283,950 173,250 144,000 175,500 776,700 31 Hourly labor Rate 18.00 $ 18.00 $ 18.00 $ 18.00 $ 18.00 32 Direct Labor Cost (dollars) $ 5,111,100 $ 3,118,500 $ 2,592,000 $3,159.000 $ 13,980,600 1 Overhead Budget 2 3 First Second Third Fourth Annual 4 Quarter Quarter Quarter Quarter Total 5 Total Direct Labor Hours Budgeted 283,950 173,250 144,000 175,500 776,700 6 Variable Overhead Rate per hour $ 10.50 $ 10.50 $ 10.50 $ 10.50 $ 10.50 7 Variable Overhead Cost $ 2,981,475$ 1,819,125$ 1,512,000 $ 1,842,750$ 8,155,350 8 Fixed Overhead Cost without Depreciation $ 950,000 $ 950,000 $ 950,000 $ 950,000 $ 3,800,000 9 Depreciation Cost $ 195,000 $ 195,000 $ 195,000 $ 195,000 $ 780,000 10 Total Overhead Cost $ 4,126,475$ 2,964,125$ 2,657,000 $ 2,987,750 $ 12,735,350 11 12 13 CHECK YOUR OVERHEAD BUDGET Does the sum of the overhead costs each quarter equal the total calculated from the Annual Total YES 14 column (E)? 15 16 17 18 19 Selling & Administrative Expense Budget 20 21 First 22 Quarter Second Quarter 18,285,000 $ 3.25% $ 23 Budgeted Sales in Dollars 24 Variable S&A rate 25 Variable S&A Cost 26 Fixed S&A Cost without Depreciation 27 Depreciation Cost 28 Total S&A Cost 25,493,000 $ 3.25% 828,523$ 180,000 $ 55,000 $ 1,063,523 $ Third Fourth Quarter Quarter 16,430,000$ 22,790,000 $ 3.25% 3.25% 533,975$ 740,675$ 180,000 $ 180,000 $ 55,000 $ 55,000$ 768,975 $ 975,675$ Annual Total 82,998,000 3.25% 2,697,435 720,000 220,000 3,637,435 $ $ $ 594,263 $ 180,000 $ 55,000 $ 829,263 $ Annual Second Quarter 18,285,000 Third Quarter 16,430,000 Fourth Quarter 22,790,000S 82,998,000 1 SCHEDULE OF CASH COLLECTIONS 2 3 First 4 Quarter 5 Budgeted Sales in dollars S 25,493,000 $ 6 7 Collections in quarter of sale 21,159,190$ 8 Collections in quarter following sale $ 5,050,000 $ 9 Total Collections 5 26,209,1905 10 11 Net Accounts Receivable Balance $ 3,823,950 $ 12 13 CHECK YOUR SCHEDULE OF CASH COLLECTIONS 15.176,550 $ 3,823,950 S 19,000,500 13,636.900$ 2,742,750 S 16,379,650 $ 18.915.700$ 2,464,500 $ 21,380.2005 68,888,340 14 081200 82,969,540 2,742,750 2,464,500 3,418 500S 3,418 500 Annual YES 14 Have you found the amount of cash that will be collected in the first quarter from sales in 2020? 15 16 17 18 SCHEDULE OF CASH PAYMENTS 19 20 First Second Third Fourth 21 Quarter Quarter Quarter Quarter 22 23 Raw Material Purchases $ 15,028,595s 9,403 125$ 8,606 2505 9,668,7505 24 25 Payments in quarter of purchase S 12,774,305.75 S 7,992,656.25S 7,315,312.50 S 8,218,437.50 $ 26 Payments in quarter following purchase $ 8,100,000 $ 2,254,2895 1,410,4695 1,290,9385 27 Total payments s 20,874,3065 10,246,9465 8,725,7815 9,509,3755 28 29 Accounts payable balance $ 2,254,2895 1.410,469S 1.290,9385 1.450,3135 30 42.706, 720 36,300,712 13,055,696 49,356 408 1.450 313 Second Quarter Third Quarter Annual Fourth Quarter 15,176,550 $ 3,823,950 $ 19.000 500 13,636,900 $ 2,742,7505 16,379,650 $ 18,915,700S 2,464,500S 21,380,200 $ 68,888,340 14,081 200 82.969,540 olululului 7,992,6565 2,254,2895 3,118,500S 2,769,1255 774,263$ 16,908,8335 7,315,3135 1,410,469 $ 2,592,000$ 2,462,000 713,9755 14,493,756 $ 8,218,4385 1,290,9385 3,159,000$ 2,792,7505 920,6755 16,381,800 $ 36,300,712 13,055,696 13,980,600 11,955,350 3,417 435 78,709,793 36 CASH BUDGET 37 38 First 39 Quarter 40 Cash Collections: 41 Collections in quarter of sale $ 21,159,190$ 42 Collections in quarter following sale $ 5,050,000 $ 43 Total Cash Receipts $ 26,209,190$ 44 45 Cash Disbursements 46 Raw Materials - 47 Payments in quarter of purchase S 12,774,3065 48 Payments in quarter following purchase 8,100,000 $ 49 Direct Labor Costs 5,111,100 $ 50 Overhead Costs 3,931,475$ 51 Selling and Administrative Costs 1,008,5235 52 Total Cash Disbursements 30,925,4035 53 54 Net Operating Cash Flows $ (4,716,213) 55 Beginning Cash s 6,150,000 56 Net Cash Available 1,433,7875 57 Minimum cash balance 250,000 S 58 Cash Available for Discretionary Uses 1,183,7875 59 60 Discretionary Uses of Cash 61 Equipment Purchases $ 1,000 $ 62 Dividends 500S 63 64 Leave Financing section empty 65 Financing (should not be required) 66 Borrowing 67 Repayment 68 Interest Total Financing Cash Increase (Decrease 70 71 Net Cash Available After Dicretionary Cash Uses $ 1,432,2875 72 Interest Earned on Cash S 14,3235 73 Ending Cash Balance $ 1,446,6105 uluu 2,091,6675 1.446,6105 3,538,2775 250,000 $ 3,288,277 $ 1,885,8945 3543,359 $ 5,429,2535 250,000S 5,179,2535 4,998,400$ 5,433,0465 10,431,4465 250,000 $ 10,181,4465 4,259,748 6,150,000 10,409,748 250,000 10,159,748 wu 20,000 $ 10,000 $ 30,000 $ 20,000 $ 40,000 $ 30,000 91,000 60.500 3,508,277 $ 35,083 S 3,543 359 s 5,379,2535 53.7935 5,433,046 S 10,361,4465 103,6145 10,465,0605 10,258,248 206,813 10,465,060 74 1 Fixed Manufacturing Overhead Rate 2 3 Total Finished Goods Units Produced 4 Total Fixed Overhead 5 Fixed Manufacturing Overhead Rate 6 7 CHECK YOUR FIXED MANUFACTURING OVERHEAD RATE Have you found the Total Fixed Overhead and used the correct formula? MORE WORK TO DO 8 9 Use Per Unit Costs 10 11 12 Ending Finished Goods Inventory-Absorption 13 14 Direct Material 15 Direct Labor 16 Variable Overhead 17 Fixed Overhead 18 Total product cost per unit manufactured 19 20 Units in Ending Finished Goods Inventory 21 Finished Goods Ending Inventory Value 25 Ending Finished Goods Inventory - Variable Use Per Unit Costs 27 Direct Material 28 Direct Labor 29 Variable Overhead 30 Total product cost per unit maufactured 31 32 Units in Ending Finished Goods Inventory 33 Finished Goods Ending Inventory Value OH & S&A Exp Budget Cash Budget Finished Goods Inventory 1 Cost of Goods Manufactured - Absorption HINTS 2 For Year Ended 12/31/2021 3 4 Direct Materials: 5 Beginning Inventory of raw materials Beginn 6 Raw Material Purchases Raw m 7 Raw Materials Available 8 Ending Inventory of Raw Materials Ending 9 Direct Materials Used 10 Direct Labor: Direct L 11 Overhead: Overhea 12 Total Manufacturing Costs Added 13 Plus: Beginning Work in Process Inventory Beginnir 14 Total Manufacturing Costs 15 Less: Ending Work in Process Inventory Ending V 16 Cost of Goods Manufactured 17 18 CHECK YOUR COST OF GOODS MANUFACTURED - ABSORPTION MORE Have you calculated beginning inventory of raw materials from the 2020 information? 19 WORK 20 21 22 23 Cost of Goods Manufactured - Variable 24 For Year Ended 12/31/2021 25 26 Direct Materials Used 27 Direct Labor 28 Variable Overhead 29 Total Variable Manufacturing Costs 30 31 Graved cells not used OH & S&A Exp Budget Cash Budget Finished Goods Inventory Cost of Goods Manu 1 Cost of Goods Sold - Absorption 2 3 Beginning Finished Goods Inventory 4 Cost of Goods Manufactured 5 Ending Finished Goods Inventory 6 Cost of Goods Sold 7 8 9 Cost of Goods Sold - Variable 10 11 Beginning Finished Goods Inventory 12 Cost of Goods Manufactured 13 Ending Finished Goods Inventory 14 Cost of Goods Sold 1 B 1 Precision Propulsion Company 2 Absorption Costing Income Statement 3 For the Year Ended 12/31/2021 4 5 Revenue 6 Less: Cost of Goods Sold 7 Gross Margin 8 Less: Operating Expenses 9 Selling & Administrative Expense 10 Bad Debt Expense 11 Operating Income 12 Plus: Interest Revenue 13 Income Before Taxes 14 1 Precision Propulsion Company 2 Variable Costing Income Statement 3 For the Year Ended 12/31/2021 4 5 Revenue 6 Less: Variable Costs 7 Variable Cost of Goods Sold 8 Variable Selling & Administrative Costs 9 Bad Debt Expense 10 Contribution Margin 11 Less: Fixed Costs 12 *Fixed Manufacturing Cost 13 *Fixed Selling & Administrative Costs 14 Operating Income 15 Plus: Interest Revenue 16 Income Before Taxes 17 18 *Include cash and non-cash fixed costs 19 Grayed cells not used 1 2 Precision Propulsion Company Statement of Retained Earnings For the Year Ended 12/31/2021 3 4 5 Retained Earnings 12/31/2020 Plus: Income (Before Taxes) 7 Less: Dividends 8 Retained Earnings 12/31/2021 B 1 2 Precision Propulsion Company Balance Sheet For the Year Ended 12/31/2021 3 4 5 ASSETS 6 Cash 7 Raw Materials Inventory 8 Work in Process Inventory 9 Finished Goods Inventory 10 Accounts Receivable (net) 11 Plant and Equipment 12 Total Assets 13 14 LIABILITIES AND STOCKHOLDER'S EQUITY 15 Accounts Payable 16 Common Stock 17 Retained Earnings 18 Total Equity 19 4 1 Precision Propulsion Company 2 Statement of Cash Flows 3 For the Year Ended 12/31/2021 4 5 Cash Flow for Operations 6 Cash Inflows 7 From customers 8 From interest 9 Cash Outflows 10 For raw materials 11 for direct production salaries 12 for other operating activities 13 Net Cash Flow for Operations 14 15 Cash Flow for Investing Activities 16 Cash Outflows 17 Capital Expenditures 18 Net Cash Flow for Investing Activities 19 20 Cash Flow for Financing Activities 21 Cash Outflows 22 For Dividends 23 Net Cash Flow for Financing Activities 24 25 Change in Cash 26 Beginning Cash 27 Ending Cash 28 29 Reconciliation of Net Income to Net Cash Flow for Operations 30 Net Income 31 Adjustments for Non-cash expenses 32 Depreciation 33 Increase (Decrease) for Changes in Working Capital 34 Change in Net Accounts Receivable 35 Change in Inventory 36 Change in Accounts Payable 37 Net Cash Flow for Operations 38 2 1 Breakeven Analysis 2 Put your well-labelled breakeven analysis below. Remember that all of the calculations must be completed in EXCEL formulas and no new data is entered on this worksheet: You may add more rows to this section if 3 needed. Be sure to put your results below. 4 5 6 Variable Cost Per Unit 7 CO Quantity 10 Cost per 1 Price Per 1 Unit Finished Goods Label for Quantity of Quantity Unit hours per unit amount of Raw Materials per unit DL hours per unit percentage of sales price percentage of sales price 11 Description of cost Direct Labor Direct Materials Variable Overhead Variable Selling & Administrative Cost Bad Debts Expense Total Variable Costs per Finished Goods Unit 12 13 14 15 16 CHECK YOUR VARIABLE COSTS MORE WORK TO DO Per Unit Have you included ALL variable costs? 17 18 19 20 21 Contribution Margin and Fixed Expenses 22 23 24 Selling Price 25 Variable costs 26 Contribution Margin 27 28 29 Fixed S & A Costs 30 Fixed Overhead Costs 31 Total Fixed Cost 312 Total Fixed Expense E 31 A B D 22 23 Per Unit 24 Selling Price 25 Variable costs 26 Contribution Margin 27 28 Total Fixed Expense 29 Fixed S & A Costs 30 Fixed Overhead Costs Total Fixed Cost 32 33 Breakeven Results: 34 35 units 36 37 Table of information for Graph showing Breakeven Point 38 39 Units Produced Total Cost Revenue 40 OS 41 5,000 $ 50,000 $ 100,000 42 10,000 $ 100,000 $ 200,000 43 15,000 $ 150,000 $ 300,000 44 20,000 $ 200,000 $ 400,000 45 25,000 $ 250,000 $ 500,000 46 30,000 $ 300,000 $ 600,000 47 35,000 $ 350,000 $ 700,000 48 40,000 $ 400,000 $ 800,000 49 45,000 $ 450,000 $ 900,000 50 50,000 $ 500,000 $ 1,000,000 51 55,000 $ 550,000 $ 1,100,000 52 60,000 $ 600,000 $ 1,200,000 53 65,000 $ 650,000 $ 1,300,000 54 70,000 $ 700,000 $ 1,400,000 55 75,000 $ 750,000 $ 1,500,000 56 180,000 $ 1,800,000 $ 3,600,000 57 58 CREATE A GRAPH SHOWING REVENUE AND TOTAL COST BASED ON THE TABLE ABOVE A15 fi Grayed cells not used E F Flexible Budget Estimate Level 2 750 B D 1 Flexible Budget - Production Costs 2 3 Forecasted Flexible 4 Production Budget Estimate 5 Unit Costs Level 1 6 Units of Production 500 7 Budgeted Production Cost: 8 Direct Materials 9 Direct Labor 10 Variable Overhead 11 Total Variable Production Cost 12 Fixed Production Cost 13 Total Budgeted Production Cost 14 15 Grayed cells not used 16 Light yellow cells are the production levels you selected on the CUSTOMIZATION worksheet. 17 CHECK YOUR FIXED PRODUCTION COSTS Have you identified just the fixed production costs? MORE WORK TO DO 18 19 20 21 22 23 24 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 2021 2021 2021 2021 0 15,000 19,000 20,000 16,000 41,368 pounds 26,000 Ipounds 25.00% of next quarters' needs for production is in ending Raw Materials Inventory 231,720 231,720 36 INVENTORY ACCOUNTS 37 Finished Goods Inventory 38 4th Quarter 39 2020 40 Ending units 41 42 Raw Materials Inventory 43 Beginning units on 1/1/2021 44 Planned Ending units on 12/31/2021 45 Policy 46 47 Work In Process Inventory 48 Beginning value 1/1/2021 $ 49 Planned Ending value on 12/31/2021 $ 50 51 Payment for Raw Materials 52 Payment Policy 53 54 55 OPERATING EXPENSES 56 Wages and Salaries Payment 57 Payment Policy 58 59 60 Variable Selling & Administrative Exp. 61 62 Fixed Selling & Administrative Exp 63 Depreciation $ 64 Other Selling & Administrative Exp $ 65 66 CAPITAL EXPENDITURES 67 1st Quarter 85.00% quarter of purchase 15.00% quarter following purchase 2 times monthly on the 15th and 30th Fully paid each month 3.25% of selling price 55,000 per quarter 180,000 per quarter 2nd Quarter 2021 3rd Quarter 2021 4th Quarter 2021 2021 68 66 CAPITAL EXPENDITURES 67 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 68 2021 2021 2021 2021 69 Equipment Purchases $1,000.00 $20,000.00 $30,000.00 $40,000.00 70 71 TREASURY ACTIVITIES 72 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 73 2021 2021 2021 2021 74 Dividends to be Declared $500.00 $10,000.00 $20,000.00 $30,000.00 75 76 Minimum Cash Balance $ 250,000 Required at end of every quarter 77 78 Interest Rate for Investing (short-term) 4.00% Annually 79 Interest Policy All of the ending cash balance for the quarter earns interest for the entire quarter 80 All of the ending cash balance for the quarter remains in Cash at the beginning of the next quarter 81 82 Interest Rate for borrowing 12.00% Annually 83 Interest Policy S 10,000 increments are used in borrowing and repayment, maintaining the minimum cash balance 84 Borrowing occurs at the beginning of the quarter. 85 Repayments occur at the end of the year. 86 87 FLEXIBLE BUDGET Actual Units Produced USE VALUE FROM PRODUCTION BUDGET 89 90 Flexible Budget - Analyze at Additional Production Levels 91 Production level 1 500 units 92 Production level 2 750 units 93 94 PRIOR YEAR RESULTS 95 Precision Propulsion Company 96 Balance Sheet 97 12/31/2020 98 ASSETS CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget DM & DL Budge... 78 Interest Rate for Investing (short-term) 4.00% Annually 79 Interest Policy All of the ending cash balance for the quarter earns interest for the entire quarter 80 All of the ending cash balance for the quarter remains in Cash at the beginning of the next quarter 81 82 Interest Rate for borrowing 12.00% Annually 83 Interest Policy 10,000 increments are used in borrowing and repayment, maintaining the minimum cash balance 84 Borrowing occurs at the beginning of the quarter. 85 Repayments occur at the end of the year. 86 87 FLEXIBLE BUDGET 88 Actual Units Produced USE VALUE FROM PRODUCTION BUDGET 89 90 Flexible Budget - Analyze at Additional Production Levels 91 Production level 1 500 units 92 Production level 2 750 units 93 94 PRIOR YEAR RESULTS 95 Precision Propulsion Company 96 Balance Sheet 97 12/31/2020 98 ASSETS 99 Cash $ 6,150,000 100 Inventory $ 3,748,000 101 Accounts Receivable (net) $ 5,050,000 102 Plant and Equipment $ 29,400,000 103 S 44,348,000 104 105 LIABILITIES AND EQUITY 106 Accounts Payable $ 8,100,000 107 Common Stock S 26,234,500 108 Retained Earnings $ 10,013,500 109 $ 44,348,000 110 CUSTOMIZATION PROJECT INFORMATION Sales Budget Production Budget DM & DL Budge... + + E 1 Sales Budget 2 Projected for Year Ending 12/31/2021 3 4 5 6 Budgeted Sales in Units 7 Selling Price 8 Budgeted Sales in Dollars 9 First Second Third Fourth Annual Quarter Quarter Quarter Quarter Total 48,1001 34,500 31,000 43,000 156600 $530 $530 $530 $530 $530 $ 25,493,000 $ 18,285,000$ 16,430,000$ 22,790,000$ 82,998,000