Tatra Treats, Inc. is a candy manufacturer that makes and distributes various chocolate and peanut butter candies. Recently the companys accountant has taken a job at a competitor. Tatra is asking you to prepare the budget for the 3rd quarter of 2020. To help, the company has provided you with a copy of the 2nd quarter budget prepared by the old accountant as well as a blank template you can use to prepare the 3rd quarter budget. Remember that the ending balance sheet in the Q2 budget is the beginning balance sheet for the Q3 budget. The company provides the following information with regards to its expected operations for Q3:

The company expects to sell 12,000 cases in July, 16,000 cases in August, 17,000 cases in September, and 20,000 cases in October. The price of a case will increase to $28. The company is going to make a better effort to collect its accounts and therefore expects to collect 70% of its receivables in the month of sale with 20% collected in the following month and 10% collected in the second month following sale. For administrative ease, assume all outstanding account receivable at the end of Q2 will be collected in the first month of Q3.

The company plans to keep 25% of next months sales in finished goods inventory. The ending finished goods inventory at the end of Q2 is budgeted to be 3,000 cases. The desired ending inventory for October is 5,000 cases.

It takes 15 pounds of chocolate and peanut butter to make a case. The company plans to have 18,000 pounds in inventory at the end of Q2. It costs the company $0.35 a pound to purchase the raw materials. The company would like to have 10% of the following months needs in ending inventory. Additionally, they plan to have 25,000 pounds of raw materials in ending inventory at the end of October. Because the company will collect it receivables quicker in Q3 it plans to pay 80% of its payables in the month of purchase with the remaining 20% being paid at the end of the next month.

The company estimates that it takes a half hour (0.50) of direct labor hours to produce one case. The company is increasing its hourly wage to $17 hour.

The companys manufacturing overhead estimates and selling administrative expense estimates are the same in Q3 as they were in Q2.The company requires a minimum cash balance of $30,000 at the end of every month. The company has an open line of credit at a local bank that they can borrow from to maintain the minimum balance. It will pay back any amounts owed the following month with any excess cash over the $30,000 minimum. It will pay 1% interest on any cash payments made to the bank. Note that the company can go below $30,000 to pay the required interest.

The company pays its investors a dividend of $50,000 at the conclusion of every quarter.

****The statements and schedules must be prepared in Excel. PLEASE INCLUDE FORMULAS AND/ OR CALCULATIONS for all work

****Needed: Budgeting Assumptions Schedule, Beginning Balance Sheet, Schedules: Sales Budget, Schedule of Expected Cash Collections, Production Budget, Direct Materials Budget, Schedule of Expected Cash Disbursements for Purchases of Raw Material, Direct Labor Budget, Manufacturing Overhead Budget, Ending Finished Goods Inventory Budget, (absorption costing basis), Selling and Admin Expense Budget, Cash Budget, Ending Income Statement, Ending Balance Sheet.

Tatra Treats, Inc. Budgeting Assumptions For the Quarter Ended June 30, 2020 July April 30,000 May 15,000 June 13,000 12,000 $ 26.00 Sales Budget Budgeted Sales in Case Selling Price per Case Percentage of sales collected in the month of sale Percentage of sales collected in the first month after sale Percentage of sales collected in the second month after sale 25% Production Budget Percentage of next month's sales in ending finished goods inventory Beginning Finished Goods inventory in cases Desired ending inventory first month next quarter 2,000 3,000 Direct Materials Budget Pounds of chocolate and peanut butter per case Beginning Raw Materials Inventory in pounds Costs per pound of chocolate and peanut butter Percentage of next month's production needs in ending inventory Desired ending raw materials inventory in pounds first month ex quarter Percentage of purchases paid in the month purchased Percentage of purchases paid in the month after purchase 14,286 0.35 10% 22,500 60% 40% Direct Labor Budget Direct labor-hours required per case Direct labor cost per hour 0.50 16.00 $ Draft Q2 Budget Manufacturing Overhead Budget Variable manufacturing overhead per direct labor-hour Fixed manufacturing overhead per month Depreciation per month $ $ $ 3.00 71,000 15,000 Tatra Treats, Inc. Budgeting Assumptions For the Quarter Ended June 30, 2020 April sune July Selling and Administrative Budget Variable selling and administrative expense per case Fixed selling and administrative expense per month Fixed selling and administrative depreciation expense per month $ $ $ 2.00 70,000 5,000 $ 30,000 Cash Budget Minimum cash balance Equipment purchases Dividends paid last month of the quarter Simple interest per month $ 6,000 $ $ 10,000 - $ $ 5,000 - $ $ 4,000 50,000 1% Draft Q2 Budget Tatra Treats, Inc. Balance Sheet March 31, 2020 100,000 80,000 5,000 24,000 Assets Current Assets Cash Accounts Receivable Raw Materials Finished Goods Inventory Total Current Assets Plant and Equipment Land Buildings and equipment Accumulated Depreciation Plant and Equipment, net Total Assets 209,000 80,000 700,000 (200,000) 580,000 789,000 27,000 Liabilities and Stockholders' Equity Current Liabilities Accounts Payable Bank Credit Total Current Liabilities Stockholders' Equity Common Stock Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity 27,000 175,000 587,000 762,000 789,000 Draft Q2 Budget Tatra Treats, Inc. For the Quarter Ended June 30, 2020 6 of 11 May June Total D a les cases) Selling price per unit Total Sales 30.000 2600 $ 15.000 13.000 26.00 $ 3,000 $ S8.000 26.00 1.506,000 12.000 26.00 312,000 10.000 Schedule of Expected Cash Collections Beginning Accounts Receivable $ 40.000 468,000 200.000 156,000 $ 214.000 Second-month sales Third month sales Total Cash Colections 508,000 $ 202,800 1,374,800 Production Budget 13.000 3.000 3,750 Budgeted unit Sales Add: desired ending inventory Totales Less: beginning inventory Required production 3,000 15.000 31,750 2.SUNaet) 12,750 59,000 Direct Materials Budget 14,500 12,750 9.000 12.000 217.500 22,500 Required production in cases Unit of raw materials needed percase Units of raw materials needed to meet production Add: desired unitsin ending inventory Total units of raw materials Les units of beginning raw materials Units of raw materials to be purchased Cost of raw materials per pound Cost of raw materials to be purchased 8000 4286 433.714 885,000 18.000 903,000 14,286 21.750 191,250 18000 209,250 19,135 190175 0.35 $ 665445 18.000 184,500 0.35 0355 169,00 $ Tatra Treats, Inc. For the Quarter Ended June 30, 2020 7 of 11 pected Cash Disbursements for Purchases of Raw Materials 27.000 101.5805 27000 169 300 Beginning Accounts Payable First month purchases Second month purchases Third month purchases Total Cash Disbursements 67,220 45,124 $ 75,206 30,082 39.925 70,008 S 128.500 5 112,8443 39.926 311432 Direct Labor Budget 31.750 Required production in cases Direct labor hours per case Total direct labor hours needed Direct labor cost per hour Total direct labor cost 15.875 16.00 254.000 14,500 0.50 7,250 16.00 116000 12150 0.50 375 16.00 102,000 59000 0.50 29.500 15.00 472.000 $ $ $ S $ Manufacturing Overhead Budget 29.500 3.00 Budgeted direct labor hours Variable manufacturing OH rate Variable manufacturing overhead Fixed manufacturing overhead Total manufacturing overhead Less depreciation Cash disbursements for manufacturing overhead 15875 300 $ 47 625 $ 71.000 118,625 15.000 7,250 3.00 $ 21,7505 71.000 92,750 15.000 77,750 $ 6,375 3.00 $ 19,125 5 71,000 90,125 88.500 213.000 301.500 45,000 256.500 75,125$ Total manufacturing overhead Budgeted direct labor hours Predetermined overhead rate 301.500 20.500 10.22 Draft Q2 Budget Tatra Treats, Inc. For the Quarter Ended June 30, 2020 Ending Finished Goods Inventory Budget (absorption costing basis) Quantity Cost Total 0.35 $ Production cost per case: Direct Materials Direct Labor Manufacturing Overhead Unit product cost 15 $ 050 $ 050 $ 16.00 10.22 Budgeted finished goods inventory Ending finished goods inventory in cases Unit product cost Ending finished goods inventory Selling and Administrative Expense Budget 30,000 2.00 60,000 70,000 130.000 $ $ $ $ Budgeted unit Sales Variable selling and admin expense per case Variable selling and admin expense Fixed Selling and admin expense Total seling and admin expense Less depreciation Cash disbursements for selling and admin expenses $ 15.000 2.00 30.000 70.000 100,000 5.000 95.000 13,000 2.00 26,000 20.000 96.000 5.000 91.000 Draft Q2 Budget 58.000 2.00 116.000 210.000 326.000 15.000 311000 125.000 $ $ Tatra Treats, Inc For the Quarter Ended June 30, 2020 Cash Budget $ $ 29,766 $ 100,000 508.000 608.000 30,000 430,000 460,000 436,800 100.000 1,374,800 1,474,800 112.844 116.000 128.580 254,000 103,625 125.000 10.000 77,750 Beginning Cash Balance Add: Collections from customers Totalcash available Less: Cash Disbursements Direct Materials Direct Labor Manufacturing Overhead Selling and Admin Expenses Equipment Purchases Dividends Total Cash Disbursements Excess (deficiency of cash available Financing Borrowings at the beginning of month) Repayments at end of month) Interest (recorded when payments made) Total Financing Ending Cash Balance 95,000 5,000 70,008 102,000 75,125 1000 4000 50,000 392,133 74 433 311.432 472.000 256500 311.000 19.000 50,000 1,419.932 54868 621 205 (13,205) 406,594 51.406 43205 ( 2406) (234) (23,640 29.766 (19,799) (198) (19,997) 54,436 43205 141205) (432) (432) 54436 43,205 30,000 $ $ $ Draft Tatra Treats, Inc. Income Statement For the Quarter Ended June 30, 2020 Sales Less: Cost of Goods Sold Gross Margin Less: Selling and Admin Expense Net Operating Income Less: Interest Expense Net Income 1,508,000 1,052 169 455,831 326,000 129,831 432 129,399 Schedule of Cost of Goods Sold 24,000 Beginning Finished Goods Inventory Plus: Cost of Units Produced Quantity of units produced Cost of units produced Good Available for Sale Less: Finished Goods Inventory Cost of Goods Sold 1,083,250 1,107,250 55,081 1,052,169 $ Schedule of Retained Earnings Beginning Retained Earnings Plus: Net Income Less: Dividends Paid Ending Retained Earnings 587.000 129,399 50,000 666,399 Draft Q2 Budget Tatra Treats, Inc. Balance Sheet June 30, 2020 54,436 213,200 6,300 55,081 Assets Current Assets Cash Accounts Receivable Raw Materials Finished Goods Inventory Total Current Assets Plant and Equipment Land Buildings and equipment Accumulated Depreciation Plant and Equipment, net Total Assets 329.017 80,000 719,000 (260,000) 539,000 868,017 26,618 26,618 Liabilities and Stockholders' Equity Current Liabilities Accounts Payable Bank Credit Total Current Liabilities Stockholders' Equity Common Stock Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity 175,000 666,399 $ 841,399 868,017 Draft Q2 Budget