Question: Can some please help me with how to do this on Excel Spreadsheet? The given information is: Fall Creations Inc. is preparing budgets for the
Can some please help me with how to do this on Excel Spreadsheet? The given information is: Fall Creations Inc. is preparing budgets for the second quarter ending June 30, 2021.
SALES BUDGET
Budgeted sales of the companys only product for the next five months are:
April ......... 26,000 units
May .......... 39,400 units
June .......... 37,000 units
July ........... 32,000 units
August ...... 20,500 units
The selling price is $11.15 per unit.
The Production budget:
Percentage of next month's production in ending inventory...... 5120
Expected March 31 balance in units.... 4,100
The given information is: Fall Creations Inc. is preparing budgets for the second quarter ending June 30, 2021.
SALES BUDGET
Budgeted sales of the companys only product for the next five months are:
April ......... 26,000 units
May .......... 39,400 units
June .......... 37,000 units
July ........... 32,000 units
August ...... 20,500 units
The selling price is $11.15 per unit.
Production budget:
. Percentage of next month's production in ending inventory.... 5120
. Expected March 31 balance in units.... 4,100 units
DIRECT MATERIALS BUDGET Additional data: 6 pounds of material are required per unit of product. Management desires to have materials on hand at the end of each month equal to 10% of the following month's production needs. The beginning materials inventory was 16.500 pounds The material costs 50:42 per pound. SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIAL Additional data: Sixty percent of a month's purchases are paid for in the month of purchase: 40 paid for in the following month. No discounts are given for early payment. The accounts payable balance on March 31 was $21,040 DIRECTBUDGET Additional data: Each unit produced requires 0.05 hour of direct labor. Each hour of direct labor costs the company S1625. Management fully adjusts the workforce to the workload cach month MANUFACTURING OVERHEAD BUDGET Additional data: Variable manufacturing overhead is $23 per direct laber-hour. Fixed manufacturing overhead is S52,600 per month. This includes $22.900 in depreciation, which is not a cash outflow. ENDING FINISHED GOODS INVENTORY BUDGET Additional data: The Company uses absorption costing in its budgeted income statement and balance sheet Manufacturing overhead is applied to units of product on the basis of direct labor hours. The company has no work in process inventorics SELLING AND ADMINISTRATIVE EXPENSE BUDGET Additional data Variable selling and administrative expenses are $0.60 per unit sold. Fixed selling and administrative expenses are $70,000 per month and includes $15,000 in dissation DIRECT MATERIALS BUDGET Additional data: 6 pounds of material are required per unit of product Management desires to have materials on hand at the end of each month equal to 10% of the following month's production needs. The beginning materials inventory was 16.500 pounds . The material costs $0.42 per pound SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIAL Additional data: Sixty percent of a month's purchases are paid for in the month of purchase, 40% is paid for in the following month. No discounts are given for early payment The accounts payable balance on March 31 was $21,040 DIRECTBUDGET Additional data: Each unit produced requires 0.05 hour of direct labor Each hour of direct labor costs the company $16.25 Management fully adjusts the workforce to the workload cach month MANUFACTURING OVERHEAD BUDGET Additional data: Variable manufacturing overhead is 23 per direct labor-hour Fixed manufacturing overhead is $52.600 per month. This includes $22.900 in depreciation, which is not a cash outflow ENDING FINISHED GOODS INVENTORY BUDGET Additional data: The Company uses absorption costing in its budgeted income statement and balance sheet Manufacturing overhead is applied to units of product on the basis of direct laber- hours. The company has no work in process inventories SELLING AND ADMINISTRATIVE EXPENSE BUDGET Additional data: Variable selling and administrative expenses are 80.68 per unit sold. Fixed selling and administrative expenses are $70,000 per month and includes $15,000 . in derociation DIRECT MATERIALS BUDGET . Additional data: 6 pounds of material are required per unit of product. Management desires to have materials on hand at the end of each month equal to 10% of the following month's production needs. The beginning materials inventory was 16,500 pounds. The material costs $0.42 per pound. SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIAL Additional data: Sixty percent of a month's purchases are paid for in the month of purchase: 40% is paid for in the following month. No discounts are given for early payment. . CASH BUDGET Additional data: 1. A line of credit is available at a local bank that allows the company to borrow up to $75,000 in one-thousand-dollar increments. a. All borrowing occurs at the beginning of the month, and all repayments occur at the end of the month. b. The company does not have to make any payments until the end of the quarter. The interest rate is 10% annually. 2. Fall Creations Inc. desires a cash balance of at least $25,000 at the end of each month. The cash balance at the beginning of April was $13,600. 3. Cash dividends of $20,000 are to be paid to stockholders in April. C. CASH BUDGET Additional data: 1. A line of credit is available at a local bank that allows the company to borrow up to $75,000 in one-thousand-dollar increments. a. All borrowing occurs at the beginning of the month, and all repayments occur at the end of the month. b. The company does not have to make any payments until the end of the quarter. The interest rate is 10% annually. 2. Fall Creations Inc. desires a cash balance of at least $25,000 at the end of each month. The cash balance at the beginning of April was $13,600. 3. Cash dividends of $20,000 are to be paid to stockholders in April. C. a DIRECT MATERIALS BUDGET Additional data: 6 pounds of material are required per unit of product. Management desires to have materials on hand at the end of each month equal to 10% of the following month's production needs. The beginning materials inventory was 16,500 pounds. The material costs $0.42 per pound. . SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIAL Additional data: Sixty percent of a month's purchases are paid for in the month of purchase: 40% is paid for in the following month. No discounts are given for early payment. . The accounts payable balance on March 31 was $21,040 DIRECTBUDGET Additional data: Each unit produced requires 0.05 hour of direct labor. Each hour of direct labor costs the company $16.25. Management fully adjusts the workforce to the workload cach month. MANUFACTURING OVERHEAD BUDGET Additional data: Variable manufacturing overhead is $23 per direct labor-hour. Fixed manufacturing overhead is $52,600 per month. This includes $22,900 in depreciation, which is not a cash outflow. ENDING FINISHED GOODS INVENTORY BUDGET Additional data: The Company uses absorption costing in its budgeted income statement and balance sheet. Manufacturing overhead is applied to units of product on the basis of direct labor- hours. The company has no work in process inventories, SELLING AND ADMINISTRATIVE EXPENSE BUDGET Additional data: Variable selling and administrative expenses are $0.60 per unit sold. Fixed selling and administrative expenses are $70,000 per month and includes $15,000 in depreciation . CASH BUDGET Additional data: 1. A line of credit is available at a local bank that allows the company to borrow up to $75,000 in one-thousand-dollar increments. a. All borrowing occurs at the beginning of the month, and all repayments occur at the end of the month. CH 2. b. The company does not have to make any payments until the end of the quarter. The interest rate is 10% annually. Fall Creations Inc. desires a cash balance of at least $25,000 at the end of each month. The cash balance at the beginning of April was $13,600. Cash dividends of $20,000 are to be paid to stockholders in April. a 3
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help