The ColorfulFurniture Company manufactures modern wood frame lounge sofas.

Currently, the company makes only one size of three-seat sofas, which is 35 inches deep

by 90 inches wide. The final product consists of a routed, sanded, assembled, and stained

wood sofa. Direct materials include oak wood frames and pre-made cushions. Other

materials, such as wood legs, screws, hinges, sand paper, stain, and packaging, are treated

as indirect materials. ColorfulFurniture is preparing budgets for the second

quarter

ending June 30, 2020

. For each requirement below prepare budgets by month for April,

May, and June, and a total budget for the quarter.

1. The previous years sales (2019) for the corresponding period were:

April 540 sofas

May 680 sofas

June 920 sofas

July 1220 sofas

August 750 sofas

The company expects the above volume of lounge sofa sales to increase by 10% for

the period April 2020 August 2020. The budgeted selling price for 2020 is $850.00

per sofa. The company expects 15% of its sales to be cash (COD) sales. The

remaining 85% of sales will be made on credit.

Prepare a Sales Budget for

ColorfulFurniture.

2. The company desires to have finished goods inventory on hand at the end of each

month equal to 10 percent of the following month's budgeted unit sales. On March

31, 2020, the company expects to have 65 sofas on hand. (Note: an estimate of sales

in July is required in order to complete the production budget for June).

Use the

@ROUNDUP function to round up to the whole number the number of sofas

desired in ending inventory. Prepare a Production budget

.

3. The sofas require two direct materials: oak wood frames and pre-made cushions:

Sixteen (16) feet of 4x1 oak wood are required for each sofa produced. Management

desires to have materials on hand at the end of each month equal to 18 percent of the

following month's sofa production needs. The beginning inventory of wood, April

2020, is expected to be 2,340 feet of wood. Oak wood is expected to cost $8.00 per

foot. (Note: budgeted production in July is required in order to complete the direct

materials budget for June.

Use the @ROUNDUP function to round up to the whole

number the number of feet of oak wood to purchase).

Pre-made cushions (30*30 inches) are purchased by a set of 10 cushions. Six (6)

cushions are required for each sofa. Management desires to have cushions on hand at

the end of each month equal to 13 percent of the following month's production needs.

Use the @ROUNDUP function to round up to the whole number the number of

cushions desired in ending inventory.

The beginning inventory, April 2020, is

expected to be 630 cushions. The set of 10 cushions is expected to cost $200. (Note:

budgeted production in July is required in order to complete the direct materials

budget for June.

Use the @ROUNDUP function to round up to nearest 10 the

number of cushions to purchase).

Prepare a Direct Materials budget

. Also, because two direct materials are required

for production - oak wood and cushions - you will need a separate schedule for each

direct material.

4. Each sofa requires 10 hours of direct labor. ColorfulFurniture uses a series of table

saws, table routers and sanders set up for specialized operations to achieve production

efficiencies. Direct labor costs the company $20 per hour.

Prepare a Direct Labor

budget

.

5. ColorfulFurniture budgets indirect materials (e.g., wood legs, screws, hinges, sand

paper, stain, and packaging) at $35.50 per sofa. ColorfulFurniture treats indirect labor

and utilities as mixed costs. The variable components are $20.60 per sofa for indirect

labor and $7.50 per sofa for utilities. The following fixed costs per month are

budgeted for indirect labor, $55,000, utilities, $3,000, and other, $20,000.

Prepare a

Manufacturing Overhead budget.

6. Variable selling and administrative expenses are $50.50 per sofa sold. Fixed selling

and administrative expenses are $85,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 10,000 sofas.

8.

Prepare a Budgeted Income Statement for the quarter for ColorfulFurniture .Assume interest expense of $0, and income tax expense of 21% of income before

taxes.

Directions:

Refer to Chapter 9 (The Master Budget) for guidance in setting up your budgets and

schedules. 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 per lounge sofa changes from $850 to

$855 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 will be graded on presentation, correctness, and quality of your

spreadsheet model (i.e., does it update correctly for changes in input variables). See

the grading rubric on Canvas. You should approach this assignment as if you are the

Management Accountant at the ColorfulFurniture Company and you are going to present

these budgets in a meeting to the CEO, CFO, and other management personnel.

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, feet per sofa, ending inventory percentage, etc. You

may use the Assumptions tab of the sample spreadsheet or 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 (see

sample spreadsheet file). To the extent possible, 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. That is, enter a constant value in

only one location in the worksheet. Then if you use the value in another cell, use a

cell reference that refers to the constant value's unique location. Example (hypothetical): You enter the constant value of 6% for sales tax

in cell E5. When you write a formula in your worksheet that requires sales tax, reference E5 in the formula instead of "hard-coding" in the 6% value.

Do: =subtotal*E5

Don't: =subtotal*6%

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 years 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...

so far here's what I have: Please answer in this format and try to show the formulas used within each cell if possible

April May June Quarter July August $ Sales Budget Unit Sales Unit Price Sales Revenue 594.00 850.00 504,900.00 $ $ $ 748.00 $ 850.00 $ 635,800.00 $ 1,012.00 850.00 860,200.00 $ $ $ 2,354.00 850.00 2,000,900.00 1,342.00 850.00 $ $ 825.00 850.00 Cash Sale Credit Sale Total 15% 85% $ $ $ 75,735.00 $ 429,165.00 $ 504,900.00 $ 95,370.00 540,430.00 635,800.00 $ $ $ 129,030.00 $ 731.170.00 $ 860,200.00 $ 300,135.00 1.700.765.00 2,000,900.00 $ $ $ 171,105.00 $ 969,595.00 $ 1,140,700.00 $ 105,187.50 596,062.50 701,250.00 April May June Quarter July August Production Budget Unit sales Plus: Desired ending inventory Total Needed Less: Begginning inventory Number of units to produce 594.00 75.00 669.00 59.00 610.00 $ $ $ $ $ 748.00 $ 101.00 $ 849.00 $ 75.00 $ 774.00 $ 1,012.00 $ 134.00 $ 1,146.00 $ 101.00 $ 1,045.00 $ 2,354.00 310.00 2,664.00 235.00 2,429.00 LA LA LA 1,342.00 825.00 2,167.00 134.00 2,033.00 $ $ $ $ $ 825.00 65.00 890.00 83.00 807.00 April May June Quarter July August $ Direct Material Budget Budgeted Production Materials required (feet) Ending inventory Beginning invenotry Quantity to purchase Cost of purchase $ $ 610.00 $ 9,760.00 $ 2,229.00 $ 1,757.00 $ 10,232.00 $ 81,856.00 $ 774.00 $ 12,384.00 $ 3,009.00 $ 2,229.00 $ 13,164.00 $ 105,312.00 $ 1,045.00 $ 16,720.00 $ 5,855.00 $ 3,010.00 $ 19,565.00 $ 156,520.00 $ 2,429.00 38,864.00 11,093.00 6,996.00 42,961.00 343,688.00 2,033.00 32,528.00 2,324.00 5,855.00 28,997.00 463,952.00 $ $ $ $ $ $ 807.00 12,912.00 2,340.00 2,324.00 12,928.00 206,848.00 $ $ April May June Quarter July August Direct Labor Budget Budgeted Production Direct labor required DL rate / Hour (20/hr) Direct labor cost @ $20per hour 610.00 $ 6,100.00 $ 20.00 $ 122,000.00 $ 774.00 $ 7,740.00 $ 20.00 $ 154,800.00 $ 1,045.00 $ 10.450.00 $ 20.00 $ 209,000.00 $ 2,429.00 24.290.00 20.00 485,800.00 $ $ $ $ 2,033.00 $ 20,330.00 $ 20.00 $ 406,600.00 $ 807.00 8,070.00 20.00 161,400.00 April May June Quarter July August v 8) MOH Budget Number of units Produced Indirect materials @ $35.50 Indirect labor @ $20.60 Utilities @ $7.50 Indirect labor fixed Budgeted manufacturing overhead V 610.00 21,655.00 12,566 4,575 78,000 116,796 $ $ $ $ $ $ 774.00 $ 27,477.00 $ 15,944 $ 5,805 $ 78,000 $ 127,226 $ 1,045.00 $ 37,097.50 $ 21,527 $ 7,838 $ 78,000 $ 144,462 $ 2,429.00 86,229.50 50,037 18,218 78,000 388,484 2,033.00 $ 72,171.50 $ 41,880 $ 15,248 $ 78,000 $ 207,299 $ 807.00 28,648.50 16,624 6,053 78,000 129,325 G G V April May June Quarter July August G Operating Expense Budget Budgeted Sales Variable operating expense Fixed operating expense Total operating expenses G 594.00 $ 29,997 $ 85,000 $ 114,997 $ 748.00 $ 37,774 $ 85,000 $ 122,774 $ 1,012.00 $ 51,106 $ 85,000 $ 136,106 $ 2,354 118,877 85,000 203,877 1,342.00 $ 67,771 $ 85,000 $ 152,771 $ 825.00 41,663 85,000 126,663 V v Budget Manufacturing Cost per Unit Per unit Total Per Year 128.00 120.00 200.00 Direct Material Oak wood frames (16 feet * $8 per feet] pre-made cushions [6 units * $20 per unit Direct Labor [10 hours * $20 per hour] Indirect Cost variable Variable indirect material Variable indirect labor Variable utilities Indirect fixed cost indirect labor [total/10,000 units] utilities total/10,000 units] other [total/10,000 units] 35.50 20.60 7.50 $ 2,000,900.00 budget Income Statement Sales income Less: Cost of goods sold [total units sold *$605.20] Selling and administrative expense Interest expense Income Before Tax Tax @ 21% Net Income After Tax