Create data in an excel spreadsheet for a project

There are 12 parts and basically you are creating a budget sheet for each part with the given information about a furniture store.

ColorfulFurniture Budget Project
ACC 202 – Spring 2020
Due Tuesday, April 28
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 year’s sales (2019) for the corresponding period were:
April
May
June
July
August
540 sofas
680 sofas
920 sofas
1,220 sofas
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 4×1 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 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…
Submission Requirements:
•
•
Submit your Excel spreadsheet to Canvas under Spreadsheet Assignment in
the Assignments menu.
Naming your file. The name of your file should follow this format:
lastname_firstname_202_spreadsheet, e.g., hyun_soonchul_202_spreadsheet.
Also, include your name in the spreadsheet.
Plagiarism:
This is to be your OWN work. Sharing files is unacceptable. The file submitted must be
your own work. If I consider files have been copied and you are submitting the work of
another student, I will assign zero for the assignment.
Late assignments will NOT be accepted.
Please email me (shyun@uncg.edu) if you have any questions.
ACC 202 Spreadsheet Assignment Grading Rubric
Assessment
Dimension
1. Presentation
Weighting
20%
2. Correctness
50%
3. Model Quality
30%
100%
Appropriate
formatting for $s
and numbers.
Clearly presented
schedules
85%
70%
Minimal formatting.
Some formatting.
Schedules hard to
Basic presentation
follow
Major errors, e.g.,
MOH miscalculations;
All schedules
missing schedules or
correct
quarter totals;
instructions not
followed.
Minimal use of input
Input area used.
Well-defined input
area. Hard keyed
Some use of hard
area. No hard keyed
numbers mean
keyed numbers in
numbers in
schedules don’t
formulas. Model
formulas. Model
update. Numbers
updates correctly
updates correctly
entered where
under some
for any variables.
formulas should be
circumstances
used
Errors in key cells e.g., ending
inventories,
quarter totals
50% or less
Unacceptable,
minimal effort
shown
Unacceptable,
minimal effort
shown
Unacceptable,
minimal effort
shown
Your spreadsheet assignment will be assessed on three dimensions. Each dimension carries a weighting,
e.g., presentation is weighted 20% of your score. You will be assessed on each dimension at one of four
levels (100%, 85%, 70%, 50% or less). See the descriptions for each dimension and level. Your
spreadsheet score will be a weighted average of performance across the three dimensions. For example,
if you scored 85% on presentation and correctness and 100% on model quality, then your score would
be: (85%*20%)+(85%*50%)+(100%*30%) = 89.5%
GBCoffee Company
2020 Budget Assumptions
Prepared by: Student Name
Sales Budget Assumptions
Unit Price
$
Percentage of Cash Sale
Percentage of Credit sale
Expected sales increase
2018 Sales
April
May
June
July
August
Production Budget Assumptions
Direct Materials Budget Assumptions
Direct Labor Budget Assumptions
Manufacturing Overhead Budget Assumptions
Operating Expense Budget Assumptions
Budgeted Manufacturing Cost Per Unit Budget Assumptions
Budgetd Income Statement Assumptions
15.00
35%
65%
8%
50,000
55,000
90,000
75,000
60,000
bags
bags
bags
bags
bags
GBCoffee Company
2020 Budget Assumptions
Prepared by: Student Name
Sales Budget
Unit Sales
Unit Price
Sales Revenue
Cash Sale
Credit Sale
Total
April
May
June
Quarter
July
August
April
May
June
Quarter
July
August
59,400.00
15.00
891,000.00
97,200.00
$
15.00
$ 1,458,000.00
210,600.00
$
15.00
$ 3,159,000.00
81,000.00
$
15.00
$ 1,215,000.00
$
$
311,850.00
579,150.00
891,000.00
$
510,300.00
947,700.00
$ 1,458,000.00
$ 1,105,650.00
2,053,350.00
$ 3,159,000.00
$
$
$
$
54,000.00
15.00
810,000.00
35% $
65%
$
283,500.00
526,500.00
810,000.00
$
$
$
$
425,250.00
789,750.00
$ 1,215,000.00
$
64,800.00
15.00
972,000.00
340,200.00
631,800.00
972,000.00
April
May
June
Quarter
July
August
April
May
June
Quarter
July
August
April
May
June
Quarter
July
August
April
May
June
Quarter
July
August
April
May
June
Quarter
July
August
April
May
June
Quarter
July
August
Production Budget
Unit sales
Desired dnding inventory
Total needed
Beginning inventory
Direct Material Budget
Direct Labor Budget
MOH Budget
Operating Expense Budget
Budget Manufacturing Cost per Unit
Budget Income Statement