Budget Problem: Better Plants, Inc. manufactures small biodegradable seedling starter trays. Josie Chase, owner, wants you to prepare the 2020 master budget for the company. The balance sheet at the end of 2019 follows: $ 435,000 1,113,750 Balance sheet 12/31/19: Current assets: Cash Accounts receivable (net) Inventories: Raw materials Finished goods Supplies Total current assets $ 76,000 315.000 391,000 33.000 $1,972,750 Property, plant & equipment: Land Buildings & equipment Less: accumulated depreciation Total PP&E TOTAL ASSETS $3,750,000 3,575,000 (2.350.000) 4.975,000 $6.947.750 $ 51,688 Current liabilities: Accounts payable Long-term liabilities: Note payable (non-interest bearing; due 12/31/23) Total liabilities Owner's equity TOTAL LIABILITIES & EQUITY 2,000,000 $2,051,688 4,896,063 $6.947,750 Sales volume in Quarter 4 -2019 was 2,250,000 trays. Based on their research, the marketing department forecasts sales volume will be: 1,750,000 trays in Quarter 1 - 2020: 1,000,000 trays in Quarter 2 - 2020; 1,250,000 trays in Quarter 3 - 2020, and 2,000,000 trays in Quarter 4 - 2020. For Quarter 1-2021 and Quarter 2 -2021 they forecast that 1,750,000 and 750,000 trays will be sold, respectively. To achieve the projected sales volume, Josie estimates a selling price of S2.75 per tray. Based on previous estimates of sales fluctuations, the marketing department believes that the desired ending finished goods inventory each quarter should equal 15% of the following quarter's sales quantity. The beginning finished goods inventory in 2020 should be calculated from the 2019 balance sheet. The accounting department estimates that the units in beginning finished goods will have an assigned cost of $1.20 per tray. The trays are produced using a material purchased from a supplier for 9.20 per ounce. It takes 5 ounces of material to produce one tray. To avoid material stock-outs, Josie maintains ending raw materials inventory equal to 20% of the following quarter's production needs. Materials inventory at the beginning of 2020 should be calculated from the 2019 balance sheet. The cost of beginning materials inventory is expected to be $0.20 per ounce. Based on a study of the production processes, it takes 3 minutes to produce one tray. Direct labor workers are paid $12 per hour. The accounting department has gathered the following information about the remaining product and period costs. (For Manufacturing Costs: all variable rates are per unit produced, and all fixed rates are per quarter. For Selling, General, and Administrative Expenses (SGA): all variable rates are per unit of sales, and all fixed rates are per quarter). 0.18 0.12 0.30 0.70 0.25 0.15 1.10 $ Variable Mfg Overhead Costs: Indirect materials Electricity Predetermined var, mf, Overhead rate Fixed Mfg Overhead Costs: Production runs Design costs Supervisor salaries Maintenance and repairs Insurance and property taxes Depreciation Utilities Variable SGA Costs: Sales commissions $ Freight-out Miscellaneous Variable SGA expenses rate $ Fixed SGA expenses: Licensing and fees $ Sales salaries Advertising Clerical wages $ $ 72.000 10,000 130,000 28,000 15.000 95,000 25,000 375.000 15,000 20.000 6.000 21.500 62.500 Assume that there is no beginning or ending work-in-process inventories. Cash information: Cash collections of sales revenue are 80% in the quarter of sale; 18% in the quarter following the sale, and 2% uncollectible. Better Plants recognizes bad debt expense on an accrual basis rather than the direct-write off method. Materials purchased for production are the source of accounts payable. Purchases are paid 75% in the quarter of purchase; and 25% in the quarter following purchase. We pay all our debts. At the beginning of Quarter 1 - 2020, Better Plants borrowed S100.000 from a local financial institution at an annual interest rate of 4%. Repayment of the loan will take place over the year with quarterly payments (at end of each quarter) of $25,000 principal plus accrued interest. The loan will be used to increase factory space and make improvements to the plant. The amounts spent will be as follows: $30,000 in Quarter 1 - 2020; $20,000 in Quarter 2 - 2020; $20,000 in Quarter 3 - 2020; and $25,000 in Quarter 4 - - 2020. Required: Using Excel, prepare the master budget. Begin with the Balance Sheet 2019; include all operating budgets; include a cash budget; and end with the Balance Sheet - 2020. All inputs must be on the initial page of the workbook and all subsequent worksheets linked to the input page. Budgets that are not properly linked will not receive credit! Each budget must be on a separate worksheet within the Excel workbook. Each worksheet tab must be labeled with the name of the budget. Each budget must be properly formatted (title, labels, etc.). Any worksheet cell that involves a calculation must include a formula. Any worksheet cell that uses a value from another budget or table must include a cell reference. Save your excel file as LastName_FirstName_Acct353Budgeting Project Grading Submit an Excel file to the Assignment drop-box through Canvas for grading. To evaluate your work, predetermined values on the initial input page of the budget will be changed. To receive full credit, the change must correctly flow through the related budgets. Example 1 - check figures: If the sales estimate in Quarter 1 - 2020 changes from 1,750,000 units to 3,000,000 units, to receive credit for the assignment, income for the year should change from $1,830,625 to $2,563,060 Example 2 - check figures: If Current Direct Materials Cost per ounce changes from $0.20 to S0.25, to receive credit for the assignment, income for the year should change from $1,830,625 to $1,705,356, Example 3 - check figures: If the revenue collection pattern changes percentages from 80/18/2 to 80/15/5, to receive credit for the assignment, income for the year should change from $1,830,625 to $1,335,625 and assets 12/31/20 from $8,773,937 to $8,278,937. Make other changes to your input data and check to see if the appropriate numbers change in the respective worksheets InputData 2019-BS Projected: Cash collections 2019 In quarter of sale In quarter following sale Uncollectible Sales (units) Sell price 2.250,000 2.75 1,750,000 2.75 1,000,000 2.75 1,250,000 2.75 2,000,000 2.75 1,750,000 2.75 750.000 2.75 80% 18% 2% 100% Qtr 4 - 2019 Qtr 1 - 2020 Qtr 2 - 2020 Qtr 3 - 2020 Qtr 4-2020 Qtr 1 - 2021 Qtr 2 - 2021 Cash collections 2020 In quarter of sale In quarter following sale Uncollectible 80% 18% 2% 100% Ending Finished Goods Inventory Policy: 15% of following quarter's sales Per Unit Cost of Beginning Finished Goods Inventory: Cash payments: In quarter of purchase In quarter following purchase 1.2 75% 25% 100% Direct Materials per Unit: 0.5 ounces Ending Direct Materials Inventory Policy: 20% of following quarter's production needs Direct Material Cost per ounce-previous: Direct Material Cost per ounce-current: Bank loan (begin of qtr): 1st qtr 2nd qtr 3rd qtr 4th qtr Interest rate (annual) Repayment: 1st qtr 2nd qtr 3rd qtr 4th qtr 100,000 0 0 0 4% 0.2 0.2 0.05 hours Direct labor per Unit: Direct Labor Rate per Hour: 25,000 25,000 25,000 25,000 12 Plant additions: 1st qtr 0.18 0.12 0.3 2nd qtr 3rd atr 4th qtr 30,000 20,000 20.000 25,000 Variable Mfg Overhead Costs: Indirect materials Electricity Predetermined var. migoverhead rate Fixed Mfg Overhead Costs: Production runs Design costs Supervisor salaries Maintenance and repairs Insurance and property taxes Depreciation Utilities 0.7 0.25 0.15 1.1 72.000 10.000 130,000 28.000 15,000 95,000 25,000 375,000 Variable SGA Costs: Sales commissions Freight-out Miscellaneous Variable SGA expenses rate Fixed SGA expenses: Licensing and fees Sales salaries Advertising Clerical wages 15,000 20.000 6,000 21,500 62.500 InputData 2019-BS Balance sheet 12/31/19 Current assets: Cash Accounts receivable (net) Inventories Raw materials Finished goods Supplies Total current assets 435.000 1.113,750 Drev qt sales units sell price cash collection% 76.000 315.000 391.000 33.000 1.972.750 3.750.000 3,575.000 -2.350.000 4,975.000 6.9447,750 Property, plant & equipment: Land Buildings & equipment Lost accumulated depreciation Total PP&E TOTAL ASSETS Current liabilities: Accounts payable Long-term liabilities: Note payable (non-interest bearing; due 12/31/23) Total liabilities 51,688 prev at dm purchases cash pmt follow atr 2,000,000 2.051,688 Owner's equity TOTAL LIABILITIES & EQUITY 4.896,062 6.947,750 Total sales Qtr 1 Qtr 2 Qtr 3 Qtr 4 Year $4.812,500 $2,750,000 $3,437,500 $5,500,000 $16,500,000 Units to be produced 1.637,500 1,037,500 1.362.500 1,962,500 6,000,000 $108,500 $110.250 $148,250 $189,000 $556,000 Direct materials purchases Total direct labor cost $982.500 $622,500 $817.500 $1,177.500 $3.600,000 Total manufacturing overhead costs $866,250 $686,250 $783,750 $963,750 $3,300,000 $1,987,500 $1,162,500 $1,437,500 $2,262,500 $6,850,000 Total SGA expenses Ending cash balance $1,568,437 $2,102,875 $2,219.875 $2.700,812 $2.700,812 Unit cost of finished goods Ending finished goods inventory $1.25 for year $328.125 12/31/2020 Cost of goods sold $7,486,875 for year Net income $1,830,625 for year Total assets $8.773.937 12/31/2020