Spring Semester 2019
ABI-WE #2
Name:
(INSERT YOUR NAME HERE)
PID:
(INSERT YOUR PID HERE)
CHAPTER 6-7
Company Profile
Spartan Furniture: New from Old, Inc. is a furniture manufacturing company founded in 2010 by Spartan
entrepreneurial students to make new furniture out of old trees that have either fallen down or had to be cut
down from campus. The company has a working relationship with MSU's Forestry Department and Surplus
Store to mill wood from trees that are often 100-year old hardwoods. Spartan Furniture designs the furniture
and employs local woodworkers in constructing high quality, reasonably priced furniture. A loyal customer base
of Spartan alumni who want to enjoy a piece of MSU history in their homes has helped the company grow
tremendously over the last seven years. (Note: Spartan Furniture: New from Old, Inc. is a fictitious company as
are the data provided; however, it is based on the real MSU's Shadows Collection available through the MSU
Surplus Store.)
Spartan Furniture has three product lines:
1) Dining room tables
2) Chairs
3) Benches
Spartan Furniture is headquartered in Lansing MI where it has a production facility and support services for
accounting, marketing, human resource, legal, and other administrative tasks. The company has another
production facilities in Escanaba in the Upper Peninsula which specializes in the chairs product line and
particular types of wood.
Scenario
Your boss seems pleased with your work in the first month of your internship, and you are very happy you are
more comfortable with data and Excel. You just received the following memo with some new tasks for this
month.
Task
Hi [Your Name]:
Thanks for your help so far. You did a great job on the income statement! I need your help for two different tasks this
month.
First, please run a break-even analysis to determine how many units we must sell to achieve our investors’ goal. Although
we had a great profit margin in 2017, our investors want us to stretch to achieve a profit of $3,645,710 next year. Our
production and sales team want to know what they need to produce and sell to reach this ambitious target. For this
analysis, we’ll assume items in each of our three product lines cost and sell for the same amount.
To simplify things, assume our product mix remains constant for any number of units produced, and our existing cost
structure remains the same. I included our expense report and a summary of our company’s accounting policies. I also
created a template to guide you through your analysis.
Second, can you help me analyze the difference between variable and absorption costing? I read a great article about
how useful variable costing can be for internal decision making, and I’m confused about how it affects the income
statement. Create an absorption and variable costing income statement so we can compare both methods. Use the cost
data from the target profit analysis. I included some templates to guide you.
Let me know if you have any questions. Thank you!
Haidee Brady
Controller- Spartan Furniture: New from Old, Inc.
Grading Rubric
Use of ALL functions
VLOOKUP
RENAME
GOALSEEK
PIVOT TABLE
IF
COMMENT
Accuracy (all or nothing)
Unit sales to reach target
Average selling price to reach target
Quality of comment's recommendation
Absorption cost of goods sold
Variable cost of goods sold
Selling expense (absorption costing)
Administrative expense (absorption costing)
Total fixed costs (variable costing)
Reconciliation in cell D31
$ value of fixed MOH in ending inventory (cell D34)
Predetermined MOH rate
MOH Over (Under) allocated $ amount (cell B45)
Selecting over/under allocated (cell C45)
Presentation and Communication
All numbers formatted correctly
TOTAL
# Points
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
20
ACC 202 EXCEL DIRECTORY
NOTES
We expect you know the basics of creating, using, and saving MS-Excel worksheets in workbooks from CSE101. Need
an Excel refresher? Email trinkle4@msu.edu and ask to be added to the "BEEP" (Broad Excel Enhancement
Program), a D2L community with no fee and available for one year. You will find excellent short videos prepared by
the Goodwill Community Foundation on the Excel functions we will use in the ABI-WEs.
There are different ways to accomplish a task in Excel, for example:
1. Use the ribbon at the top of the worksheet. Choose a category, then look at each of the folders and click the icon
in the bottom right corner of that folder for more options.
2. Use keyboard shortcuts, such as Alt (don't need to hold down), Ctrl (hold down), Shift, and Function keys.
3. Right click to pull up options.
4. Look at Quick Icons (frequently used commands) at the top of the worksheet.
EXCEL COMMAND AND FUNCTIONS
The following Excel commands below are those we'll use in the ABI-WEs are presented with the path through the
ribbon of the most current version of MS-Excel for a PC. Instructions for a MAC are similar but not identical. If
confused, use the HELP command.
COMMAND/FUNCTION
ADD/REMOVE DECIMALS
COMMENT
INFORMATION
Purpose: To increase or decrease the number of decimal places of a number.
HOME-->NUMBER-->icons with right and left arrows
Purpose: To create a pop up box with the author's name that provides a short
message. Indicated by a red triangle in the top, right of the cell.
REVIEW-->COMMENTS-->NEW COMMENT
GOAL SEEK
Goal Seek is a feature that allows you to perform a 'what-if analysis' by having Excel
set one cell to a particular value by changing another cell that is referenced by the
On the top ribbon in Excel go "Data", then "Data Tools", click "What if Analysis" then
Where it says "Set Cell:" reference or click on the cell corresponding to net income in
the "Company's Cost Formula" area of the "CVP Analysis" tab.
Where it says, "To Value:" enter 6,550,170.
Where it says, "By Changing:" reference or click on the cell corresponding to unit
sales in the "Company's Cost Formula" area of the "CVP Analysis" tab. For Scenario
Click ok.
You should see that the unit sales (or selling price in scenario # 2) cell changed in
order to make the net income cell equal to $6,550,170.
COPY CELLS
Purpose: To copy contents of a cell to another cell, either "relatively" (so that
formulas adjust) or "absolutely" (as the same value) and paste it to the new cell.
Either, drag the bottom right corner of a cell to other cells or enter =B4 to copy
"relatively" of =$B$4 to copy "absolutely"
FORMAT CELLS
FORMAT NUMBERS
GRIDLINES
Purpose: To change font type and size, color, and alignment of a cell.
HOME-->then FONT, ALIGNMENT
Purpose: To label numbers so they present in an understandable way.
HOME-->NUMBER then drop down menu to choose currency, dates, percentages…
Purpose: To remove gridlines so the worksheet is cleaner to look at.
VIEW-->SHOW-->GRIDLINES-->check or uncheck
IF
Purpose: To set up a condition that has to be met; that is, IF X is true, then do this,
otherwise, do this.
FORMULAS-->FUNCTION LIBRARY-->LOGICAL-->IF e.g., =IF(B6>10,"true","false")
PAGE BREAK PREVIEW
Purpose: To ensure the worksheet will properly print on 8 1/2"x11" paper.
VIEW-->WORKBOOK VIEWS-->PAGE BREAK PREVIEW
Note: Drag the blue lines to adjust breaks. Return to Normal. View in FILE -->PRINT
before printing.
REFERENCING
Purpose: To link cells together with reference to formulas within a worksheet or
across worksheets, so a change in the original data will flow through to cells that
reference that cell.
Enter, for example, =B4 when you want to copy the formula in B4 to another cell.
SUM
Purpose: To add up values in two or more cells.
FORMULAS-->FUNCTION LIBRARY-->MATH&TRIG-->SUM e.g., =SUM(B4:B11)
SUMIF
Purpose: To add up cells that meet the condition described by the IF clause.
FORMULAS-->FUNCTION LIBRARY-->MATH&TRIG-->SUMIF
TEXT TO COLUMNS
VLOOKUP
Purpose: To separate data in one cell that is separated by semicolons into separate
columns.
DATA-->DATA TOOLS -->TEXT TO COLUMNS-->DELIMITED-->SEMICOLON
Purpose: To link to data in another worksheet or area of the same worksheet.
FORMULAS-->FUNCTION LIBRARY-->LOOKUP & REFERENCE-->VLOOKUP
Y
heets in workbooks from CSE101. Need
EP" (Broad Excel Enhancement
ind excellent short videos prepared by
ABI-WEs.
at each of the folders and click the icon
own), Shift, and Function keys.
et.
presented with the path through the
AC are similar but not identical. If
TION
of decimal places of a number.
arrows
or's name that provides a short
p, right of the cell.
rm a 'what-if analysis' by having Excel
nother cell that is referenced by the
ata Tools", click "What if Analysis" then
the cell corresponding to net income in
VP Analysis" tab.
ck on the cell corresponding to unit
the "CVP Analysis" tab. For Scenario
rice in scenario # 2) cell changed in
,550,170.
cell, either "relatively" (so that
value) and paste it to the new cell.
o other cells or enter =B4 to copy
and alignment of a cell.
an understandable way.
hoose currency, dates, percentages…
t is cleaner to look at.
ck
met; that is, IF X is true, then do this,
->IF e.g., =IF(B6>10,"true","false")
rly print on 8 1/2"x11" paper.
EVIEW
turn to Normal. View in FILE -->PRINT
to formulas within a worksheet or
data will flow through to cells that
py the formula in B4 to another cell.
s.
RIG-->SUM e.g., =SUM(B4:B11)
on described by the IF clause.
RIG-->SUMIF
eparated by semicolons into separate
>DELIMITED-->SEMICOLON
or area of the same worksheet.
& REFERENCE-->VLOOKUP
TASKS
Step 1: Determine the variable and fixed portions of each expense.
You have been provided worksheets with an "Expense Report" template and "Accounting Policies" showing how the company classifies expenses.
In the "Expense Report" use VLOOKUP to fill in the Type, Classification, and Product/Period columns for each expense with information from the
"Accounting Policies" worksheet.
Complete the Cost per Unit column in the "Expense Report" worksheet. Use the production data (in blue) at the top right corner of the worksheet. Wh
finding cost per unit use an IF statement so that Total Units Produced is used for product costs and Total Units Sold is used for period costs.
TIP: You only have to enter a formula once in the top row. REFERENCE (Absolutely) the appropriate cells and COPY (or drag) the formula down to inclu
all rows in that column.
Step 2: Turn the data into a Pivot Table.
Select the data from the "Expense Report" starting with the left column titled, "Acct. #", moving over to include all the columns and down to include ev
expense on the trial balance. TIP: It is important to include the row that includes the column titles.
Insert a PIVOT TABLE on a new tab. RENAME the tab, "Pivot Table". TIP: You can right click on the name of the worksheet to change its name.
Step 3: Pivot the table appropriately to obtain relevant information.
In the FIELD LIST next to the Pivot Table, drag Classification and Type into the filter.
In the FIELD LIST next to the Pivot Table, drag Account Description to the rows and Total Cost to the sum of values. Be sure the VALUE FIELD SETTINGS
set to SUM not COUNT. TIP: right click anywhere in the Pivot Table to open up the Field List choices on the right-hand side.
Filter the Pivot Table to list Fixed, MOH costs.
Highlight the expense titles and total cost in the Pivot Table and copy and paste them to the "Cost Structure" worksheet in the section for Fixed Overhe
(MOH).
Next, filter the Pivot Table to list Fixed, selling expenses and copy and paste the expense titles and amounts into the "Cost Structure" tab in the section
Fixed Selling. Follow the same steps above to fill in the Fixed Administrative section.
On the FIELD LIST next to the Pivot Table, drag Total Cost out of the Sum of Values and drag Cost Per Unit into the Sum of Values. THIS IS AN IMPORTAN
STEP! Variable costs should be shown as per unit amounts, NOT total amounts.
With the Type filter set to variable, use the Classification filter to find variable cost per unit for Selling, MOH, direct labor, and direct materials. Copy the
expense titles and costs per unit for these items from the "Pivot Table" to the "Cost Structure" worksheet and place within the appropriate section. B
sure the VALUE FIELD SETTINGS are set to SUM not COUNT.
Step 4: Perform a CVP Analysis to determine how to reach a profit of $3,645,710.
REFERENCING the information you just copied from the Pivot Table, complete the CVP analysis section in the top right portion of the "Cost Structure" ta
For the units sold and average selling price items only, hard-code the amounts into your table or your goal seek function will not work.
In the "Find Unit Sales" portion of the "Cost Structure" worksheet, use GOAL SEEK to determine the units sold required to attain a profit of $3,645,710.
The average selling price per unit should be $4,900.
TIP: You have to have formulas entered before you can use GOAL SEEK.
Under the "Find Selling Price" table use GOAL SEEK to determine the per unit selling price required to attain a profit of $3,645,710 assuming unit sales d
not increase. Enter the units sold from the top right portion of the "Expense Report".
Enter a COMMENT in the cell labeled Comment on the "Cost Structure" worksheet explaining to your boss (Your GTA) two potential ways of reaching t
targeted profit based on your GOAL SEEK analysis and which one you recommend. Your comment should be addressed to your boss (Your GTA) and
include your name. Be professional in tone and be sure to use PROPER SPELLING. See the example to the right.
ompany classifies expenses.
with information from the
ght corner of the worksheet. When
used for period costs.
r drag) the formula down to include
columns and down to include every
eet to change its name.
sure the VALUE FIELD SETTINGS are
side.
et in the section for Fixed Overhead
Cost Structure" tab in the section
m of Values. THIS IS AN IMPORTANT
bor, and direct materials. Copy the
ithin the appropriate section. Be
portion of the "Cost Structure" tab.
on will not work.
d to attain a profit of $3,645,710.
$3,645,710 assuming unit sales do
two potential ways of reaching the
d to your boss (Your GTA) and
Spartan Furniture: New from Old, Inc.
Expense Report
Year ended December 31, 2018
Acct. #
3599
3393
3396
3930
3922
3685
3786
3659
3231
3719
3280
3425
3616
3887
3680
3704
3292
3522
3570
3723
3971
3784
3342
3734
3334
3955
Account Description
Advertising Expense
Employee Benefits-Admin
Employee Benefits-Sales
Commissions expense
Maintenance- Factory Equip
Maintenance-Office Equipment
Insurance- Property/Equip
Insurance-Headquarters
Maintenance
Maintenance-Headquarters
Marketing Expense
Packaging Expenses
Professional Fees-Legal Services
Property Taxes-Factories
Property Taxes- Headquarters
Rent-Administrative
Rent-Factory Equipment
Rent-Sales Vehicles
Repairs-Factory
Repairs-Headquarters
Salary-Administrative Staff
Salary-Sales
Supplies Expense- Admin
Supplies Expense- Factory
Supplies Expense-Sales Offices
Travel-CEO
$
Total Cost
50.750
6.500
5.600
483.803
1.460.800
48.000
200.000
72.000
97.000
10.000
507.600
265.000
45.000
29.000
10.000
22.000
100.000
35.000
125.000
5.000
550.000
720.000
14.109
90.000
46.582
6.611
Type
Fixed
Fixed
Fixed
Variable
Fixed
Fixed
Fixed
Fixed
Variable
Fixed
Fixed
Variable
Fixed
Fixed
Fixed
Fixed
Fixed
Fixed
Variable
Fixed
Fixed
Fixed
Fixed
Variable
Fixed
Fixed
Classification
Selling
Admin
Selling
Selling
MOH
Admin
MOH
Admin
MOH
Admin
Selling
Selling
Admin
MOH
Admin
Admin
MOH
Selling
MOH
Admin
Admin
Selling
Admin
MOH
Selling
Admin
3325
3915
3871
3611
3999
3049
3001
Travel-Sales
Utilities-Factories
Utilities-Headquarters
Wages- Line Workers
Salaries- Designers
Salaries- Factory Supervisors
Direct Materials
$
45.000
1.750.350
80.000
400.000
875.000
500.000
5.500.340
Fixed
Variable
Fixed
Variable
Fixed
Fixed
Variable
Selling
MOH
Admin
DL
MOH
MOH
DM
Product/Period
Period
Period
Period
Period
Product
Period
Product
Period
Product
Period
Period
Period
Period
Product
Period
Period
Product
Period
Product
Period
Period
Period
Period
Product
Period
Period
Cost Per Unit
$
16,94
$
2,17
$
1,87
$
161,54
$
434,76
$
16,03
$
59,52
$
24,04
$
28,87
$
3,34
$
169,48
$
88,48
$
15,03
$
8,63
$
3,34
$
7,35
$
29,76
$
11,69
$
37,20
$
1,67
$
183,64
$
240,40
$
4,71
$
26,79
$
15,55
$
2,21
Total Units Produced:
3.360
Total Units Sold:
2.995
Period
Product
Period
Product
Product
Product
Product
$
$
$
$
$
$
$
15,03
520,94
26,71
119,05
260,42
148,81
1.637,01
Acct. #
3001
3049
3231
3280
3292
3325
3334
3342
3393
3396
3425
3522
3570
3599
3611
3616
3659
3680
3685
3704
3719
3723
3734
3784
3786
3871
3887
3915
3922
3930
Type
Variable
Fixed
Variable
Fixed
Fixed
Fixed
Fixed
Fixed
Fixed
Fixed
Variable
Fixed
Variable
Fixed
Variable
Fixed
Fixed
Fixed
Fixed
Fixed
Fixed
Fixed
Variable
Fixed
Fixed
Fixed
Fixed
Variable
Fixed
Variable
Classification
DM
MOH
MOH
Selling
MOH
Selling
Selling
Admin
Admin
Selling
Selling
Selling
MOH
Selling
DL
Admin
Admin
Admin
Admin
Admin
Admin
Admin
MOH
Selling
MOH
Admin
MOH
MOH
MOH
Selling
Product/Period
Product
Product
Product
Period
Product
Period
Period
Period
Period
Period
Period
Period
Product
Period
Product
Period
Period
Period
Period
Period
Period
Period
Product
Period
Product
Period
Product
Product
Product
Period
3955
3971
3999
Fixed
Fixed
Fixed
Admin
Admin
MOH
Period
Period
Product
Per Unit
Variable Selling
Fixed Administrative
Grand Total
Check:
250,02
Variable Overhead (MOH)
Grand Total
Check:
613,79
Grand Total
Check:
Direct Labor
Grand Total
Check:
119,05
Fixed Selling
Direct Materials
Grand Total
Check:
1637,01
Grand Total
Check:
Fixed Overhead (MOH)
Grand Total
Check:
Find Unit Sales
Total Dollars
dministrative
d Selling
Average selling price
Units sold
Sales
Variable costs
Fixed costs
Net operating income*
Find Selling Price
Average selling price
Units sold
Sales
Variable costs
Fixed costs
Net operating income*
869.220
Comment
*For this task only, assume units sold=units
produced.
1.410.532
erhead (MOH)
3.164.800
TASKS
Step 1: Prepare traditional and variable costing income statements.
The "Income Statements" worksheet contains outlines for traditional and variable costing income statements.
REFERENCE data from the "Cost Structure" worksheet to complete each income statement. You do not need to fill in
the per unit amounts for the fixed costs on the variable costing income statement. Use $4,900 as your selling price
per unit and the units sold and units produced at the top of your "Expense Report" tab.
TIP: When finding the per unit cost, or going from the per unit cost to total cost, REFERENCE the units sold and units
produced at the top of the "Income Statements" worksheet. For example, per unit sales will equal the average selling
price as given on the "Cost Structure" worksheet. Total sales will equal the per unit sales multiplied by the units sold,
as given on the "Income Statements" worksheet. DO NOT USE THE UNITS SOLD ON THE "Cost Structure" worksheet
that resulted from GOAL SEEK. It may be easiest to complete the per unit column first with data from the "Cost
Structure" worksheet.
Step 2: Reconcile the net operating incomes under traditional and variable costing.
Fill out the cells in the reconciliation box to reconcile absorption costing net operating income to variable costing net
operating income.
HINT: The difference between absorption costing net operating income and variable costing net operating income
should be equal to the fixed MOH expensed as a period cost under variable costing, but maintained in ending
inventory under absoprtion costing as a product cost.
Step 3: Find out how much manufacturing overhead was either over or under allocated.
Use the information from Step 1 as well as the information that is given to calculate whether the MOH was over- or
under-stated. The company uses direct labor hours to allocate overhead.
Step 1
Production Information 2018
Units produced
Units sold
Spartan Furniture: New From Old, Inc.
Absorption Costing Income Statement
for the year ended December 31, 2018
Per Unit
Total
Sales
Less: Cost of goods sold
Direct materials
Direct labor
Manufacturing overhead
Total cost of goods sold
Gross margin
Less: Operating expenses
Selling expense
Administrative expense
Net operating income
Step 2
Reconciliation of Absorption Costing NOI and Variable Costing NOI
Difference between absorption NOI and variable NOI
Difference between units produced and units sold
Fixed manufacturing overhead per unit produced
The dollar value of fixed manufacturing overhead remaining in ending inventory under
absorption costing, but expensed under variable costing as a period cost:
Step 3
Budgeted direct labor hours
Actual direct labor hours
Budgeted total MOH
Actual total MOH
Predetermined MOH rate
Overhead allocated
MOH over (under) allocated
275.500
262.500
4.100.500
-
$
$
Another view (not required):
MOH
Actual
$
Allocated
-
$
-
Spartan Furniture: New From Old, Inc.
Variable Costing Income Statement
for the year ended December 31, 2018
Per Unit
Sales
Less: Variable Cost of Goods Sold
Direct materials
Direct labor
Variable overhead
Total variable cost of goods sold
Less: Variable selling and administrative expenses:
Variable selling expense
Total variable expenses
Contribution margin
Less: Fixed costs
Fixed manufacturing overhead
Fixed selling
Fixed administrative
Total fixed costs
Net operating income
Total
FOR QUALITY ASSURANCE PURPOSES, BEFORE SUBMITTING YOUR
ASSIGNMENT DO ALL OF THE FOLLOWING
1 Enter your name and PID on the top right of the Scenario worksheet. Save your file.
Keep it secure, it is your intellectual property.
2 FORMAT all numbers appropriately, include commas where appropriate, etc.
3 Round each total number to the nearest dollar. Round each per unit amount (i.e.
prices) to the nearest cent. Round percentages to two decimal places. Use the
ADD/REMOVE decimals button (this will change the format from "Accounting" to
"Custom", THIS IS FINE). DO NOT use the ROUND function.
4 Enter a COMMENT to alert your teaching assistant anytime you made any significant
assumptions.
5 Remember! Check to be sure your file was submitted properly in D2L. If a file
cannot be seen or read by your TA, it cannot be graded. REMEMBER: Do not submit
this file if it was started on someone else's file. The syllabus clearly defines this as an
example of academic dishonesty, and there are consequences.
IMPORTANT!!!
IMPORTANT!!!
Purchase answer to see full
attachment