Tatra Treats, Inc. is a candy manufacturer that makes and distributes various chocolate and peanut butter candies. Recently the company’s 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 month’s 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 company’s 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.

  
 
 
 
 
 
 
 

Transcribed Image Text:

Tatra Treats, Inc. Budgeting Assumptions For the Quarter Ended June 30, 2020 June July Sales Budget Budgeted Sales in Case 15,000 13,000 12,000 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 25% Beginning Finished Goods inventory in cases 2,000 3,000 Desired ending inventory first month next quarter Direct Materials Budget Pounds of chocolate and peanut butter per case Beginning Raw Materials Inventory in pounds 15 14,286 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 0.35 10% 22,500 60% 40% Direct Labor Budget Direct labor-hours required per case Direct labor cost per hour Draft Q2 Budgèt 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 June July Sales Budget Budgeted Sales in Case 15,000 13,000 12,000 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 25% Beginning Finished Goods inventory in cases 2,000 3,000 Desired ending inventory first month next quarter Direct Materials Budget Pounds of chocolate and peanut butter per case Beginning Raw Materials Inventory in pounds 15 14,286 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 0.35 10% 22,500 60% 40% Direct Labor Budget Direct labor-hours required per case Direct labor cost per hour Draft Q2 Budgèt 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