Budget Excel Project – Bloomfield Co. Bloomfield is an ice cream company, who makes homestyle ice creams using fresh and all-natural ingredients and sells to retail stores. In preparing the budget for the coming year, 2024, they forecasted the following expected sales and gathered the costs data from managers in various areas of the company. Sales Forecasted to sell 25,000 units in the 1st Quarter of 2024, and an additional 2,000 units each quarter. The 2,000 additional units per quarter are expected to continue through the end of 2nd quarter of 2025. The 2023 4th quarter sales were 24,000 units. Selling price for one unit of ice cream is $15. Bloomfield maintains 15% of the next quarter’s unit sales in ending inventory. All sales to retail stores are on account, with collections of 75% made in the quarter of sales and 25% collected in the quarter after sale. Accounts receivable on December 31, 2023 is $72,000. Direct Materials Direct materials cost $3 per pound. 1.5 pounds of direct materials are required to produce each unit. Bloomfield maintains 6% of the materials needed for the next quarter in its ending material inventory. Raw Materials on December 31, 2023 were 2,250 pounds. Payment for materials are made 50% in the quarter of purchase, and 50% in the quarter after purchase. Accounts Payable balance on December 31, 2023 was $56,250. Direct Labor Labor required is 15 minutes per unit at a rate of $11 per hour. Manufacturing Overhead Variable overhead costs are based on labor hours, listed below: Indirect materials 50¢ per labor hour Indirect labor 75¢ per labor hour Utilities 65¢ per labor hour Maintenance 35¢ per labor hour Foxed overhead costs are budgeted for the month, listed below: Rent $3,000 per month Salaries $11,000 per month Depreciation $4,800 per month Property taxes $ 675 per month Insurance $ 500 per month Selling and Administrative Variable selling and administrative cost are budgeted per sales unit: Shipping expense $.75 per sales unit Packaging expense $.50 per sales unit Fixed selling and administrative costs are budgeted for the month, listed below: Advertising $2,000 a month Insurance $700 a month Office Salaries $4,000 a month Depreciation $800 a month Other Information The Cash balance at the end of 2023, is $80,750, and Bloomfield would like to maintain a cash balance of at least $200,000 at the end of each quarter. All Direct Labor, Manufacturing Overhead and Selling & Administrative costs are paid in the quarter incurred. Bloomfield has an open line of credit with the bank, and can borrow in any quarter they don’t meet the minimum cash balance of $55,000. Borrowed funds accrue 3% interest for the quarter and are paid in the first quarter cash is available along with interest. Bloomfield collects interest of $3,000 from a note receivable each quarter. Bloomfield plans to purchase a $50,000 equipment in cash in Quarter 3. Bloomfield pays an estimated income tax at $5,000 each quarter. Please prepare the Schedule of Expected Collections from Clients and the Schedule of Expected Payments for Material

Budget Excel Project – Bloomfield Co. Bloomfield is an ice cream company, who makes homestyle ice creams using fresh and all-natural ingredients and sells to retail stores. In preparing the budget for the coming year, 2024, they forecasted the following expected sales and gathered the costs data from managers in various areas of the company. Sales Forecasted to sell 25,000 units in the 1st Quarter of 2024, and an additional 2,000 units each quarter. The 2,000 additional units per quarter are expected to continue through the end of 2nd quarter of 2025. The 2023 4th quarter sales were 24,000 units. Selling price for one unit of ice cream is $15. Bloomfield maintains 15% of the next quarter’s unit sales in ending inventory. All sales to retail stores are on account, with collections of 75% made in the quarter of sales and 25% collected in the quarter after sale. Accounts receivable on December 31, 2023 is $72,000. Direct Materials Direct materials cost $3 per pound. 1.5 pounds of direct materials are required to produce each unit. Bloomfield maintains 6% of the materials needed for the next quarter in its ending material inventory. Raw Materials on December 31, 2023 were 2,250 pounds. Payment for materials are made 50% in the quarter of purchase, and 50% in the quarter after purchase. Accounts Payable balance on December 31, 2023 was $56,250. Direct Labor Labor required is 15 minutes per unit at a rate of $11 per hour. Manufacturing Overhead Variable overhead costs are based on labor hours, listed below: Indirect materials 50¢ per labor hour Indirect labor 75¢ per labor hour Utilities 65¢ per labor hour Maintenance 35¢ per labor hour Foxed overhead costs are budgeted for the month, listed below: Rent $3,000 per month Salaries $11,000 per month Depreciation $4,800 per month Property taxes $ 675 per month Insurance $ 500 per month Selling and Administrative Variable selling and administrative cost are budgeted per sales unit: Shipping expense $.75 per sales unit Packaging expense $.50 per sales unit Fixed selling and administrative costs are budgeted for the month, listed below: Advertising $2,000 a month Insurance $700 a month Office Salaries $4,000 a month Depreciation $800 a month Other Information The Cash balance at the end of 2023, is $80,750, and Bloomfield would like to maintain a cash balance of at least $200,000 at the end of each quarter. All Direct Labor, Manufacturing Overhead and Selling & Administrative costs are paid in the quarter incurred. Bloomfield has an open line of credit with the bank, and can borrow in any quarter they don’t meet the minimum cash balance of $55,000. Borrowed funds accrue 3% interest for the quarter and are paid in the first quarter cash is available along with interest. Bloomfield collects interest of $3,000 from a note receivable each quarter. Bloomfield plans to purchase a $50,000 equipment in cash in Quarter 3. Bloomfield pays an estimated income tax at $5,000 each quarter. Please prepare the Schedule of Expected Collections from Clients and the Schedule of Expected Payments for Material

Principles of Accounting Volume 1

19th Edition

ISBN:9781947172685

Author:OpenStax

Publisher:OpenStax

Chapter7: Accounting Information Systems

Section: Chapter Questions

Problem 5MC: A document that asks you to return an identifying part of it with your payment is a(n) ________. A....

See similar textbooks

Related questions

Concept explainers

Question

Budget Excel Project – Bloomfield Co.

Bloomfield is an ice cream company, who makes homestyle ice creams using fresh and all-natural ingredients and sells to retail stores. In preparing the budget for the coming year, 2024, they forecasted the following expected sales and gathered the costs data from managers in various areas of the company.

Sales

Forecasted to sell 25,000 units in the 1^st Quarter of 2024, and an additional 2,000 units each quarter.
The 2,000 additional units per quarter are expected to continue through the end of 2nd quarter of 2025.
The 2023 4^th quarter sales were 24,000 units.
Selling price for one unit of ice cream is $15.
Bloomfield maintains 15% of the next quarter’s unit sales in ending inventory.
All sales to retail stores are on account, with collections of 75% made in the quarter of sales and 25% collected in the quarter after sale.
Accounts receivable on December 31, 2023 is $72,000.

Direct Materials

Direct materials cost $3 per pound. 1.5 pounds of direct materials are required to produce each unit.
Bloomfield maintains 6% of the materials needed for the next quarter in its ending material inventory.
Raw Materials on December 31, 2023 were 2,250 pounds.
Payment for materials are made 50% in the quarter of purchase, and 50% in the quarter after purchase.
Accounts Payable balance on December 31, 2023 was $56,250.

Direct Labor

Labor required is 15 minutes per unit at a rate of $11 per hour.

Manufacturing Overhead

Variable overhead costs are based on labor hours, listed below:

Indirect materials 50¢ per labor hour
Indirect labor 75¢ per labor hour
Utilities 65¢ per labor hour
Maintenance 35¢ per labor hour

Foxed overhead costs are budgeted for the month, listed below:

Rent $3,000 per month
Salaries $11,000 per month
Depreciation $4,800 per month
Property taxes $ 675 per month
Insurance $ 500 per month

Selling and Administrative

Variable selling and administrative cost are budgeted per sales unit:

Shipping expense $.75 per sales unit
Packaging expense $.50 per sales unit

Fixed selling and administrative costs are budgeted for the month, listed below:

Advertising $2,000 a month
Insurance $700 a month
Office Salaries $4,000 a month
Depreciation $800 a month

Other Information

The Cash balance at the end of 2023, is $80,750, and Bloomfield would like to maintain a cash balance of at least $200,000 at the end of each quarter.
All Direct Labor, Manufacturing Overhead and Selling & Administrative costs are paid in the quarter incurred.
Bloomfield has an open line of credit with the bank, and can borrow in any quarter they don’t meet the minimum cash balance of $55,000.
Borrowed funds accrue 3% interest for the quarter and are paid in the first quarter cash is available along with interest.
Bloomfield collects interest of $3,000 from a note receivable each quarter.
Bloomfield plans to purchase a $50,000 equipment in cash in Quarter 3.
Bloomfield pays an estimated income tax at $5,000 each quarter.

Please prepare the Schedule of Expected Collections from Clients and the Schedule of Expected Payments for Material

(2)
D
Qtr1
E
Schedule of Expected Collections from Clients
For the Year Ending
Qtr2
Qtr1
F
Schedule of Expected Payments for Material
For the Year Ending
Qtr2
cs Scanned with CamScanner
Qtr3
Qtr3
G
Qtr4
Otr4
H
Total
Total
J

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.