Budgeting Problem (Case Study 3) Global Requirements for Budgeting Each budget needs to be prepared on a separate excel spreadsheet or spreadsheet page (ie You can include all of this information within one spreadsheet just make sure that you label your budgets as follows (at least at the top of your budget spreadsheets) Sales, Production, Purchases, Direct Labor, Overhead, Cash Receipts and Cash Disbursements As sample title of a budget would be Sample CompanySales Budget for the First Quarter 2016 If you have supporting documentation that you would like to include to support your budgetary calculations, please refer to the support calculations by either including the information at the bottom of your budget spreadsheet or in a separate spreadsheet properly labeled Requirements for Budgeting Problem Prepare a sales budget for the first quarter of 2016 Prepare a production budget for the first quarter of 2016 Prepare a purchases budget for the first quarter of 2016 Prepare a direct labor budget for the first quarter of 2016 Prepare an overhead budget for the first quarter of 2016 Prepare a cash receipts and disbursements budgets for the first quarter of 2016 Budgeting Problem Tina s Fine Juices is a bottler of orange juice located in the Northeast The company produces bottled orange juice from fruit concentrate purchased from suppliers in Florida, Arizona, and California The only ingredients in the juice are water and concentrate The juice is blended, pasteurized, and bottled for sales in 12 ounce plastic bottles The process is heavily automated and is centered on five machines that control the mixing and bottling of the juice The amounts of labor requires is very small per bottle of juice The average workers can process 10 bottles of juice per minute, or 600 bottles per hour The juice is sold by a number of grocery stores under their store brand name and in smaller restaurants, delis, and bagel shops under the name of Tina s Fine Juices Tina s has been in business for several years and uses a sophisticated sales forecasting models based on prior sales, expected changes in demand, and economic factors affecting the industry Sales of juice are highly seasonal, peaking in the first quarter of the calendar year Forecasted sales for the last two months of 2015 and all of 2016 are as follows 2015 Bottles November 375,000 December 370,000 2016 Bottles January 350,000 February 425,000 March 400,000 April 395,000 May 375,000 June 350,000 July 375,000 August 385,000 September 395,000 October 405,000 November 400,000 December 365,000 Other information that relates to Tina s Fine Juices Juice is sold for $1 05 per 12 ounce bottle, in cartons that each hold 50 bottles Tina s Fine Juices tries to maintain at least 10 percentage of the next month s estimates sales in inventory at the end of each month The company needs to prepare two purchases budgets one for the concentrate used in its orange juice and one for the bottles that are purchases from an outside supplier Tina has determined that its takes 1 gallon of orange concentrate for every 32 bottles of finished product Each gallon of concentrates costs $4 80 Tina s also requires 20 percent of next month s direct material needs to be on hand at the end of the budget period Bottles can be purchase from an outside supplies for $0 10 each Factory workers are paid an average of $15 00 per hour, including fringe benefits and payroll taxes If the production schedule does not allow for full utilization of the workers and machine, one or more workers are temporarily moved to another department Most of the production process is automated, the juice is mixed by machine, and machines do the bottling and packaging Overhead costs are incurred almost entirely in the mixing and of bottling process Consequently, Tina s separates variable overhead to products Variable overhead costs will be in direct proportion of the number of bottles of juice produced, but fixed overhead costs will remain constant, regardless of production For budgeting purposes, Tina s separates variable overhead from fixed overhead and calculates a predetermined overhead rate for variable manufacturing overhead costs Variable overhead is estimated to be $438,000 for the year, and the production machines will run approximately 8,000 hours at the projected production volume for the year (4,775,000 bottles ) Therefore, Tina s determined overhead rate for variable overhead is $54 75 per machine hour ($438,000 8,000 machine hours) Tina s has also estimated fixed overhead to be $1,480,000 per year ($123,333 per month,) of which $1,240,0000 per year ($103,000 per month) is depreciation on existing property, plant, and equipment All of the company s sales are on account Based on the company s experience in previous years, the company estimates that 50 percent of the sales each month will be paid for in the month of sale The company also estimates that 35 percent of the month s sales will be collected in the month following sale and that 15 percent of each month s sales will be collected in the second month following sale Tina s has a policy of paying 50 percent of the direct material purchase in the month of purchase and the balance in the month after purchase Overhead costs are also paid 50 percent in the month they are incurred and 50 percent in the following month Sellingandadministrativeexpensesare$100,000permonthandarepaidincashastheyareincurred Budgets Steven Bridgeman ACC 409 09 13 16 1 Sales Budget For Jan Feb Mar January 350,000 Unit $ TPS 2 Feb 425,000 Mar 400,000 1 05 1 05 367,500 446,250 Total Projected Sales 1,175,000 1 05 1 05 420,000 1,233,750 Production Budget Dec Proj Sales 370,000 Jan Feb 350,000 425,000 Proj ending 35,000 Proj Need 42,500 405,000 392,500 Beg inv 37000 Production 368000 Mar 400,000 40,000 39,500 465,000 439,500 35000 357,500 40,000 425,000 39,500 400,000 3 Materials Purchase Budget (Concentrate) Dec Jan Feb Mar 1st Quarter Month December January February 368,000 357,500 422,500 32(Bottles Per Gallon) 11,500 11,172 13,203 12484 Proj Ending Inventory 2,234 2641 2456 Proj Prod(bottles) projected needs 13,734 Proj, Beginning Inventory 2300 Concentrate needed 11,434 X Price per gallon(4 80) 54,885 2497 March 399,500 13,813 15700 14941 2234 2641 2497 11578 13059 12444 55,575 62,685 59730 Material Purchases Budget ( Bottles) Month December January February March Proj Production 368,000 357500 422,500 399,500 Proj Ending Inv 71,500 84,500 79,900 78,600 Proj Needed 439,500 442,000 502,400 478,100 73,600 71,500 84,500 79,900 B Inv Bottles Needed 365,900 X Price per bottle 370,500 10 417,900 10 36590 37050 4 Direct labor Budget 368000 357500 Bottles per hour 600 600 Machine hours 613 Direct labor Per hour 15 9,195 398,200 10 10 41,790 39,820 422,500 399,500 600 600 596 704 666 15 15 15 8940 10,560 9,990 5 Overhead Budget December January February March Proj Prod (Bottles 368,000 422500 Bottles Per Hour 600 357500 399,500 600 600 600 704 666 Budgeted Machine Hrs 613 596 X variable overhead 54 75 54 75 54 75 54 75 Proj Variable Overhead 33562 32631 38544 36464 Proj Fixed Overhead 123,333 123,333 123,333 123,333 Total Proj O H 156,895 155,964 161,877 159,797 103,333 103,333 103,333 103,333 53,562 52,631 58,544 56,464 Depr Budget Cash Outflow Cash Receipt Budget Cash Receipts Dec, Jan, Feb and Mar(First Quarter) Nov Bottles Dec Jan Feb March April 375,000 370,000 350,000 425,000 400,000 318,750 314,500 297,500 361,250 340,000 85 Bal Bottles 56250 Net Inflow 370750 353000 413750 Cartons of cartons Cost Per carton Sales 55500 50 52500 50 50 7415 7060 1 05 7786 00 395,000 335,750 63750 403750 60000 395750 50 50 8275 8075 7915 1 05 1 05 1 05 1 05 7413 8689 00 8479 00 8311 00 Cash Disbursements Budget DM Purchase concentrate 54,885 55,575 62,685 59730 DM Purchases Bottle 36590 37050 41790 39820 Total Disbursements For DM 91475 92625 104475 99550 Manufacturing O H Cost 53580 52622 58553 56454 Total Disbursements for S A 145055 145247 163028 156004 Total Cash Disbursements (137269) (137834) (154339) (147526) Add Depreciation 103,333 103,333 103,333 Net Cash Flow (33926) (34501) (51006) 103,333 (44193 00 )

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

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 28, 2024

Budgeting Problem (Case Study #3): Global Requirements for Budgeting: Each budget needs to be prepared on a separate excel spreadsheet or spreadsheet page. (ie. You

Budgeting Problem (Case Study #3):

Global Requirements for Budgeting:

Each budget needs to be prepared on a separate excel spreadsheet or spreadsheet page. (ie. You can include all of this information within one spreadsheet ? just make sure that you label your budgets as follows (at least at the top of your budget spreadsheets): Sales, Production, Purchases, Direct Labor, Overhead, Cash Receipts and Cash Disbursements. As sample title of a budget would be: Sample CompanySales Budget for the First Quarter 2016 If you have supporting documentation that you would like to include to support your budgetary calculations, please refer to the support calculations ? by either including the information at the bottom of your budget spreadsheet or in a separate spreadsheet properly labeled. Requirements for Budgeting Problem:
Prepare a sales budget for the first quarter of 2016.
Prepare a production budget for the first quarter of 2016.
Prepare a purchases budget for the first quarter of 2016.
Prepare a direct labor budget for the first quarter of 2016.
Prepare an overhead budget for the first quarter of 2016.
Prepare a cash receipts and disbursements budgets for the first quarter of 2016. Budgeting Problem:Tina?s Fine Juices is a bottler of orange juice located in the Northeast. The company produces bottled orange juice from fruit concentrate purchased from suppliers in Florida, Arizona, and California. The only ingredients in the juice are water and concentrate. The juice is blended, pasteurized, and bottled for sales in 12-ounce plastic bottles. The process is heavily automated and is centered on five machines that control the mixing and bottling of the juice. The amounts of labor requires is very small per bottle of juice. The average workers can process 10 bottles of juice per minute, or 600 bottles per hour. The juice is sold by a number of grocery stores under their store brand name and in smaller restaurants, delis, and bagel shops under the name of Tina?s Fine Juices. Tina?s has been in business for several years and uses a sophisticated sales forecasting models based on prior sales, expected changes in demand, and economic factors affecting the industry. Sales of juice are highly seasonal, peaking in the first quarter of the calendar year. Forecasted sales for the last two months of 2015 and all of 2016 are as follows:

2015	Bottles
November	375,000
December	370,000

2016	Bottles
January	350,000
February	425,000
March	400,000
April	395,000
May	375,000
June	350,000
July	375,000
August	385,000
September	395,000
October	405,000
November	400,000
December	365,000

Other information that relates to Tina?s Fine Juices:

Juice is sold for $1.05 per 12-ounce bottle, in cartons that each hold 50 bottles.
Tina?s Fine Juices tries to maintain at least 10 percentage of the next month?s estimates sales in inventory at the end of each month.
The company needs to prepare two purchases budgets ? one for the concentrate used in its orange juice and one for the bottles that are purchases from an outside supplier. Tina? has determined that its takes 1 gallon of orange concentrate for every 32 bottles of finished product. Each gallon of concentrates costs $4.80. Tina?s also requires 20 percent of next month?s direct material needs to be on hand at the end of the budget period. Bottles can be purchase from an outside supplies for $0.10 each.
Factory workers are paid an average of $15.00 per hour, including fringe benefits and payroll taxes. If the production schedule does not allow for full utilization of the workers and machine, one or more workers are temporarily moved to another department.
Most of the production process is automated, the juice is mixed by machine, and machines do the bottling and packaging. Overhead costs are incurred almost entirely in the mixing and of bottling process. Consequently, Tina?s separates variable overhead to products.
Variable overhead costs will be in direct proportion of the number of bottles of juice produced, but fixed overhead costs will remain constant, regardless of production. For budgeting purposes, Tina?s separates variable overhead from fixed overhead and calculates a predetermined overhead rate for variable manufacturing overhead costs.
Variable overhead is estimated to be $438,000 for the year, and the production machines will run approximately 8,000 hours at the projected production volume for the year (4,775,000 bottles.) Therefore, Tina?s determined overhead rate for variable overhead is $54.75 per machine hour ($438,000 /8,000 machine hours). Tina?s has also estimated fixed overhead to be $1,480,000 per year ($123,333 per month,) of which $1,240,0000 per year ($103,000 per month) is depreciation on existing property, plant, and equipment.
All of the company?s sales are on account. Based on the company?s experience in previous years, the company estimates that 50 percent of the sales each month will be paid for in the month of sale. The company also estimates that 35 percent of the month?s sales will be collected in the month following sale and that 15 percent of each month?s sales will be collected in the second month following sale.
Tina?s has a policy of paying 50 percent of the direct material purchase in the month of purchase and the balance in the month after purchase. Overhead costs are also paid 50 percent in the month they are incurred and 50 percent in the following month.
Sellingandadministrativeexpensesare$100,000permonthandarepaidincashastheyareincurred.

Budgets Steven Bridgeman ACC 409 09/13/16 1. Sales Budget For Jan Feb Mar January: 350,000 Unit $ TPS 2. Feb: 425,000 Mar: 400,000 1.05 1.05 367,500 446,250 Total Projected Sales: 1,175,000 1.05 1.05 420,000 1,233,750 Production Budget Dec Proj. Sales: 370,000 Jan Feb 350,000 425,000 + Proj ending 35,000 = Proj. Need - 42,500 405,000 392,500 Beg inv. 37000 Production: 368000 Mar 400,000 40,000 39,500 465,000 439,500 35000 357,500 40,000 425,000 39,500 400,000 3. Materials Purchase Budget (Concentrate): Dec Jan Feb Mar 1st Quarter: Month December January February 368,000 357,500 422,500 32(Bottles Per Gallon) 11,500 11,172 13,203 12484 + Proj. Ending Inventory 2,234 2641 2456 Proj Prod(bottles) - = projected needs 13,734 Proj, Beginning Inventory -2300 = Concentrate needed 11,434 X Price per gallon(4.80) 54,885 2497 March 399,500 13,813 15700 14941 -2234 -2641 2497 11578 13059 12444 55,575 62,685 59730 Material Purchases Budget ( Bottles): Month December January February March Proj. Production 368,000 357500 422,500 399,500 + Proj. Ending Inv. 71,500 84,500 79,900 78,600 Proj. Needed 439,500 442,000 502,400 478,100 73,600 71,500 84,500 79,900 B. Inv. Bottles Needed 365,900 X Price per bottle 370,500 .10 417,900 .10 36590 37050 4. Direct labor Budget 368000 357500 Bottles per hour 600 600 Machine hours 613 Direct labor Per hour 15 9,195 398,200 .10 .10 41,790 39,820 422,500 399,500 600 600 596 704 666 15 15 15 8940 10,560 9,990 5. Overhead Budget December January February March Proj. Prod (Bottles 368,000 422500 /Bottles Per Hour 600 357500 399,500 600 600 600 704 666 Budgeted Machine Hrs. 613 596 X variable overhead 54.75 54.75 54.75 54.75 Proj. Variable Overhead 33562 32631 38544 36464 + Proj. Fixed Overhead 123,333 123,333 123,333 123,333 Total Proj. O/H 156,895 155,964 161,877 159,797 103,333 103,333 103,333 103,333 53,562 52,631 58,544 56,464 - Depr. Budget: Cash Outflow Cash Receipt Budget Cash Receipts Dec, Jan, Feb and Mar(First Quarter) Nov Bottles Dec Jan Feb March April 375,000 370,000 350,000 425,000 400,000 318,750 314,500 297,500 361,250 340,000 85% Bal. Bottles 56250 Net Inflow 370750 353000 413750 Cartons # of cartons Cost Per carton Sales 55500 50 52500 50 50 7415 7060 1.05 7786.00 395,000 335,750 63750 403750 60000 395750 50 50 8275 8075 7915 1.05 1.05 1.05 1.05 7413 8689.00 8479.00 8311.00 Cash Disbursements Budget DM Purchase-concentrate 54,885 55,575 62,685 59730 DM Purchases- Bottle 36590 37050 41790 39820 Total Disbursements For DM 91475 92625 104475 99550 Manufacturing O/H Cost 53580 52622 58553 56454 Total Disbursements for S&A 145055 145247 163028 156004 Total Cash Disbursements (137269) (137834) (154339) (147526) Add: Depreciation 103,333 103,333 103,333 Net Cash Flow (33926) (34501) (51006) 103,333 (44193.00 )