To edit the graded assignment

profileesurmen
MAC7200CasestudyWeek5RM-8.xlsx
Home>Business & Finance homework help>Accounting homework help>To edit the graded assignment

Week 5

Particulars 2,016 2017 (August YTD 2017 (Full Year) 2,018
Volume 615,000 480,000 664,721 697,957
Sales 7,687,500 6,000,000 8,309,008 8,724,458
Cost of Goods Sold-Raw Materials 3,075,000 2,400,000 3,323,603 3,489,783
Gross Margin 4,612,500 3,600,000 4,985,405 5,234,675
Expense Category
Salaries and Wages 775,000 485,667 728,500 750,355
Fringe Benefits 372,000 242,833 364,250 375,178
Travel & Entertainment 40,000 28,000 42,000 44,100
Outside Contractors 145,000 100,000 150,000 206,000
Manufacturing Supplies 250,000 160,000 221,574 229,139
Parts and Tools 60,000 40,000 55,393 57,288
Utilities 300,000 220,000 304,664 315,075
Depreciation 150,000 110,000 167,167 171,500
Warehousing Costs 80,000 50,000 69,242 71,636
Total Operating Expenses 2,172,000 1,436,500 2,102,790 2,220,271 Amount to separate for fixed & variable costs
Sales and Advertising Exp 450,000 300,000 415,450 436,223 ignored in week 5
Interest expense 14,000 12,000 ignored in week 5
Total Expenses 2,622,000 1,736,500 2,532,240 2,668,494
Income from Operations 1,990,500 1,863,500 2,453,165 2,566,182
1) Using your annual budget data from Week 1 and the data provided, calculate the fixed and variable costs for the 3 production departments.
Part 1 Fixed and Variable costs calculations
Variable Costs Total Mixing Extruding Assembly
Fringe Benefits 375,178 75,036 112,553 187,589
Travel And Entertainment 44,100 8,820 13,230 22,050
Outside Contractor 206,000 103,000 103,000 - 0
Manufacturing Supplies 229,139 76,372 76,372 76,395
Parts And Tools 57,288 - 0 - 0 57,288
Utilities 315,075 126,030 126,030 63,015
Total 1,226,780 389,258 431,185 406,337
Fixed Costs
Salaries And Wages 750,355 150,071 225,107 375,178
Depreciation 171,500 34,300 51,450 85,750
Warehousing 71,636 28,654 28,654 14,327
Total 993,491 213,025 305,211 475,255
Total Mixing Extruding Assembly
Variable Costs 1,226,780 389,258 431,185 406,337
Fixed Costs 993,491 213,025 305,211 475,255
Total Costs 2,220,271 602,283 736,396 881,592
2) Using your annual budget data from Week 3 and the data provided, calculate the standard costs of the 2 product lines produced in your factory.
2 methods accepted
Method 1 -Allocation of both Fixed and variable costs by type of product produced
Rayz Beamz Total
Budgeted Volume 418,774 279,183 697,957
Batches (per 1,000) 419 279 698
Rayz Beamz Total
Total Hours in Mixing machinery 1,256 838 2,094 Machine hours differs slightly over expected hours of 2,000 (40*50) due to rounding from the number of batches
Total Hours in Extruding machinery 2,094 2,233 4,327 Note different hours used in Rayz vs Beamz. Machine hours differs slightly over expected hours of 4,000 (2 machines *40*50)due to rounding from the number of batches
Total employee hours in Assembly 5,025 5,863 10,888 Note different hours used in Rayz vs Beamsz.Employee hours differs slightly over expected hours of 10,000 (5 employees 840*50)due to rounding from the number of batches
8,374 8,932 17,307
Product Costing
Rayz Glasses Beamz Glasses
Total Per Unit Total Per Unit Standard Material Costs
Standard Material Costs unit 2,051,994 4.90 1,367,996 4.90 Requires for 1 pair:
Mixing 361,370 0.86 240,913 0.86 Lens 2 $1 per lens 2.00
Extruding 356,321 0.85 380,076 1.36 Screws 4 $.10 per screw 0.40
Assembly 406,888 0.97 474,703 1.70 Plymer 0.5 $5 per gallon 2.50
3,176,573 7.59 2,463,688 8.82 4.90
Method 2 -Allocation of variable costs by Dept & Salaries & Wages This method does not differeniate between hours needed in Extruding & Assembly for Beamz glasses versus Rayz
Standard Costs Calculations Production of Sunglasses
Standard Costs to make products Mixing Extruding Assembly Each batch of sunglasses makes 1000 pairs of sunglasses.
One batch of Rayz glasses takes 3 hours in the Mixing Department, 5 hours in the Extruding Department, and 12 hours in Assembly
Warehousing 28,654 28,654 14,327 One batch of Beamz glasses takes 3 hours in the Mixing Department, 8 hours in the Extruding Department, and 21 hours in Assembly
Utilities 126,030 126,030 63,015
Parts & Tools 0 0 57,288
Salaries and Wages 150,071 225,107 375,178
Fringe Benefits 75,036 112,553 187,589
Outside Contractor 103,000 103,000 0
Manufacturing Supplies 76,372 76,372 76,395
Travel And Entertainment 8,820 13,230 22,050
Depreciation - fixed cost
Total 567,983 684,946 795,842
Standard costs per batch
Mixing Line 568
Extruding line 685
Assembly line 796
3) Using the budgeted sales data, calculate the standard gross margin for each of the 2 products - both per unit and total gross margin with budgeted volume.
2 methods accepted
Method 1 -Allocation of both Fixed and variable costs by type of product produced
Rayz Beamz Total
Budgeted volume: 418,774 279,183 697,957
Gross margin per unit
Sales $ 11.50 $ 14.00
Cost of Goods Sold:
Direct Materials $ 5.00 $ 5.00 Problem asks for budgeted costs from week 3
Production Costs
Mixing Costs $ 0.86 $ 0.86
Extruding Costs $ 0.85 $ 1.36 Note: for student who used dept allocation method of costs
Assembly Costs $ 0.97 $ 1.70 see the differences in per unit cost by dept
Cost of Goods Sold: $ 7.69 $ 8.92
Gross Profit $ 3.81 $ 5.08
Gross profit margin 33.17% 36.25%
Method 2 -Allocation of variable costs by Dept & Salaries & Wages This method does not differeniate between hours needed in Extruding & Assembly for Beamz glasses versus Rayz
Per Unit Contribution: Rayz Beamz Total
Sales 11.50 14.00
Material,Variable & salary costs 7.83 7.83 Same costs when Beamz takes more Extruding & Assembly hours
Contribution Margin 3.67 6.17
checks to budget income statement
Total Sales 4,815,903 3,908,559 8,724,463 5 Rounding to sales above due to product breakout
Direct Materials 2,093,871 1,395,914 3,489,785 -2 Rounding to sales above due to product breakout
Variable & salary costs 1,186,281 790,854 1,977,135
Contribution Margin 1,535,751 1,721,791 3,257,543
Fixed Costs 145,882 97,254 243,136
Operating Profit 1,389,870 1,624,537 3,014,407 -2 Rounding to sales above due to product breakout
1
4. Product line to advertise to make additional sales - narrative
Based on the contribution & profit margins listed above, the company would be better off to pursue a
higher advertising campaign and production of the Beamz glasses. The Beamz do take longer to produce
but it produces a higher profit margin & slighly higher contribution margin.

Week 3 Operating statement

Data for Operating Budget
2016 Full Year 2017 August YTD 2017 full year 2018 Full year
Sales 7,687,500 6,000,000 8,309,008 8,724,458 Increase of 5% in sales from 2017. 2017 full year sales calculated above times 1.05
Purchased Raw Materials 3,075,000 2,400,000 3,323,603 3,489,783 Increase of 5% in sales from 2017. 2017 full year sales calculated above times 1.05
Expense Category
Salaries and Wages 775,000 485,667 annualized 728,500 750,355 Increase of 3% for cost of living for 2018
Fringe Benefits 372,000 242,833 50% of salary 364,250 375,178
Travel & Entertainment 40,000 28,000 annualized 42,000 5% increase 44,100 Taking the same 5% increase in T&E that occurred from 2016 to 2017 and carrying over to 2018
Outside Contractors 145,000 100,000 annualized 150,000 3% increase 206,000 Increase of 3% for outside contractors = 154,500 / 3 = $51,500 per contractor X 4 people = 206,000
Manufacturing Supplies 250,000 160,000 volume based 221,574 229,139 if full year would have been .3333 per unit, (221,574/664,721) see savings below
Parts and Tools 60,000 40,000 volume based 55,393 57,288 if full year would have been .08333 per unit, (55,393/664,721) see savings below
Utilities 300,000
rbamo: rbamo: 300,000/615,000=.4878		 220,000
rbamo: rbamo: 220,000/480,000= .4583 		volume based 304,664 315,075 if full year would have been .4583 per unit, (304,664/664,721) see savings below
Depreciation 150,000 110,000 new mix tank 167,167
rbamo: rbamo: month Depr 110,000/8= $13750*12 months plus addition depr for mixing tank $65,000/10 years = $6500/12 times 4 months Sept-Dec 		4 month depr 171,500 New mixing tank purchased Sept 2017 - 4 months depreciation in 2017, full year in 2018
Warehousing Costs 80,000 50,000 volume based 69,242
rbamo: rbamo: Thru August 50,000/480,000 units = .104167 each
rbamo: rbamo: 300,000/615,000=.4878
rbamo: rbamo: 220,000/480,000= .4583 		71,636 if full year would have been .1042 per unit, (69,242/664,721) see savings below
Total Operating Expenses 2,172,000 1,436,500 2,102,789 2,220,272
Sales and Advertising Exp 450,000 300,000 volume based 415,450 436,223 volume based in 2017 - per unit cost .625, case study silent on method of growth or savings
Interest expense 14,000 12,000 See accepted amortization methods
Income from Operations 1,990,500 1,863,500 2,453,165 2,566,180
Volume 615,000 480,000 664,721 697,957
12.50 12.50 12.50 12.50
5 5 5 5
Notes: based on based on based on
8 months 12 months 12 months
Units sold 2018 Volume based savings
2016 2017 based on 2016 2017 annual 2018 Annual Mfr supp Parts Utilities Warehouse
Jan-March 102,705 102,705 110,897 116,442 0 0 0
Oct-Dec 102,090 - 0 110,900 116,445 1164.33 291.10 1601.00 364.01
April- Sept 410,205 341,838 442,924 465,070 2325.12 581.31 3197.12 726.90
615,000 444,543 664,721 697,957 3489.45 872.41 4798.13 1090.91
480,000
2017 % increase in sales over 2016 7.976% sales based on Sales 5% higher
7.976% volume case study
increase assumption

Loan Amort methods

Accepted Amortization methods 4 variations Mixing machine was purchased in Sept otherwise there would have been interest cost in August
$100,000 per month plus interest 4%
1 9/1/17 1,200,000.00 $ 4,000.00
2 10/1/17 1,100,000.00 $ 3,666.67
3 11/1/17 1,000,000.00 $ 3,333.33
4 12/1/17 900,000.00 $ 3,000.00 $ 14,000.00
5 1/1/18 800,000.00 $ 2,666.67
6 2/1/18 700,000.00 $ 2,333.33
7 3/3/18 600,000.00 $ 2,000.00
8 4/2/18 500,000.00 $ 1,666.67
9 5/2/18 400,000.00 $ 1,333.33
10 6/2/18 300,000.00 $ 1,000.00
11 7/2/18 200,000.00 $ 666.67
12 8/1/18 100,000.00 $ 333.33 $ 12,000.00
Fixed payment over 12 month
Loan Amount $1,200,000.00 2017
Interest Rate 4% 2018
# payments 12 4 in 2017, 8 in 2018
# of years 1
Monthly payment $102,179.89
Additional principal needed $5,312.49 $1,328.12
Payment Month Payment Principal Interest Add. Principal Principal + Interest Loan amount left
0 $1,200,000.00
1 $102,179.89 $ 98,179.89 $4,000.00 $1,328.12 $102,179.89 $1,100,491.99
2 $102,179.89 $ 98,511.58 $3,668.31 $1,328.12 $102,179.89 $1,000,652.30
3 $102,179.89 $ 98,844.38 $3,335.51 $1,328.12 $102,179.89 $900,479.80
4 $102,179.89 $ 99,178.29 $3,001.60 $1,301.51 $102,179.89 $800,000.00
5 $102,179.89 $ 99,513.22 $2,666.67 $102,179.89 $700,486.79
6 $102,179.89 $ 99,844.93 $2,334.96 $102,179.89 $600,641.86
7 $102,179.89 $ 100,177.75 $2,002.14 $102,179.89 $500,464.11
8 $102,179.89 $ 100,511.67 $1,668.21 $102,179.89 $399,952.44
9 $102,179.89 $ 100,846.71 $1,333.17 $102,179.89 $299,105.73
10 $102,179.89 $ 101,182.87 $997.02 $102,179.89 $197,922.86
11 $102,179.89 $ 101,520.14 $659.74 $102,179.89 $96,402.72
12 $96,724.06 $ 96,402.72 $321.34 $96,724.06 $0.00
$1,220,702.80 $1,194,714.13 $25,988.67
Total interest expense: 2017 $14,005.41
2018 $11,983.26
Total 2017 principal payments made on loan $ 400,000.00
Total 2018 principal payments made on loan $ 800,000.00
$ 1,200,000.00
LOAN AMORTIZATION SCHEDULE FOR 12 MONTHS OF PAYMENTS TO PAY OFF $800,000 LOAN BALANCE WITH 4% INTEREST
PRINCIPAL INTEREST CUMULATIVE PRINCIPAL CUMULATIVE INT LOAN PRIN BALANCE
1 65,453.25 2,666.67 65,453.25 2,666.67 734,546.75
2 65,671.43 2,448.49 131,124.68 5,115.16 668,875.32
3 65,890.34 2,229.58 197,015.02 7,344.74 602,984.98
4 66,109.97 2,009.95 263,124.99 9,354.69 536,875.01
5 66,330.34 1,789.58 329,455.33 11,144.27 470,544.67
6 66,551.44 1,568.48 396,006.77 12,712.75 403,993.23
7 66,773.28 1,346.64 462,780.05 14,059.39 337,219.95
8 66,995.85 1,124.07 529,775.90 15,183.46 270,224.10
9 67,219.17 900.75 596,995.07 16,084.21 203,004.93
10 67,443.24 676.68 664,438.31 16,760.89 135,561.69
11 67,668.05 451.87 732,106.36 17,212.76 67,893.64
12 67,893.61 226.31 799,999.97 17,439.07 0.03
13 *0.03 0 800,000.00 17,439.07 0
Balloon payment method
Paid 12/31/2017 1,200,000.00 4% $ 16,000.00 annual interest Sept-December
Paid 12/31/2018 800,000.00 4% $ 32,000.00 annual interest assumes 12 months