Question

In: Accounting

You are the Controller for the Window Blinds Company (WBC), a firm that makes wooden window...

You are the Controller for the Window Blinds Company (WBC), a firm that makes wooden window blinds. Your firm just reached an agreement with the local Home Depot to begin selling your products on a test basis in their store for the months of April, May and June 2020 (second quarter). Management expects sales to increase as a result of this test and you realize that a sufficient quantity of product must be produced to avoid stock outs.

You have requested your Accounting Manager to prepare a Master Budget for the second quarter of 2020 (April, May and June) to assist in managing operations, determining how much material needs to be ordered, and how much labor will be required.

In addition, you also requested the Accounting Manager to prepare a pro-forma income statement for the 2nd Quarter (April, May and June) that will to be used in evaluating the results of the test program.

You should prepare your budgets and income statement using Excel. The attached format for the Excel spreadsheet must be used. The completed worksheet must contain formulas and cell references for all calculations.

Information

Sales and Cash Collection Information

WBC sales staff have prepared a schedule of the budgeted sales for the months of January through December 2019. Expected unit sales for the months of 2019 are listed below:

January

1000

February

March                                                                                      

3000

3000

April

2250

May

10000

June

7750

July

1000

August

September

October

November

December

TOTAL

1500

1000

2000

1000

2500

36,000

WBC sells the blinds to Home Depot and its other customers at a price of $40 each. WBC sells all the blinds on account and expects to collect 60% of the cash in the month of the sale and 40% in the month after the sale.

Production and Material Budget Information

Finished Goods

Window Blinds Company (WBC) manufactures window blinds from wood. Each blind uses 3 feet of “high-quality” teak hardwood, which costs WBC $3 per foot.   WBC likes to end the month with a finished goods inventory equal to 30% of the planned sales for the next month.

Raw Materials

In addition to the finished goods inventory, WBC also has an inventory of Teak wood in a climate controlled warehouse. WBC like to have in its warehouse a beginning inventory equal to 40% of the teak wood it will need for the month’s production.

WBC pays for the material it purchases in the month of purchase.

Labor Budget Information

WBC pays its workers $11 per hour and each blind takes 1 hour of labor to complete. All wages are paid in the month incurred.

Manufacturing Overhead Information

Total manufacturing overhead is estimated to be $94,500. Manufacturing Overhead is applied equally to each product produced.

Overhead is paid in the month incurred.

Selling and Administrative Costs

Selling and administrative expenses are a combination of fixed and variable costs. Fixed Selling and Administrative costs are estimated at $8,000 per month and variable selling and administrative costs are $5.00 per unit sold.

Selling and administrative costs are paid in the month incurred.

Cash Management

WBC starts the month of April with $2,000 in the bank. Management would like to maintain a minimum of $10,000 in the bank. WBC just opened a line of credit with the bank that they can draw upon on the last day of the month. Interest is paid at the end of the month and the line of credit can be paid back in any increment. Interest is charged at 1% per month on the outstanding balance borrowed.

Required:

  1. Prepare the following for WBC for the second quarter of the year (April, May, June).   Include each month in your projections as well as the 2nd Quarter totals for each budget.
  1. Sales Budget
  2. Production Budget
  3. Raw Materials Purchases Budget
  4. Direct Labor Budget
  5. Manufacturing Overhead Budget
  6. Budgeted Cost of Cost of Goods Sold
  7. Selling and Administrative Expenses Budget
  8. WBC’s Pro Forma Income Statement for the 2nd Quarter.
  9. Cash Budget for the 2nd Quarter
  1. In addition answer the following questions.

1. What will be the number of units and the cost of the Raw Materials and Finished Goods   ending inventories that will appear on the Balance Sheet at the End of the 2nd Quarter?

2. What will be the balance in Accounts Receivable at the end of the 2nd Quarter?

3. Will WBC need to consider borrowing any cash from a bank during the 2nd Quarter?

The beginning cash balance is $2,000 and WBC would like to end each month with $10,000 in the bank.

To accomplish this, how much should be borrowed and how much will WBC owe the bank on June 30?

Solutions

Expert Solution

A.

1.

Sales Budget
April May June Quarter
Budgeted unit sales 2,250 10,000 7,750 20,000
Sales price per unit $ 40 $ 40 $ 40 $ 40
Budgeted sales revenue $ 90,000 $ 400,000 $ 310,000 $ 800,000

2.

Production Budget
April May June Quarter July
Budgeted unit sales 2,250 10,000 7,750 20,000 1,000
Add: Desired ending inventory 3,000 2,325 300 300 450
Total 5,250 12,325 8,050 20,300 1,450
Less: Beginning inventory 675 3,000 2,325 675 300
Required production in units 4,575 9,325 5,725 19,625 1,150

3.

Raw Materials Purchases Budget
April May June Quarter
Qty needed in production ( ft ) 13,725 27,975 17,175 58,875
Add: Desired ending inventory 11,190 6,870 1,380 1,380
Total 24,915 34,845 18,555 60,255
Less: Beginning inventory 5,490 11,190 6,870 5,490
Budgeted purchases in ft. 19,425 23,655 11,685 54,765
Cost per ft. $ 3 $ 3 $ 3 $ 3
Budgeted Cost of RM Purchases $ 58,275 $ 70,965 $ 35,055 $ 164,295

4.

Direct Labor Budget
April May June Quarter
Budgeted production in units 4,575 9,325 5,725 19,625
Labor hour per unit 1 1 1 1
Budgeted labor hours 4,575 9,325 5,725 19,625
Labor hour rate $ 11 $ 11 $ 11 $ 11
Budgeted Cost of Direct Labor $ 50,325 $ 102,575 $ 62,975 $ 215,875

Related Solutions

You are the Controller for ABC Company, which makes and sells products. You are filling in...
You are the Controller for ABC Company, which makes and sells products. You are filling in for the CFO of your company by making a presentation at a pre-planning meeting held at your company. Your topic is decentralization.
I came up with a venture design idea of Cardboard Window Blinds instead of the conventional...
I came up with a venture design idea of Cardboard Window Blinds instead of the conventional wood blinds or paper blinds for my finals. I was taught that the definition of distinctive competency follows the VRIO framework i.e. True distinctive competency means a product has to be: Valuable to your customer, Rare among solution providers, superior to those of other competitors and Costly for others to imitate!. QUESTION: Product: Cardboard Window Blind Imagine you are the one developing this product,...
Horton Manufacturing Inc. produces blinds and other window treatments for residential homes and offices. The owner...
Horton Manufacturing Inc. produces blinds and other window treatments for residential homes and offices. The owner is concerned about the maintenance costs for the production machinery because maintenance costs for the previous fiscal year were higher than he expected. The owner has asked you to assist in estimating future maintenance costs to better predict the firm’s profitability. Together, you have determined that the best cost driver for maintenance costs is machine hours. The data from the previous fiscal year for...
Marigold Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The...
Marigold Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The following information relates to its budgeted operations for the current year. Commercial Residential Revenues $328,000 $514,000 Direct materials costs $45,000 $50,000 Direct labor costs 110,000 290,000 Overhead costs 108,000 263,000 199,000 539,000 Operating income (loss) $65,000 $(25,000) The controller, Peggy Kingman, is concerned about the residential product line. She cannot understand why this line is not more profitable given that the installations of window...
Venus Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The...
Venus Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The following information relates to its budgeted operations for the current year. Commercial Residential Revenues $322,500 $537,500 Direct materials costs $35,000 $50,000 Direct labor costs 120,000 280,000 Overhead costs 87,500 242,500 232,500 562,500 Operating income (loss) $80,000 $(25,000) The controller, Peggy Kingman, is concerned about the residential product line. She cannot understand why this line is not more profitable given that the installations of window...
Venus Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The...
Venus Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The following information relates to its budgeted operations for the current year. Commercial Residential Revenues $299,000 $475,000 Direct materials costs $30,000 $49,000 Direct labor costs 109,900 296,500 Overhead costs 93,900 233,800 149,000 494,500 Operating income (loss) $65,200 $(19,500) The controller, Peggy Kingman, is concerned about the residential product line. She cannot understand why this line is not more profitable given that the installations of window...
Venus Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The...
Venus Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The following information relates to its budgeted operations for the current year. Commercial Residential Revenues $355,050 $425,000 Direct materials costs $30,000 $50,000 Direct labor costs 150,000 250,000 Overhead costs 85,050 265,050 145,000 445,000 Operating income (loss) $90,000 $(20,000) The controller, Peggy Kingman, is concerned about the residential product line. She cannot understand why this line is not more profitable given that the installations of window...
Horton Manufacturing Inc. produces blinds and other window treatments for residential homes and offices. The owner...
Horton Manufacturing Inc. produces blinds and other window treatments for residential homes and offices. The owner is concerned about the maintenance costs for the production machinery because maintenance costs for the previous fiscal year were higher than he expected. The owner has asked you to assist in estimating future maintenance costs to better predict the firm’s profitability. Together, you have determined that the best cost driver for maintenance costs is machine hours. The data from the previous fiscal year for...
Venus Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The...
Venus Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The following information relates to its budgeted operations for the current year. Commercial Residential Revenues $298,800 $475,000 Direct materials costs $30,000 $50,000 Direct labor costs 118,300 312,500 Overhead costs 94,100 242,400 150,000 512,500 Operating income (loss) $56,400 $(37,500) The controller, Peggy Kingman, is concerned about the residential product line. She cannot understand why this line is not more profitable given that the installations of window...
Assume you are the manager of Wenstrom Brewing Company (WBC) and you face the following demand...
Assume you are the manager of Wenstrom Brewing Company (WBC) and you face the following demand for your beer: QWBC = 12 - 3PWBC + 4PPBR. Suppose you sell your beer for $3.00 per bottle and PBR sells for $1.50 per bottle. a. Is PBR a substitute or a complement? Explain. b. Calculate the cross-price elasticity of demand between WBC and PBR at the given prices. c. If you want to increase the level of beer you sell by 15%,...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT