Seedling Manufacturing Company, a company that will start production in 2021, plans to manufacture and sell grain combines (harvesters) to farmers who produce grains such as wheat, oats and barley. During July 2020, Shannon Trepans, president and major shareholder of Seedling, approached First Security Bank for an operating loan to cover working capital needs for 2021. The bank loan officer requested a budgeted projection of the firm's operating cash flows and income statement for 2021. The cash-flow projection will be used to help determine if the firm can repay the loan as required. Seedling plans to hire and train a team of skilled employees; to utilize these employees throughout the year, Seedling plans to produce an equal number of harvesters each month to satisfy the annual demand. The practical capacity of the plant to be completed soon will be 30 harvesters per month although current plans are to produce only 25 units per month Sales and Cash Collections: Shannon has conducted marketing studies of the potential demand for harvesters and estimates the 2021 demand at 300 harvesters if the sales price is $45,500 each. Sales for the year are expected to occur in the following pattern: March 10%, April, 15%, May, 20%: June, 25%; July, 15% August, 10% and September, 5%. All sales will be made on credit with collections expected in the following pattern: 60% in the month following sale: 39% in the second month following sale, and 1% never collected. Seedling will grant customers a 5% discount if they pay in the month following sale, this discount should generate the 60% collection rate in the month following sale. Finished Goods Inventory, January 1, 2021: On January 1, 2021, Seedling expects to have 90 harvesters in inventory. These harvesters will be produced during September through December 2020. The unit manufacturing cost of the harvesters produced during 2020 is expected to be as follows: Variable manufacturing costs $20,800 Fixed manufacturing costs 15.000 Total $35.800 Manufacturing Costs: Variable manufacturing costs per unit for 2021 are budgeted as follows: Direct materials per unit $16,750 Variable overhead per unit $6,500 Materials and variable overhead are acquired as needed during the production process, i.e. there are no inventories of materials. Purchases of materials are paid 70 percent in the month incurred and 30 percent in the following month. Variable overhead is paid in the month incurred Annual fixed manufacturing costs for 2021 are budgeted to be as follows: Salaries and wages $1,500,000 Depreciation 564,000 Property taxes 300,000 Insurance 276,000 Utilities 984,000 Other 540,000 Total S4.164.000 Salaries and wages, utilities, and other manufacturing costs are incurred and paid at an equal amount over the 12 months. Depreciation is calculated using the straight-line method and spread uniformly over the months of the year. The cash payment for property taxes occurs in equal semi-annual installments in February and August of each year. The insurance is paid in equal quarterly installments in January, April, July, and October Marketing and Administrative Costs: Fixed marketing and administrative costs consist of warehouse storage, promotion and other costs. The inventory of unsold harvesters will be stored in rented space in a warehouse that is located near the manufacturing facility. The warehouse storage costs are $20,000 per month. They are paid in the month incurred. The promotion costs are budgeted and paid as follows: March, $10,000April, $30,000; May, $30,000; June, $15,000; and July, $15,000. Other fixed marketing and administrative costs are $35,000 per month. These costs are paid in the month incurred. Variable marketing and administrative consists of shipping and selling costs. Shipping costs are $2,500 per unit sold and selling costs are one percent (1%) of sales revenue. These costs are paid in the month when the sales occur. Cash Balance: The cash balance for Seedling is expected to be $80,000 on January 1, 2021. The fimm wishes to maintain a minimum cash balance of $75,000. Accounts Payable Balance: The accounts payable balance on January 1, 2021, for purchases of materials is expected to be $72,000. Accounting Practices: Seedling employs the following accounting practices: 1. Absorption costing is used for budgeted financial statements prepared for creditors and owners. 2. Finished goods inventory costs are determined using first-in, first-out (FIFO) cost flow assumption 3. Work-in-process inventory is expected to be approximately the same amount at the beginning and end of each month because production is scheduled evenly throughout the year. Since the beginning and ending monthly balances in work-in-process inventories will be the same amount, they are ignored in calculating the budgeted cost of goods manufactured 3. The estimated uncollectible portion of any month's sales and the estimated cash discounts are deducted from sales to arrive at net sales. 4. For this project, income taxes may be ignored. Operating Loan Requirements: First Security Bank has agreed to extend an operating loan to Seedling Manufacturing Company under the following conditions 1. The minimum cash balance to be maintained during any month in a checking account at First Security is $75,000. All loans are made on the first day of the month when the budget shows financing is needed. To be consistent, assume any repayments of loans occur on the first day of the month when the budget shows funds are available for repayment 2. Interest is payable on the first day of each month at the rate of 0.85% of the balance of the loan outstanding during the preceding month. Required: A. Prepare the following budgets for the twelve months beginning January 2021. The cash budget should indicate the nature and extent of any financing required over this period. 1. Schedule A: Sales Budget (The sales budget is prepared for you to illustrate how the formulas should reference data in the data section of the spreadsheet.) 2. Schedule B: Manufacturing Cost Budget 3. Schedule C: Finished Goods Inventory Budget 4. Schedule D: Cash Required for Operations Budget 5. Schedule E: Cash Budget Showing Financing Required 6. Schedule F: Budgeted Income Statement for the Year Ending December 31, 2021 The budget schedules with labels are provided in the Excel file named A204 Budget Project 2020 Fall - Template. These schedules should be completed using formulas that contain references to cells in the data section (cells Al to G76) and to previously calculated values. Do not calculate amounts manually and enter those amounts in the spreadsheet because changes in the data must flow through the schedules to complete requirements (B) and (C). B. Seedling is thinking about raising the price of the harvester. If the price (cell G5) is raised to $49,500, Shannon expects that demand (cell D5) will fall to 240 harvesters. If Seedling elects the price increase, monthly production will be adjusted and the January I inventory (cell F23) will be only 75 machines. Also, salaries and wages costs (cell F38) will be reduced to $1,400,000 annually. All other costs will remain as before. Based on the absorption costing income before taxes and the ending cash balance, is the price increase desirable? Print schedules A through F with the new forecasts to PDF along with your recommendation as to whether the price increase is desirable. After printing the revised schedules, return the data to the original before answering part c. C Sunbelt Banks has offered to meet Seedling's loan requirements. The conditions for this loan would be the same as First Security's except that the required cash balance (cell F74) would be $150,000 instead of the $75,000 required by First Security. However, to compensate for the higher compensating balance, the monthly interest rate (cell F75) would be 0.65% (1.0.0.0065) of the balance of the loan outstanding. Based on 2021 interest expense, is a change in the banks worthwhile? Print schedules A through F of the solution with the original sales and cost data and the Sunbelt Bank terms to PDF along with your recommendation as to whether the change in banks is worthwhile. The January 1 cash balance (cell F67) should be left at $80,000. After you have printed the solutions to Requirements B and C, return the data to the original and submit the excel file and the PDF solutions to Requirements, A, B, and C in Canvas to fulfill the requirements of the project. Seedling Manufacturing Company, a company that will start production in 2021, plans to manufacture and sell grain combines (harvesters) to farmers who produce grains such as wheat, oats and barley. During July 2020, Shannon Trepans, president and major shareholder of Seedling, approached First Security Bank for an operating loan to cover working capital needs for 2021. The bank loan officer requested a budgeted projection of the firm's operating cash flows and income statement for 2021. The cash-flow projection will be used to help determine if the firm can repay the loan as required. Seedling plans to hire and train a team of skilled employees; to utilize these employees throughout the year, Seedling plans to produce an equal number of harvesters each month to satisfy the annual demand. The practical capacity of the plant to be completed soon will be 30 harvesters per month although current plans are to produce only 25 units per month Sales and Cash Collections: Shannon has conducted marketing studies of the potential demand for harvesters and estimates the 2021 demand at 300 harvesters if the sales price is $45,500 each. Sales for the year are expected to occur in the following pattern: March 10%, April, 15%, May, 20%: June, 25%; July, 15% August, 10% and September, 5%. All sales will be made on credit with collections expected in the following pattern: 60% in the month following sale: 39% in the second month following sale, and 1% never collected. Seedling will grant customers a 5% discount if they pay in the month following sale, this discount should generate the 60% collection rate in the month following sale. Finished Goods Inventory, January 1, 2021: On January 1, 2021, Seedling expects to have 90 harvesters in inventory. These harvesters will be produced during September through December 2020. The unit manufacturing cost of the harvesters produced during 2020 is expected to be as follows: Variable manufacturing costs $20,800 Fixed manufacturing costs 15.000 Total $35.800 Manufacturing Costs: Variable manufacturing costs per unit for 2021 are budgeted as follows: Direct materials per unit $16,750 Variable overhead per unit $6,500 Materials and variable overhead are acquired as needed during the production process, i.e. there are no inventories of materials. Purchases of materials are paid 70 percent in the month incurred and 30 percent in the following month. Variable overhead is paid in the month incurred Annual fixed manufacturing costs for 2021 are budgeted to be as follows: Salaries and wages $1,500,000 Depreciation 564,000 Property taxes 300,000 Insurance 276,000 Utilities 984,000 Other 540,000 Total S4.164.000 Salaries and wages, utilities, and other manufacturing costs are incurred and paid at an equal amount over the 12 months. Depreciation is calculated using the straight-line method and spread uniformly over the months of the year. The cash payment for property taxes occurs in equal semi-annual installments in February and August of each year. The insurance is paid in equal quarterly installments in January, April, July, and October Marketing and Administrative Costs: Fixed marketing and administrative costs consist of warehouse storage, promotion and other costs. The inventory of unsold harvesters will be stored in rented space in a warehouse that is located near the manufacturing facility. The warehouse storage costs are $20,000 per month. They are paid in the month incurred. The promotion costs are budgeted and paid as follows: March, $10,000April, $30,000; May, $30,000; June, $15,000; and July, $15,000. Other fixed marketing and administrative costs are $35,000 per month. These costs are paid in the month incurred. Variable marketing and administrative consists of shipping and selling costs. Shipping costs are $2,500 per unit sold and selling costs are one percent (1%) of sales revenue. These costs are paid in the month when the sales occur. Cash Balance: The cash balance for Seedling is expected to be $80,000 on January 1, 2021. The fimm wishes to maintain a minimum cash balance of $75,000. Accounts Payable Balance: The accounts payable balance on January 1, 2021, for purchases of materials is expected to be $72,000. Accounting Practices: Seedling employs the following accounting practices: 1. Absorption costing is used for budgeted financial statements prepared for creditors and owners. 2. Finished goods inventory costs are determined using first-in, first-out (FIFO) cost flow assumption 3. Work-in-process inventory is expected to be approximately the same amount at the beginning and end of each month because production is scheduled evenly throughout the year. Since the beginning and ending monthly balances in work-in-process inventories will be the same amount, they are ignored in calculating the budgeted cost of goods manufactured 3. The estimated uncollectible portion of any month's sales and the estimated cash discounts are deducted from sales to arrive at net sales. 4. For this project, income taxes may be ignored. Operating Loan Requirements: First Security Bank has agreed to extend an operating loan to Seedling Manufacturing Company under the following conditions 1. The minimum cash balance to be maintained during any month in a checking account at First Security is $75,000. All loans are made on the first day of the month when the budget shows financing is needed. To be consistent, assume any repayments of loans occur on the first day of the month when the budget shows funds are available for repayment 2. Interest is payable on the first day of each month at the rate of 0.85% of the balance of the loan outstanding during the preceding month. Required: A. Prepare the following budgets for the twelve months beginning January 2021. The cash budget should indicate the nature and extent of any financing required over this period. 1. Schedule A: Sales Budget (The sales budget is prepared for you to illustrate how the formulas should reference data in the data section of the spreadsheet.) 2. Schedule B: Manufacturing Cost Budget 3. Schedule C: Finished Goods Inventory Budget 4. Schedule D: Cash Required for Operations Budget 5. Schedule E: Cash Budget Showing Financing Required 6. Schedule F: Budgeted Income Statement for the Year Ending December 31, 2021 The budget schedules with labels are provided in the Excel file named A204 Budget Project 2020 Fall - Template. These schedules should be completed using formulas that contain references to cells in the data section (cells Al to G76) and to previously calculated values. Do not calculate amounts manually and enter those amounts in the spreadsheet because changes in the data must flow through the schedules to complete requirements (B) and (C). B. Seedling is thinking about raising the price of the harvester. If the price (cell G5) is raised to $49,500, Shannon expects that demand (cell D5) will fall to 240 harvesters. If Seedling elects the price increase, monthly production will be adjusted and the January I inventory (cell F23) will be only 75 machines. Also, salaries and wages costs (cell F38) will be reduced to $1,400,000 annually. All other costs will remain as before. Based on the absorption costing income before taxes and the ending cash balance, is the price increase desirable? Print schedules A through F with the new forecasts to PDF along with your recommendation as to whether the price increase is desirable. After printing the revised schedules, return the data to the original before answering part c. C Sunbelt Banks has offered to meet Seedling's loan requirements. The conditions for this loan would be the same as First Security's except that the required cash balance (cell F74) would be $150,000 instead of the $75,000 required by First Security. However, to compensate for the higher compensating balance, the monthly interest rate (cell F75) would be 0.65% (1.0.0.0065) of the balance of the loan outstanding. Based on 2021 interest expense, is a change in the banks worthwhile? Print schedules A through F of the solution with the original sales and cost data and the Sunbelt Bank terms to PDF along with your recommendation as to whether the change in banks is worthwhile. The January 1 cash balance (cell F67) should be left at $80,000. After you have printed the solutions to Requirements B and C, return the data to the original and submit the excel file and the PDF solutions to Requirements, A, B, and C in Canvas to fulfill the requirements of the project