How do I proceed with calculations on the variable income statement tab?

Enter your WIN number in the next cell Put one number in each cell W 2 2 5 1 1 8 3 6 Diesel Dynamo Company Budget Project Fall 2017 INPUT SECTION SALES 4th Quarter 2017 43,000 Budgeted Sales in units Budgeted Selling Price 2nd Quarter 2018 34,500 $530 per production unit (Finished Good) RECEIVABLES Receivables Collection Schedule 91.50% quarter of sale 5.50% quarter following sale 3.00% uncollectible 100.00% Entire projected uncollectible receivables are written off each Policy INVENTORY COSTS Direct Labor $ 4.5 hours 19.50 per direct labor hour $ 3 direct material units per finished good production unit 85.00 per raw material unit Raw Materials Variable Overhead $ Fixed Overhead Depreciation Other Fixed Overhead $ Fixed Overhead Application Rate 1st Quarter 2018 37,500 9.50 per Direct Labor Hour 328,000 per quarter $950,000 per quarter CALCULATE FROM PRODUCTION BUDGET INVENTORY ACCOUNTS 4th Quarter Ending Finished Goods Inventory in units 0 Raw Materials Inventory Beginning Inventory 1/1/2018 Planned Ending Inventory Policy Work In Process Inventory Beginning Inventory 1/1/2018 Planned Ending Inventory 1st Quarter 15,000 2nd Quarter 19,000 28,436 units 26,000 units 25% of next quarters' needs for production is in ending Raw Mater $ $ 2,850 2,850 Payment for Raw Materials Payment Policy 85% quarter of purchase 15% quarter following purchase OPERATING EXPENSES Wages and Salaries Payment Payment Policy 2 times monthly on the 15th and 30th Fully paid each month Variable Selling & Administrative Exp. 3.25% of selling price Fixed Selling & Administrative Exp Depreciation Other Selling & Administrative Exp $55,000 per quarter $180,000 per quarter CAPITAL EXPENDITURES Equipment Purchases $5,450,000 end of 4th quarter TREASURY ACTIVITIES Dividends to be Declared $400,000 per quarter Minimum Cash Balance $250,000 Required at end of every quarter Interest Rate for Investing (short-term) Interest Policy 3.50% Annually All of the ending cash balance for the quarter earns interest for t All of the ending cash balance for the quarter remains in Cash at Interest Rate for borrowing Interest Policy 12% Annually $10,000 increments are used in borrowing and repayment, maintainin Borrowing occurs at the beginning of the quarter. Repayments occur at the end of the quarter. FLEXIBLE BUDGET Actual Units Produced First Quarter 43,000 units Flexible Budget - Analyze at 2 Production Levels Production level 1 Production level 2 45,000 units 40,000 units PRIOR YEAR RESULTS Diesel Dynamo Company Balance Sheet 12/31/2017 ASSETS Cash Inventory Accounts Receivable (net) Plant and Equipment LIABILITIES AND EQUITY $ $ $ $ $ 6,150,000 3,748,000 5,050,000 29,400,000 44,348,000 (use for Flexible Budget Tab only) Accounts Payable Common Stock Retained Earnings $ $ $ $ 8,100,000 26,234,500 10,013,500 44,348,000 3rd Quarter 2018 31,000 4th Quarter 2018 43,000 tible receivables are written off each quarter finished good production unit 3rd Quarter 20,000 4th Quarter 15,000 or production is in ending Raw Materials Inventory e for the quarter earns interest for the entire quarter e for the quarter remains in Cash at the beginning of the next quarter orrowing and repayment, maintaining the minimum cash balance beginning of the quarter. end of the quarter. or Flexible Budget Tab only) Sales Budget Projected for Year Ending 12/31/2018 First Second Quarter Sales in Units Selling Price Sales Revenue 37,500 $530 $ Quarter 34,500 $530 Third Quarter 31,000 $530 Fourth Quarter 43,000 $530 Annual Total 146,000 $530 19,875,000 $ 18,285,000 $ 16,430,000 $ 22,790,000 $ 77,380,000 NOTE: this information flows into the Cash Collections portion of the Cash Budget $77,380,000 Checkfigure from Total COLULMN Production Budget i.e. Finished Goods Production Plan First Second Third Fourth Quarter Quarter Quarter Quarter Annual Total Sales needs (Production Units) 37,500 34,500 31,000 43,000 146,000 Ending Inventory Total Needs 15,000 52,500 19,000 53,500 20,000 51,000 15,000 58,000 15,000 161,000 0 15,000 19,000 20,000 0 52,500 38,500 32,000 38,000 161,000 Beginning Inventory Total Production 161,000 Checkfigure for Annual Total Column Direct Materials Purchases Budget Finished Goods Produced (units) DM needed per unit DM needed for production DM Ending Inventory Units Total DM needed DM Beginning Inventory DM Purchases in Units Purchase Cost per Unit $ Cost of DM Purchases $ DM = Direct Materials (i.e. raw materials) First Quarter 52,500 3 157,500 28,875 186,375 28,436 157,939 85.00 $ 13,424,815 $ Second Third Fourth Annual Quarter Quarter Quarter Total 38,500 32,000 38,000 161,000 3 3 3 3 115,500 96,000 114,000 483,000 24,000 28,500 26,000 26,000 139,500 124,500 140,000 509,000 28,875 24,000 28,500 28,436 110,625 100,500 111,500 480,564 85.00 $ 85.00 $ 85.00 $ 85.00 9,403,125 $ 8,542,500 $ 9,477,500 $ 40,847,940 $ 40,847,940 $ 14,127,750 Direct Labor Budget FG Units to be Produced Hours Required/Unit Total Hours Required Hourly Labor Rate $ Direct Labor Cost FG = Finished Goods or production units First Quarter 52,500 4.50 236,250 19.50 $ $4,606,875 Second Quarter 38,500 4.50 173,250 19.50 $ $3,378,375 Third Quarter 32,000 4.50 144,000 19.50 $ $2,808,000 Fourth Annual Quarter Total 38,000 161,000 4.50 4.50 171,000 724,500 19.50 $ 19.50 $3,334,500 $14,127,750 Overhead Budget Budgeted Hours Variable Overhead Rate ($/hour) Budgeted Variable Overhead Budgeted Fixed Overhead without Depreciation Depreciation Total Overhead Leave Totals rounded to nearest dollar. First Quarter 236,250 $ 9.50 $ $2,244,375 $950,000 $328,000 $3,522,375 Second Third Fourth Annual Quarter Quarter Quarter Total 173,250 144,000 171,000 724,500 9.50 $ 9.50 $ 9.50 $ 9.50 $1,645,875 $1,368,000 $1,624,500 $6,882,750 $950,000 $950,000 $950,000 $3,800,000 $328,000 $328,000 $328,000 $1,312,000 $2,923,875 $2,646,000 $2,902,500 $11,994,750 $11,994,750 Selling & Administrative Expense Budget Planned Sales in Dollars Variable S&A rate (% Per Unit Sold) Variable S&A Expense Fixed S&A Expense without depreciation Depreciation Total S&A Expense Leave Totals rounded to nearest dollar. First Quarter 19,875,000 3.25% $645,938 $180,000 $55,000 $880,938 Second Quarter 18,285,000 3.25% $594,263 $180,000 $55,000 $829,263 Third Quarter 16,430,000 3.25% $533,975 $180,000 $55,000 $768,975 Fourth Quarter 22,790,000 3.25% $740,675 $180,000 $55,000 $975,675 Annual Total 77,380,000 3.25% $2,514,850 $720,000 $220,000 $3,454,850 $3,454,850 SCHEDULE OF CASH COLLECTIONS First Quarter Second Quarter Third Quarter Fourth Quarter Annual Budgeted Sales in dollars $ 19,875,000 $ 18,285,000 $ 16,430,000 $ 22,790,000 $ 77,380,000 Collections in quarter of sale Collections in quarter following sale Total Collections $ 18,185,625 $ 16,730,775.00 $ 15,033,450 $ $ $ 5,050,000 $ 23,235,625 $ 1,093,125 $ 17,823,900 $ 1,005,675 $ 16,039,125 $ 20,852,850 $ 903,650 $ 21,756,500 $ 70,802,700 8,052,450 78,855,150 Net Accounts Receivable Balance $ 1,093,125 $ 1,005,675 $ 903,650 $ 1,253,450 $ 1,253,450 SCHEDULE OF CASH PAYMENTS First Quarter Second Quarter Third Quarter Fourth Quarter Annual Raw Material Purchases $ 13,424,815 $ 9,403,125 $ 8,542,500 $ 9,477,500 $ 40,847,940 Payments in quarter of purchase Payments in quarter following purchase Total payments $ $ $ 11,411,092.75 $ 8,100,000 $ 19,511,093 $ 7,992,656.25 $ 2,013,722.25 $ 10,006,379 $ 7,261,125.00 $ 1,410,468.75 $ 8,671,594 $ 8,055,875.00 $ 1,281,375.00 $ 9,337,250 $ 34,720,749 12,805,566 47,526,315 Accounts payable balance $ 2,013,722.25 $ 1,410,468.75 $ 1,281,375.00 $ 1,421,625.00 $ 1,421,625 $ First Quarter 6,150,000 $ Second Quarter 854,759 $ Third Quarter 1,537,101 $ Fourth Quarter 2,687,973 $ CASH BUDGET Beginning Cash Balance Cash Collections: From credit sales - Annual 6,150,000 Collections in quarter of sale Collections in quarter following sale Total Cash Available $ $ $ 18,185,625 $ 5,050,000 $ 29,385,625 $ 16,730,775 $ 1,093,125 $ 18,678,659 $ 15,033,450 $ 1,005,675 $ 17,576,226 $ 20,852,850 $ 903,650 $ 24,444,473 $ 70,802,700 8,052,450 85,005,150 Cash Disbursements: For Raw Materials Payments in quarter of purchase Payments in quarter following purchase Direct Labor Overhead Selling and Administrative Expense Equipment Purchases Dividends Total Cash Disbursements $ $ $ $ $ $ $ $ 11,411,093 8,100,000 4,606,875 3,194,375 825,938 400,000 28,538,280 7,992,656 2,013,722 3,378,375 2,595,875 774,263 400,000 17,154,891 7,261,125 1,410,469 2,808,000 2,318,000 713,975 400,000 14,911,569 8,055,875 1,281,375 3,334,500 2,574,500 920,675 5,450,000 400,000 22,016,925 $ $ $ $ $ $ $ $ 34,720,749 12,805,566 14,127,750 10,682,750 3,234,850 5,450,000 1,600,000 82,621,665 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Net Cash Balance Minimum cash balance Excess (Deficiency) of Cash Leave Financing section empty: Financing: (should not be required) Borrowing Repayment Interest Total Financing Cash Increase (Decrease) $ $ $ 847,345 $ 250,000 $ 597,345 $ 1,523,768 $ 250,000 $ 1,273,768 $ 2,664,657 $ 250,000 $ 2,414,657 $ 2,427,548 $ 250,000 $ 2,177,548 $ 2,383,485 250,000 2,133,485 Cash Balance before interest on balance Interest Earned on Cash Balance Cash Balance after interest on balance $ $ $ 847,345 $ 7,414 $ 854,759 $ 1,523,768 $ 13,333 $ 1,537,101 $ 2,664,657 $ 23,316 $ 2,687,973 $ 2,427,548 $ 21,241 $ 2,448,789 $ 2,383,485 65,304 2,448,789 Grayed cells are not used. $ 1,253,450 $ 1,421,625 $ 2,448,789 Fixed Manufacturing Overhead Rate 161,000 $5,112,000 $31.75 Total Finished Goods Units Produced Total Fixed Overhead Fixed Manufacturing Overhead Rate Ending Finished Goods Inventory-Absorption Direct Material Direct Labor Variable Overhead Fixed Overhead Total product cost per unit manufactured Units in Ending Finished Goods Inventory Finished Goods Ending Inventory Value Use Per Unit Costs $ 255.00 $ 87.75 $ 42.75 $ 31.75 $ 417.25 $ 15,000 6,258,773 Direct Material Direct Labor Variable Overhead Variable Cost per unit manufactured $ $ $ $ Use Per Unit Costs 255.00 87.75 42.75 385.50 Units in Ending Finished Goods Inventory Finished Goods Ending Inventory Value $ 15,000 5,782,500 Ending Finished Goods Inventory - Variable Cost of Goods Manufactured - Absorption For Year Ended 12/31/2018 Direct Materials: Beginning Inventory Direct Material Purchases Direct Materials Available Ending Inventory Direct Materials Used Direct Labor: Overhead: Total Manufacturing Costs Added Plus: Beginning Work in Process Inventory Total Manufacturing Costs Less: Ending Work in Process Inventory Cost of Goods Manufactured Grayed cells not used $ 2,417,060 $ 40,847,940 $ 43,265,000 $ 2,210,000 $ 41,055,000 $ $ $ $ $ Cost of Goods Manufactured - Variable For Year Ended 12/31/2018 Direct Materials Used Direct Labor Variable Overhead Total Variable Manufacturing Costs $ 41,055,000 $14,127,750 41,055,000 $14,127,750 $11,994,750 67,177,500 2,850 67,180,350 2,850 67,177,500 Cost of Goods Sold - Absorption Finished Goods Beginning Inventory Cost of Goods Manufactured Finished Goods Ending Inventory Cost of Goods Sold Cost of Goods Sold - Variable Finished Goods Beginning Inventory Cost of Goods Manufactured Finished Goods Ending Inventory Cost of Goods Sold Diesel Dynamo Company Absorption Costing Income Statement For the Year Ended 12/31/2018 Revenue Less: Cost of Goods Sold Gross Margin Less: Operating Expenses Selling & Administrative Expense Bad Debt Expense Operating Income Plus: Interest Revenue Income Before Taxes Grayed cells not used 030613-PJS Diesel Dynamo Company Variable Costing Income Statement For the Year Ended 12/31/2018 Revenue Less: Variable Costs Variable Cost of Goods Sold Variable Selling & Administrative Bad Debt Expense Contribution Margin Less: Fixed Costs Fixed Manufacturing Cost Fixed Selling & Administrative Operating Income Plus: Interest Revenue Income Before Taxes Grayed cells not used $ - $ - Diesel Dynamo Company Absorption Costing Statement of Retained Earnings For the Year Ended 12/31/2018 Retained Earnings 1/1/2018 Plus: Net Income Less: Dividends Retained Earnings 12/31/2018 Diesel Dynamo Company Balance Sheet For the Year Ended 12/31/2018 ASSETS Cash Raw Materials Inventory Work in Process Inventory Finished Goods Inventory Accounts Receivable (net) Plant and Equipment Total Assets $ 2,850 LIABILITIES AND STOCKHOLDER'S EQUITY Accounts Payable Common Stock $ 26,234,500 Retained Earnings Total Equity Breakeven Analysis Variable Cost Per Unit Quantity Label for Quantity hours per unit amount of Raw Materials per unit DL hours per unit percentage of sales price percentage of sales price Price Per 1 Unit of Quantity Model Information: Per Unit Selling Price Variable Costs Contribution Margin Total Fixed Expense Fixed S & A Fixed Overhead Total Fixed Cost Breakeven: units Table of Information for Graph showing Breakeven Point Units Produced Total Cost 0 4,000 8,000 12,000 16,000 20,000 24,000 28,000 32,000 36,000 40,000 44000 48,000 52,000 56,000 60,000 64,000 CREATE A GRAPH SHOWING REVENUE AND TOTAL COST BASED ON THE TABLE ABOVE. Cost per Finished Goods Unit Revenue SED ON THE TABLE ABOVE. Description of cost Direct Labor Direct Materials Variable Overhead Variable Selling & Administrative Cost Bad Debts Expense Var. Costs/unit Flexible Budget - Production Costs Grayed cells not used Unit Costs Units of Production Budgeted Production Cost: Direct Materials Direct Labor Variable Overhead Total Variable Production Cost Fixed Cost Total Budgeted Production Cost Actual Production Flexible Budget Flexible Budget Assumed Actual Level Level 1 Level 2 Budget Check Figures Diesel Dynamo Company Fall 2017 Checkfigures for W22511836 PROFESSIONAL APPLICATION PART I Total Sales Total Production $77,380,000 Annual Total in Dollars 161,000 Annual Total in units Direct Material $40,847,940 Annual Total in Dollars Direct Labor $14,127,750 Annual Total in Dollars Overhead $11,994,750 Annual Total in Dollars Sales & Admin Exp $3,454,850 Annual Total in Dollars Schedule of Cash Collections $ Schedule of Cash Payments $ Cash Budget Finished Goods Inventory $ $ 1,253,450 Year End Net Accounts Receivable 1,421,625.00 Year End Accounts Payable $2,448,789 Year end Balance 6,258,773 Absorption Costing 5,782,500 Variable Costing PROFESSIONAL APPLICATION PART II Cost of Goods Manufactured $ $ 67,177,500 Absorption Costing - Variable Costing Cost of Goods Sold $0 Absorption Costing $0 Variable Costing Income Statements $0 Net Income Absorption Costing $0 Net Income Variable Costing Retained Earnings Balance Sheet $0 Ending Retained Earnings $0 Total Assets $0 Total Liabilities and Shareholder's Equity Breakeven Analysis $ - Variable cost per unit Breakeven Analysis - Flexible Budget Units $0 Total Budgeted Costs at "actual" (assumed) production