You now have the information you need to create a budget that will allow you to show the banker your plans for the coming year. This budget also will help you to understand your sales and the collection of those sales. You will be able to determine how much money you need to purchase the ingredients for your cookies and to pay your overhead and operating expenses. You realize, "It all begins with sales. If I can estimate how many dozens of cookies I can sell, then I can calculate how many ingredients to buy and how much my overhead and operating expenses will be. Well, I had better get that sales number as accurate as possible."

You reach in your desk and pull out a disk that the banker gave you. On that disk is an example format of a budget that the banker has seen many businesses use over the years. You pull up the spreadsheet and begin working.

Requirements for Part 2:

Exhibit 2 presents information regarding your sales projections, expected collection patterns, purchasing and payment patterns for the first four months of the year.

1. Use the spreadsheet template provided by your instructor to prepare the following operating budgets for the first quarter: (a) Sales budget/Cash collections Budget (b) Direct materials purchases budget/Cash disbursements budget (c) Manufacturing overhead budget (d) Operating expenses budget

Note: When preparing the budgets, you should make maximum use of cell referencing and formulas in Excel spreadsheets. You should not have to enter the same data more than one time (for example, you should enter monthly sales projections on the sales budget and then use cell referencing from the sales budget to incorporate this information in all of the other budgets). You should use formulas in Excel to perform all of your mathematical calculations. Part of your grade for this assignment will be based on your ability to use these Excel tools. Do not complete the income statements, cash budget, and balance sheet worksheets. These budgets comprise Part 3 of the project that will be completed later.

EXHIBIT 1 Sales Price, Cost of Ingredients, Manufacturing Overhead, and Operating Expenses 125% Sales Price: Mark-up on total variable cost Cost of Raw Materials: Direct Materials Flour - per cup Eggs -per egg Butter, Shorting, Oil per cup* Chips/Nuts, etc. per cup White / Brown Sugar- per cup * 1 stick of butter = k cup Unit Cost $0.15 $0.10 $0.05 $0.75 $0.15 Direct Labor Costs: Information regarding direct labor costs is not maintained because your facility is highly automated. Direct labor is included as a part of manufacturing overhead. Manufacturing Overhead: Variable Cost Fixed Cost (per dozen) (per month) $0.65 $0.85 Utilities Other indirect materials and labor * Maintenance Depreciation Supervision Totals $520 $2,010 $2,550 $5,080.00 $1.50 Operating Expenses: Variable Cost (per dozen) $0.55 $1.05 Fixed Cost (per month) Sales Commission Shipping Costs Salaries Depreciation Other Totals $50,100 $215 $1,795 $52.110.00 $1.60 Includes any ingredients not listed above. Please list what you think belongs under this cost when reporting costs. EXHIBIT 2 Sales Projections, Collections, Purchases and Payments Monthly sales projections (in dozens of cookies): Sample Number* January The first four digits of your phone number 3709 February The year you were born 1948 March The first four digits of your social security number 4088 April The last four digits of your social security number 5992 *Enter the appropriate number in this column. You have stopped production of cookies at year end to facilitate the expansion of the business. Therefore, you expect to have no uncollected accounts receivables, unpaid accounts payable, Or raw material inventories at January 1, the beginning of your budget period. Collections on Sales: Cash sales are collected in the month of sale. Credit sales are collected in the next month. Percentage 40% 60% Production: The company produces the cookies daily. No work-in-process or finished goods inventories are maintained. Raw materials: The company plans to maintain an ending inventory of raw materials at the end of each month. The company desires to maintain a percentage of raw materials production needs for the next month. Desired ending raw materials inventory percentage: 10% Payment for Raw Materials: Percentage Paid in the month of purchase Paid in the month following purchase 25% 75% See Exhibit 1 for information regarding raw material costs, manufacturing overhead, and operating expense. EXHIBIT 1 Sales Price, Cost of Ingredients, Manufacturing Overhead, and Operating Expenses 125% Sales Price: Mark-up on total variable cost Cost of Raw Materials: Direct Materials Flour - per cup Eggs -per egg Butter, Shorting, Oil per cup* Chips/Nuts, etc. per cup White / Brown Sugar- per cup * 1 stick of butter = k cup Unit Cost $0.15 $0.10 $0.05 $0.75 $0.15 Direct Labor Costs: Information regarding direct labor costs is not maintained because your facility is highly automated. Direct labor is included as a part of manufacturing overhead. Manufacturing Overhead: Variable Cost Fixed Cost (per dozen) (per month) $0.65 $0.85 Utilities Other indirect materials and labor * Maintenance Depreciation Supervision Totals $520 $2,010 $2,550 $5,080.00 $1.50 Operating Expenses: Variable Cost (per dozen) $0.55 $1.05 Fixed Cost (per month) Sales Commission Shipping Costs Salaries Depreciation Other Totals $50,100 $215 $1,795 $52.110.00 $1.60 Includes any ingredients not listed above. Please list what you think belongs under this cost when reporting costs. EXHIBIT 2 Sales Projections, Collections, Purchases and Payments Monthly sales projections (in dozens of cookies): Sample Number* January The first four digits of your phone number 3709 February The year you were born 1948 March The first four digits of your social security number 4088 April The last four digits of your social security number 5992 *Enter the appropriate number in this column. You have stopped production of cookies at year end to facilitate the expansion of the business. Therefore, you expect to have no uncollected accounts receivables, unpaid accounts payable, Or raw material inventories at January 1, the beginning of your budget period. Collections on Sales: Cash sales are collected in the month of sale. Credit sales are collected in the next month. Percentage 40% 60% Production: The company produces the cookies daily. No work-in-process or finished goods inventories are maintained. Raw materials: The company plans to maintain an ending inventory of raw materials at the end of each month. The company desires to maintain a percentage of raw materials production needs for the next month. Desired ending raw materials inventory percentage: 10% Payment for Raw Materials: Percentage Paid in the month of purchase Paid in the month following purchase 25% 75% See Exhibit 1 for information regarding raw material costs, manufacturing overhead, and operating expense

Question

You now have the information you need to create a budget that will allow you to show the banker your plans for the coming year. This budget also will help you to understand your sales and the collection of those sales. You will be able to determine how much money you need to purchase the ingredients for your cookies and to pay your overhead and operating expenses. You realize, "It all begins with sales. If I can estimate how many dozens of cookies I can sell, then I can calculate how many ingredients to buy and how much my overhead and operating expenses will be. Well, I had better get that sales number as accurate as possible."

You reach in your desk and pull out a disk that the banker gave you. On that disk is an example format of a budget that the banker has seen many businesses use over the years. You pull up the spreadsheet and begin working.

Requirements for Part 2:

Exhibit 2 presents information regarding your sales projections, expected collection patterns, purchasing and payment patterns for the first four months of the year.

1. Use the spreadsheet template provided by your instructor to prepare the following operating budgets for the first quarter: (a) Sales budget/Cash collections Budget (b) Direct materials purchases budget/Cash disbursements budget (c) Manufacturing overhead budget (d) Operating expenses budget

Note: When preparing the budgets, you should make maximum use of cell referencing and formulas in Excel spreadsheets. You should not have to enter the same data more than one time (for example, you should enter monthly sales projections on the sales budget and then use cell referencing from the sales budget to incorporate this information in all of the other budgets). You should use formulas in Excel to perform all of your mathematical calculations. Part of your grade for this assignment will be based on your ability to use these Excel tools. Do not complete the income statements, cash budget, and balance sheet worksheets. These budgets comprise Part 3 of the project that will be completed later.

image text in transcribed

EXHIBIT 1 Sales Price, Cost of Ingredients, Manufacturing Overhead, and Operating Expenses 125% Sales Price: Mark-up on total variable cost Cost of Raw Materials: Direct Materials Flour - per cup Eggs -per egg Butter, Shorting, Oil per cup* Chips/Nuts, etc. per cup White / Brown Sugar- per cup * 1 stick of butter = k cup Unit Cost $0.15 $0.10 $0.05 $0.75 $0.15 Direct Labor Costs: Information regarding direct labor costs is not maintained because your facility is highly automated. Direct labor is included as a part of manufacturing overhead. Manufacturing Overhead: Variable Cost Fixed Cost (per dozen) (per month) $0.65 $0.85 Utilities Other indirect materials and labor * Maintenance Depreciation Supervision Totals $520 $2,010 $2,550 $5,080.00 $1.50 Operating Expenses: Variable Cost (per dozen) $0.55 $1.05 Fixed Cost (per month) Sales Commission Shipping Costs Salaries Depreciation Other Totals $50,100 $215 $1,795 $52.110.00 $1.60 Includes any ingredients not listed above. Please list what you think belongs under this cost when reporting costs. EXHIBIT 2 Sales Projections, Collections, Purchases and Payments Monthly sales projections (in dozens of cookies): Sample Number* January The first four digits of your phone number 3709 February The year you were born 1948 March The first four digits of your social security number 4088 April The last four digits of your social security number 5992 *Enter the appropriate number in this column. You have stopped production of cookies at year end to facilitate the expansion of the business. Therefore, you expect to have no uncollected accounts receivables, unpaid accounts payable, Or raw material inventories at January 1, the beginning of your budget period. Collections on Sales: Cash sales are collected in the month of sale. Credit sales are collected in the next month. Percentage 40% 60% Production: The company produces the cookies daily. No work-in-process or finished goods inventories are maintained. Raw materials: The company plans to maintain an ending inventory of raw materials at the end of each month. The company desires to maintain a percentage of raw materials production needs for the next month. Desired ending raw materials inventory percentage: 10% Payment for Raw Materials: Percentage Paid in the month of purchase Paid in the month following purchase 25% 75% See Exhibit 1 for information regarding raw material costs, manufacturing overhead, and operating expense. EXHIBIT 1 Sales Price, Cost of Ingredients, Manufacturing Overhead, and Operating Expenses 125% Sales Price: Mark-up on total variable cost Cost of Raw Materials: Direct Materials Flour - per cup Eggs -per egg Butter, Shorting, Oil per cup* Chips/Nuts, etc. per cup White / Brown Sugar- per cup * 1 stick of butter = k cup Unit Cost $0.15 $0.10 $0.05 $0.75 $0.15 Direct Labor Costs: Information regarding direct labor costs is not maintained because your facility is highly automated. Direct labor is included as a part of manufacturing overhead. Manufacturing Overhead: Variable Cost Fixed Cost (per dozen) (per month) $0.65 $0.85 Utilities Other indirect materials and labor * Maintenance Depreciation Supervision Totals $520 $2,010 $2,550 $5,080.00 $1.50 Operating Expenses: Variable Cost (per dozen) $0.55 $1.05 Fixed Cost (per month) Sales Commission Shipping Costs Salaries Depreciation Other Totals $50,100 $215 $1,795 $52.110.00 $1.60 Includes any ingredients not listed above. Please list what you think belongs under this cost when reporting costs. EXHIBIT 2 Sales Projections, Collections, Purchases and Payments Monthly sales projections (in dozens of cookies): Sample Number* January The first four digits of your phone number 3709 February The year you were born 1948 March The first four digits of your social security number 4088 April The last four digits of your social security number 5992 *Enter the appropriate number in this column. You have stopped production of cookies at year end to facilitate the expansion of the business. Therefore, you expect to have no uncollected accounts receivables, unpaid accounts payable, Or raw material inventories at January 1, the beginning of your budget period. Collections on Sales: Cash sales are collected in the month of sale. Credit sales are collected in the next month. Percentage 40% 60% Production: The company produces the cookies daily. No work-in-process or finished goods inventories are maintained. Raw materials: The company plans to maintain an ending inventory of raw materials at the end of each month. The company desires to maintain a percentage of raw materials production needs for the next month. Desired ending raw materials inventory percentage: 10% Payment for Raw Materials: Percentage Paid in the month of purchase Paid in the month following purchase 25% 75% See Exhibit 1 for information regarding raw material costs, manufacturing overhead, and operating expense

Accepted Answer

The Answer is in the image, click to view ...

Question

You now have the information you need to create a budget that will allow you to show the banker your plans for the coming year.

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Cost Accounting A Managerial Emphasis

Students also viewed these Accounting questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question