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 plans on spending $8,000 on new equipment in July, $10,000 in August, $6,000 in September, and $4,000 in October.

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.

****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 April May June July 30,000 15,000 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 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 25% 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 next quarter Percentage of purchases paid in the month purchased Percentage of purchases paid in the month after purchase Draht Q2 Budget 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 $ 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 May June 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 $ Draft Q2 Budget 27,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 175,000 587,000 $ 762,000 789,000 Tatra Treats, Inc. For the Quarter Ended June 30, 2020 6 of 11 April May Total D $8.000 u sales cases) Selling price per unit Total Sales 30,000 26.00 $ 70,000 $ 15.000 26.00 $ 390,000 $ 13.000 26.00 $ 338,000 $ 26.00 $ 26.00 $ Schedule of Expected Cash Collections $ 40,000 668,000 Beginning Accounts Receivable First month sales Second-month sales Third-month sales Total Cash Collections 40.000 156,000 234.000 $ 80.000 2000 312.000 202,800 1,374,800 508,000 $ Production Budget 30.000 13.000 S8.000 12.000 3,000 Budgeted unit sales Add: desired ending inventory Total needs Lew beginning tentory Required production 10 2.000 16,000 3,250 12,710 61,000 2,000 99,000 3,000 31,70 Direct Materials Budget Draft Qe Budget 14.500 12,750 59,000 12.000 191.250 180,000 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 Less 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 217.500 19.135 236,625 21,750 214,875 0.35 209,250 19,125 190,125 0.355 665445 885,000 18,000 903.000 14.286 E74 202,500 18.000 184,500 5 SS Tatra Treats, Inc. For the Quarter Ended June 30, 2020 7 of 11 {pected Cash Disbursements for Purchases of Raw Materials 27,000 101.580 27,000 169,300 $ Beginning Accounts Payable First month purchases Second month purchases Third month purchases Total Cash Disbursements 67.720 45,124 $ 30,082 39.925 70,008 75 206 39.926 128.580 $ 112.8445 311,432 Direct Labor Budget 12.50 14.500 0.50 Required production in cases Direct labor hours per case Total direct labor hours needed Direct labor cost per hour Total direct labor cost 31.750 0.50 15,875 16.00 254,000 7.250 0.50 6,375 16.00 102,000 59.000 0.50 29,500 16.00 472,000 16.00 $ $ $ $ Manufacturing Overhead Budget $ $ 15,875 3.00 47,625 71,000 118,625 $ $ Budgeted direct labor hours Variable manufacturing OH rate Variable manufacturing overhead Fixed manufacturing overhead Total manufacturing overhead Less depreciation Cash disbursements for manufacturing overhead S 7,250 3.00 21,750 71.000 92.750 15,000 77,750 6,375 3.00 19,125 71.000 90,125 15,000 75,125 Drah Q2 Budget 29,500 3.00 88,500 213,000 301,500 45,000 256,500 15.000 103,625 S $ $ Total manufacturing overhead Budgeted direct labor hours Predetermined overhead rate 301,500 29,500 10.22 Tatra Treats, Inc. For the Quarter Ended June 30, 2020 Ending Finished Goods Inventory Budget (absorption costing basis) Quantity Cost Total Production cost per case Direct Materials Direct Labor Manufacturing Overhead Unit product cost 15 0.50 0.50 $ $ $ 0.35S 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 13.000 58.000 2.00 $ $ $ 2.00 s Budgeted unit Sales Variable seling and admin expense per case Variable seling and admin expense Fixed Selling and admin expense Total selling and admin expense Less depreciation Cash disbursements for selling and admin expenses 30.000 2.00 60,000 70,000 130,000 5,000 125,000 15.000 2.00 30,000 $ 70,000 100,000 5,000 95,000 $ 26,000 20.000 96,000 5.000 91.000 116,000 210,000 326,000 15,000 311,000 $ $ Draft Q2 Budget 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,832 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 3 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