Microsoft Excel fill out info

Complete the remaining 3 tabs in the Project Workbook Spreadsheet.

Scenario
It has now been one month since you launched your business. You are meeting with a few
investors soon to share your vision for the company and to ask for more funding. You will be
sharing your company’s financial health at this meeting. To do that, you need to determine and
analyze your company’s performance over the last month using the data provided.
Prompt
Use the information in the Milestone Three Actual Costs and Revenue Data Appendix Word
Document to evaluate your company’s performance and complete the remaining tabs in
the Project Workbook Spreadsheet.
Specifically, you must address the following rubric criteria:
• Statement of Cost of Goods Sold: Prepare the statement of cost of goods sold in the
“COGS” tab of the workbook. Remember to show your work using calculations to the
side of the table or using appropriate formulas in the table.
• Income Statement: Use the given revenue data to prepare the “Income Statement” tab
table and calculate the net income. Remember to show your work using calculations to
the side of the table or using appropriate formulas in the table.
• Variance Analysis: Prepare the data in the “Variances” tab to determine whether the
variances are favorable or unfavorable. Remember to show your work using
calculations to the side of the table or using appropriate formulas in the table.
o
Complete the data table for the variances by entering the budgeted (standard)
and actual labor and material values. Remember to use the estimates for
expected sales from your Milestone Two assignment.
o
Determine the variances for direct labor and direct materials in the
“Variances” tab.
o
Evaluate the significance of the variances in the “Variances” tab, and mark
them as favorable or unfavorable.
H
Milestone One – Cost Classification
INSTRUCTIONS:
Putting an X in the appropriate spot, classify the costs as: Direct Material, Direct Labor, Overhead, or Period Costs.
The Fixed and Variable cost classifications have been provided for you.
Item/Cost
Direct
Material
Direct
Labor
Overhead
Period Costs
Fixed
Variable
Salary – Collar maker
x
x
Salary – Leash maker
x
x
Salary – Harness maker
x
x
x
Salary – Receptionist
High-tensile strength nylon webbing
x
x
Polyester/nylon ribbons
x
x
Buckles made of cast hardware
x
x
Depreciation on sewing machines
x
x
Rent
x
x
Utilities and insurance
x
x
Scissors, thread, and cording
x
x
Price tags
x
x
Office supplies
x
Other business equipment
x
Loan payment
x
x
Salary to self
x
x
ACC202 – MANAGERIAL ACCOUNTING
H
Milestone One – Variable and Fixed Costs
Workings
Collars
Item
Variable Cost/Item
Item
High-tensile strength nylon webbing
Polyester/nylon ribbons
Buckles made of cast hardware
Price tags
$
$
$
$
4.00
3.00
2.00
0.10
Collar maker’s salary (monthly)
Depreciation on sewing machines
Rent
Utilities and insurance
Scissors, thread, and cording
Loan payment
Salary to self
$
$
$
$
$
$
$
Fixed Costs
2,773.33
55.00
250.00
200.00
400.00
183.33
166.67
Total Variable Costs per Collar
$
9.10
Total Fixed Costs
$
4,028.33
Variable cost
high tensile strength nylon webbing cost: $12/3 = $4 per collar
Ribbon cost: $9/3 = $3 per collar
2773.333
55
250
200
400
183.3333
166.6667
Buckle cost: $0.50 * 4 = $2 per collar
Price tags 0.10 each
Total variable cost per collar = $4 (webbing) + $3 (ribbon) + $2 (buckle)+ 0.1 (price tag) = $9.10 per collar
2773.333
-55
250
200
400
183.3333
166.6667
Variable cost
high tensile strength nylon webbing cost: $12/2 = $6 per collar
Leashes
Item
Variable Cost/Item
Item
High-tensile strength nylon webbing
Polyester/nylon ribbons
Buckles made of cast hardware
Price tags
$
$
$
$
Leash maker’s salary (monthly)
Depreciation on sewing machines
Rent
Utilities and insurance
Scissors, thread, and cording
Loan payment
Salary to self
$
$
$
$
$
$
$
2,773.33
55.00
250.00
200.00
400.00
183.33
166.67
Total Fixed Costs
$
4,028.33
Total Variable Costs per Leash
$
6.00
4.50
1.50
0.10
12.10
Fixed Costs
Ribbon cost: $9/2 = $4.5 per collar
Buckle cost: $0.50 * 3 = $1.5 per collar
Price tags 0.10 each
Total variable cost per collar = $6 (webbing) + $4.5(ribbon) + $1.5 (buckle) + 0.1 (price tag)= $12.1per collar
$4,028.33
Harnesses
Item
Variable Cost/Item
Item
High-tensile strength nylon webbing
Polyester/nylon ribbons
Buckles made of cast hardware
Price tags
$
$
$
$
6.00
4.50
4.00
0.10
Harness maker’s salary
Depreciation on sewing machines
Rent
Utilities and insurance
Scissors, thread, and cording
Loan
Salary to self
$
$
$
$
$
$
$
Fixed Costs
2,946.68
55.00
250.00
200.00
400.00
183.33
166.67
Total Variable Costs per Harness $
14.60
Total Fixed Costs
$
4,201.68
2946.667
55
250
200
400
183.3333
166.6667
ACC202 – MANAGERIAL ACCOUNTING
high tensile strength nylon Webbing cost: $12/2 = $6 per harness
Ribbon cost: $9/2 = $4.50 per harness
Buckle cost: $0.50 * 8 = $4 per harness
Price tag each at 0.10
Total variable cost per harness = $6 (webbing) + $4.50 (ribbon) + $4 (buckle) + price tag 0.1 = $14.60 per harness
H
Milestone Two – Contribution Margin Analysis
COLLARS
LEASHES
HARNESSES
Sales Price per Unit
Variable Cost per Unit
$
$
24.00
9.10
$
$
26.00
12.10
$
$
30.00
14.60
Contribution Margin
$
14.90
$
13.90
$
15.40
ACC202 – MANAGERIAL ACCOUNTING
H
Milestone Two – Break-Even Analysis
COLLARS
Sales Price
$
Fixed Costs
Contribution Margin
$
Break-Even Units (round up)
14.90
$
300.00
$
500.00
304.00
$
13.90
$
302
$
290.00
$
HARNESSES
26.00
4,201.68
270
Break-Even Units (round up)
Target Profit
$
4,028.33
Break-Even Units (round up)
Target Profit
LEASHES
24.00
400.00
Fixed Costs / (Sales Price Per Unit – Variable Cost Per Unit)
Break Even Units =
Fixed Costs / (Contribution Margin Per Unit)
4,201.68
Collars =
Collars =
$4028.33/14.90
270
Leashes=
Leashes=
$4201.68/13.90
302
Harnesses=
Harnesses=
$4201.68/15.40
273
15.40
273
$
331.00
$
Break Even Units =
30.00
500.00
305.00
600.00
$
650.00
345.00
$
315.00
Collars
Collars =
Generate $300
Collars =
Collars =
Break Even Units =
Generate $500
Collars =
Collars =
Break Even Units =
ACC202 – MANAGERIAL ACCOUNTING
Target Profit of $300
270 Break Even
300/14.90
20
290.00
500/14.90
34
304.00
Generate $400
Leashes=
Leashes=
Break Even Units =
400/13.90
29
331.00
Generate $600
Leashes=
Leashes=
Break Even Units =
600/13.90
43
345.00
Generate $500
Harnesses=
Harnesses=
Break Even Units =
500/15.40
32
305.00
Generate $650
Harnesses=
Harnesses=
Break Even Units =
650/15.40
42
315.00
H
Milestone Three – Statement of Cost of Goods Sold
Beginning Work in Process Inventory
Direct Materials:
Materials: Beginning
Add: Purchases for month of January
$
–
0
Materials available for use
Deduct: Ending materials
Materials Used
Direct Labor
Overhead
Total Costs
Deduct: Ending Work in Process Inventory
0
Cost of Goods Sold
ACC202 – MANAGERIAL ACCOUNTING
Milestone Three – Income Statement
Revenue:
Collars
Leashes
Harnesses
$
–
Total Revenue:
Cost of goods sold
Gross profit
$
$
–
Total Expenses
$
–
Net Income/Loss
$
–
Expenses:
General and administrative salaries
Office supplies
Other business equipment
$
ACC202 – MANAGERIAL ACCOUNTING
–
Milestone Three – Variance Analysis
Data for Variance Analysis:
Budgeted
(Standard)
Hours/Qty
Budgeted
(Standard)
Rate
Actual
Hours/Qty
Actual
Rate
Variance
Favorable/
Unfavorable
Labor
Materials
Variances for Collar Sales
Direct Labor Time Variance
(Actual Hours – Standard Hours) x Standard Rate
$
–
Direct Labor Rate Variance
(Actual Rate – Standard Rate) x Actual Hours
$
–
Direct Materials Quantity/Efficiency Variance
(Actual Quantity – Standard Quantity) x Standard Price
$
–
Direct Materials Price Variance
(Actual Price – Standard Price) x Actual Quantity
$
–
ACC202 – MANAGERIAL ACCOUNTING
Milestone Three: Actual Costs and Revenue Data Appendix
At the end of the first month of opening your business, you calculate the actual operating costs of the
business and the income you earned. You also notice and document the difference in what you
budgeted for certain materials and labor against the actual amounts you spent on the same.
For your statement of cost of goods sold, use the following data regarding the actual costs incurred by
the business over the past month:
•
Materials purchased: $20,000
o Consumed 80% of the purchased materials
• Direct labor: $8,493
• Overhead costs: $3,765
Note: Assume that the beginning materials and ending work in process are zero for the month.
Use the following revenue and cost information for the income statement. Note that the revenue you
use will depend on the pricing level options you chose in Milestone Two. Also, assume that after
accounting for weekends and other holidays, there were 20 business days in the first month of
operation. For example, if you chose a sales price of $20 per collar, the actual number of collars sold in
the month was 33 per day or 33 x 20 = 660 per month.
Established Sales Price
Collars
$20
$24
$28
Leashes
$22
$26
$30
Harnesses
$25
$30
$35
Number of Items Sold per Day
33
28
23
28
23
18
25
22
20
The other costs incurred by the business include:
•
•
•
General and administrative salaries
o Receptionist: $1,950
Office supplies: $200
Other business equipment: $150
1
Variance
At the end of the month, you find that the labor and materials spent on manufacturing collars was
different from what you estimated:
•
•
•
•
The collar maker had to work nine hours a day instead of eight due to an increased demand for
collars.
Because of the increased demand, the hourly rate you paid your employee for making the
collars increased to $16.50.
An increase in the cost of raw material led the direct material cost per collar to increase to $10.
However, you also made and sold 60 more collars than you expected to sell in the month.
You now need to determine the variance in the materials and labor cost from what you estimated in
Milestone Two based on the market research data.
2