Prepare budgets, homework help

Anonymous
timer Asked: Apr 13th, 2017
account_balance_wallet $100

Question Description

The Company manufactures and sells a Loft Bed. The loft bed has two direct materials – premium pine lumber and paint. The company is preparing budgets for the second quarter ending June 30, 2017.For each requirement below prepare budgets by month for April, May, and June, and a total budget for the quarter.

1.The previous year’s sales (2016) for the corresponding period were:

April 600 beds

May 650 beds

June 900 beds

July 800 beds

August 750 beds

The company expects the above volume of sales to increase by 10% for the period April 2017 – August 2017. The budgeted selling price for 2017 is $600.00 per bed.The company expects 40% of its sales to be cash (COD) sales.The remaining 60% of sales will be made on credit.Prepare a Sales Budget for the company

2.The company desires to have finished goods inventory on hand at the end of each month equal to 20 percent of the following month's budgeted unit sales.On March 31, 2017, the company expects to have 132 beds on hand.Prepare a Production budget.

3.The loft bed requires two direct materials: premium pine lumber and paint.

60 board feet of pine lumber is required for each bed.Various lengths and widths of lumber are cut and ripped from standard board feet purchased in order to construct the beds.Management desires to have materials on hand (i.e., pine) at the end of each month equal to 10 percent of the following month's production needs.The beginning materials inventory, April 2017, is expected to be 5,368 board feet.Premium pine costs $2.00 per board foot.

Paint is purchased in 5 gallon pails and 2 beds can be painted from each pail. The paint is kid friendly and environmentally sustainable – it protects the beds while preserving the environment. Management desires to have paint on hand at the end of each month equal to 15 percent of the following month's production needs.The beginning inventory of paint (i.e., number of 5-gallon pails) in April 2017 is expected to be 51 pails. Paint is expected to cost $68 per pail.

Note, budgeted production in July is required in order to complete the direct materials budget for June. Also, use the @ROUNDUP function to round up to the nearest whole number the board feet of lumber and number of pails pf paint to purchase. Also, because two direct materials are required for production – premium pine lumber and pails of paint - you will need a separate schedule for each direct material. Prepare a Direct Materials budget.

4.Each bed requires 8 hours of direct labor. The company prides itself on producing hand-crafted beds, however, it also continuously seeks ways to minimize unnecessary labor, and has been experimenting with mechanization of certain repetitive production steps – for example, sanding and pilot hole drilling and counter-sinking. Each hour of direct labor costs the company $15.Prepare a Direct Labor budget.

5.The company budgets indirect materials (e.g., sandpaper, screws, and glue) at $10 per bed. It treats indirect labor and utilities as mixed costs. The variable components are $20 per bed for indirect labor and $5 per bed for utilities. The following fixed costs per month are budgeted for indirect labor, $20,000, utilities, $6,000, and other, $14,000.Prepare a Manufacturing Overhead budget.

6.Variable selling and administrative expenses are $150 per bed sold.Fixed selling and administrative expenses are $60,000 per month. These costs are not itemized, i.e., the budget has only two line items – variable operating expenses and fixed operating expenses. Prepare an Operating Expenses budget.

7.Prepare a Budgeted Manufacturing Cost per unit budget.Refer to exhibit 9-11 for guidance. To calculate FMOH/unit calculate total FMOH for the year and divide this by budgeted production for the year. The total production volume for the year is budgeted at 9,600 beds.

8.Prepare a Budgeted Income Statement for the quarter for the company. Assume interest expense of $0, and income tax expense of 30% of income before taxes.

Directions:

Adapt your schedules for the specific details outlined in the requirements above. Prepare your budgets using Excel.Use formulas and cell references so that any change you make in one budget is carried through to all the budgets. There should be no hard keyed numbers in your formulas. For example, if you change the ‘sales volume increase’ from 10% to 12% you should see effects of that change throughout the other budgets. Likewise, if the budgeted selling price changes from $600 to $625 your spreadsheet model should be able to quickly and easily accommodate this change, i.e., change the input cell for budgeted selling price and see the effect on income.

The spreadsheet should update correctly for changes in input variables.

Some general principles to follow in constructing your Excel spreadsheet model:

1.Prepare an input area in which you enter all input variables – e.g., selling price, budgeted volume increase, board feet of lumber per bed, ending inventory percentage, etc. Use a designated area within your budget spreadsheet, as long as the input area is clearly labeled and neatly organized.

2.Each schedule should refer to the input area for each constant data value. Keep all constant values together in one area of the worksheet. An important principle of good spreadsheet design is to keep just one copy of each constant value.

3.Use cell references for constant data values and to calculate formulas within your spreadsheet. There should be no hard-keyed numbers in your formulas. For example, the formula to determine current period sales in units should reference an input cell with last year’s sales volume and a cell with the volume percentage increase.

4.Label and format appropriately – e.g., use $ to format dollar amounts, format cells for decimal places, etc…

Tutor Answer

TeacherSethGreg
School: Duke University

Attached.

Previos year sales (2016)
April
May
June
July
Units

600

650

900

August
800

750

% Increase in units sales for 2017 budget

10%

The company
Sales budget( 2017)

May

Selling price

Month

April

Sales(units)
Sales($)

660
715
990
$396.000 $429.000 $594.000

Cash on Delivery (COD) sales
Credit sales

June

July

40%
60%

August
880

$600

Total 2nd Quarter
825

2365
$1.419.000
$567.600
$851.400

The company
Production budget 2017
Finished gooods inventory on hand percentage of next month budgetted sales

Month

April

Expected unit sales
Add: Desired ending finished goods

May

June

660
143

715
198

990
176

Less: Beginnig finished goods

(132)

(143)

(198)

Required units to produce

671

770

968

July

20%

August Total
880

825

2365
517
(473)

880

825

2409

Direct materials
Premium pine lumber

Per bed
60 feets

Desired ending

10%

Cost $
Paint

$2
0,5 pail

Desired ending
Cost $

15%
$68

The company
Direct material budget 2017

Month

March April

Units to be produced
Direct material
Pine (feets)
Add: Desired ending pine
Less: Beginning pine
Pine purchased (feet)
Total cost of pine$
Paint (pails)

May

June

July

August

671

770

968

880

825

40260

46200

58080

52800

49500

4950
(5280)
52470

0
(4950)
44550

4620
5808
5280
(5368)
(4620)
(5808)
39512
47388
57552
$79.024 $94.776 #######
336

385

484

Add: Desired ending paint
Less: Beginning paint
Paint purchased (pales)
Total cost of paint $

58
73
66
(51)
(58)
(73)
342
400
477
$23.273 $27.190 $32.463

Cost of direct ma...

flag Report DMCA
Review

Anonymous
Tutor went the extra mile to help me with this essay. Citations were a bit shaky but I appreciated how well he handled APA styles and how ok he was to change them even though I didnt specify. Got a B+ which is believable and acceptable.

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors