Introduction
YA3213
I SEE THE LIGHT
Yuksenin, when you are ready to have your work graded you will upload this file YA3213.xls or YA3213.xlsx
to the same screen that the project was downloaded from:
L830 160825
www.cybertext.com, The Book List, Building Blocks of Accounting--A Managerial Perspective, Enter password, Upload Your Excel File.
Keep two copies of your spreadsheet in two separate places in case one of Big Al's competitors sends someone to destroy your work or it is lost in transmission.
You may find it easier to work on this project if you print a hard copy of all the pages.
There are alternative methods of solving problems. To insure similar answers and to guarantee that you are graded co
ectly please follow the instructions as to rounding.
NOTE:
If there are any questions about the project e-mail
[email protected] or call 305.284.6296.
Grade will be based upon answers entered into the shaded boxes.
FAQ
Elf Village Productions 50 Sheet Legal Pad
Building Blocks of Accounting .. A Financial Perspective
FAQ Page 1
FAQ 01 My file used to upload, why is it not uploading?
Answer: Sometimes we unknowingly add items to a workbook that inhibits the upload process. The conversion to a Excel Binary
Workbook (*.xlsb) version and then back to the cu
ent version generally fixes the situation. If the problem continues
simple send your BINARY file as an attachment with your username and password…
[email protected] Windows Operating System
Select File
Select Save As
Select Save As Type:
Select Excel Binary Workbook (*.xlsb)
Select Save
Select Continue or Yes if prompted or if it indicates that there is limited memory.
Select File Close
Select File Open and open the .xlsb file
Select Save As
Select Save As Type:
Select Excel Workbook (*.xlsx)
Select Save
Upload the file at cybertext.com
Apple Operating System
Select File
Select Save As
Select Format:
Select Excel Binary Workbook (*.xlsb)
Select Save
Select File Close
Select File Open and open the .xlsb file
Select Save As
Select Format:
Select Excel Workbook (*.xlsx)
Select Save
Upload the file at cybertext.com
Elf Village Productions 50 Sheet Legal Pad
Building Blocks of Accounting .. A Financial Perspective
FAQ Page 2
FAQ 02 What is the difference between rounding a number and rounding up a number?
Answer: B C D E F
1 Cost of a Taxi $ 100.00
2 Number of Passengers 3
3
4 Cost per Passenger
5 Without rounding 33.3333333333 =F1/F2
6 Rounding to two decimals 33.33 =ROUND(F1/F2,2)
7 Roundup to two decimals 33.34 =ROUNDUP(F1/F2,2)
FAQ 03 When I upload it, the results show that I have an answer wrong, yet that answer is needed for another
question which is marked co
ect.
Answer: The computer is giving you part credit.
Given: Width 10 ft
Length 12 ft
Cost per sq. ft. $6
Find {1.1} Area ----- 100 sq ft (wrong)
{1.2} Cost ---- $600 co
ect based on the wrong area.
Note if the area is co
ected,120 sq ft, the cost would be wrong.
1
FIRST LAST number File
Yuksenin Alba 3213 YA3213
I SEE THE LIGHT
Background Information
I SEE THE LIGHT (ISTL) is a subchapter S corporation that manufactures children's lamps/nightlights for use in bedrooms. These lamps are sold nationwide through a group of independent sales representatives who have an exclusive sales region. The business is in its tenth year and has asked you to assist in planning for next year's operations.
The lamps are ceramic figurines of animals, boats, boys and girls playing and singing, all in delightful colors. The owner of the business, Big Al, creates a drawing for the figurine and faxes it to a plant in China where a mold is created and a sample produced and hand painted. If the mold meets the expectations of Big Al an order of 500 pieces is placed. There are presently 10 different figurines that come in six different colors; 60 models.
The lamp shades and the electrical parts are supplied from domestic manufacturers. There are presently 10 workers in the plant. They are responsible for receiving the raw material, manufacturing the product, packing and shipping. In addition to Big Al there are two office workers who are responsible for all administrative duties.
Big Al had his accountant prepare the Projected Income Statement and Balance Sheet presented on page two. Big Al heard about your skills in managerial accounting and would like your assistance in the following areas:
Part 1 Fixed and Variable Cost Determinations - Unit Cost Calculations
Part 2 Cost Volume Relationships - Profit Planning
Part 3 Budgets
Part 4 Process Costing
Part 5 Job Order Costing
Part 6 Standard Costing - Variance Analysis
Part 7 Capital Decision Making
To upload your work to Big Al the file without changing the name. Pay attention to the specific location that Excel saves the file. Return to the bottom of the page that you downloaded the file from; Cybertext.com, The Book List, Building Blocks of Accounting--A Managerial Perspective, Enter password, Upload Your Excel File. If you upload an old version of the file the results will not update.
Keep two copies of your spreadsheet in two separate places in case one of Big Al's competitors sends someone to destroy your work or it is lost in transmission.
You may find it easier to work on this project if you print a hard copy of all the pages.
NOTE:
If there are any questions about the project e-mail
[email protected] or call 305.284.6296.
Grade will be based upon answers entered into the shaded boxes.
 2Â
I See The Light
Projected Income Statement
For the Period Ending December 31, 20x1
Sales 25,000 lamps @ $45.00 $ 1,125,000.00
Cost of Goods Sold @ $28.93 723,250.00
Gross Profit $ 401,750.00
Selling Expenses:
Fixed $ 23,000.00
Variable (Commission per unit) @ $3.15 78,750.00 $ 101,750.00
Administrative Expenses 40,250.00
Total Selling and Administrative Expenses: 142,000.00
Net Profit $ 259,750.00
I See The Light
Projected Balance Sheet
As of December 31, 20x1
Cu
ent Assets
Cash $ 34,710.00
Accounts Receivable 67,500.00
Inventory
Raw Material
Figurines 500 @ $9.20 4,600.00
Electrical Sets 500 @ $1.25 625.00
Work in Process 0 - 0
Finished Goods 3000 @ $28.9250 86,775.00
Total Cu
ent Assets $ 194,210.00
Fixed Assets
Equipment $ 20,000.00
Accumulated Depreciation 6,800.00
Total Fixed Assets 13,200.00
Total Assets $ 207,410.00
Cu
ent Liabilities
Accounts Payable $ 54,000.00
Total Liabilities $ 54,000.00
Stockholder's Equity
Common Stock $ 12,000.00
Retained Earnings 141,410.00
Total Stockholder's Equity 153,410.00
Total Liabilities and Stockholder's Equity $ 207,410.00
Page 2
3
Yuksenin Alba
3589
PART 1
Fixed and Variable Cost Determinations
Unit Cost Calculations
The projected cost of a lamp is calculated based upon the projected increases or decreases to
cu
ent costs. The present costs to manufacture one lamp are:
Figurines $9.2000000 per lamp
Electrical Sets 1.2500000 per lamp
Lamp Shade 6.0000000 per lamp
Direct Labor: 2.2500000 per lamp (4 lamps/hr.)
Variable Overhead: 0.2250000 per lamp
Fixed Overhead: 10.0000000 per lamp (based on normal capacity of 25,000 lamps)
Cost per lamp: $28.9250000 per lamp
Expected increases for 20x2
When calculating projected increases round to SEVEN decimal places,$0.0000000.
1. Material Costs are expected to increase by 6.00% .
2. Labor Costs are expected to increase by 3.50%.
3. Variable Overhead is expected to increase by 6.50%.
4. Fixed Overhead is expected to increase to $300,000.
5. Fixed selling expenses are expected to be $25,000 in 20x2.
6. Variable selling expenses (measured on a per lamp basis) are expected to increase
by 4.00%.
7. Fixed Administrative expenses are expected to increase by $12,000.
The total administrative expenses for 20x0 were $40,005.00, when
21,500 units were sold. Use the High-Low method to calculate
the total fixed administrative expense.
8. Variable administrative expenses (measured on a per lamp basis) are expected to
increase by 6.00%. The total administrative expenses for 20x0 were
$40,005.00, when 21,500 units were sold. Use the High-Low method to calculate
the variable administrative expense per lamp.
On the following schedule develop the following figures:
1- 20x2 Projected Variable Manufacturing Unit Cost of a lamp.
2- 20x2 Projected Variable Unit Cost per lamp.
3- 20x2 Projected Fixed Costs.
Page 3
4
Yuksenin Alba
3589
I See The Light, Inc
Schedule of Projected Costs
Variable Manufacturing Unit Cost
20x1 Cost Projected Percent Increase 20x2 Cost Rounded to 7 Decimal Places
Figurines 9.2 6.00% $9.7520000 {4.01} 7
Electrical Sets 1.25 6.00% $1.3250000 {4.02} 7
Lamp Shade 6 6.00% $6.3600000 {4.03} 7
Labor 2.25 3.50% $2.3287500 {4.04} 7
Variable Overhead 0.225 6.50% $0.2396250 {4.05} 7
Projected Variable Manufacturing Cost Per Unit 18.925 $20.0053750 {4.06} 7
Total Variable Cost Per Unit
20x1 Cost Projected Percent Increase 20x2 Cost Rounded to 7 Decimal Places
Variable Selling 3.15 4.00% 3.2760000 {4.07} 7
Variable Administrative 20x1 0.0700000 {4.08} 7
Variable Administrative 20x2 6.00% 0.0742000 {4.09} 7
Projected Variable Manufacturing Unit Cost 18.925 $20.0053750 {4.06}
Projected Total Variable Cost Per Unit 22.075 23.3555750 {4.10} 7
Schedule of Fixed Costs
20x1 Cost Projected Increase 20x2 Cost Rounded to 2 Decimal Places
Fixed Overhead $ 300,000.00 {4.11} 2
(normal capacity of _________ lamps @ __ )
Fixed Selling $ 25,000.00 {4.12} 2
Fixed Administrative 20x1 38,500.00 {4.13} 7
Fixed Administrative 20x2 12000 $ 50,500.00 {4.14} 2
Projected Total Fixed Costs $ 375,500.00 {4.15} 2
Page 4
 5
Yuksenin Alba
3589
PART 2
Cost Volume Relationships -
Profit Planning
Big Al is about to begin work on the budget for 20x2 and they have requested that you prepare an analysis
based on the following assumptions.
Note: Remember, that we cannot sell part of a lamp, therefore to find the number of units you have to round
up to the next complete unit. Furthuremore, to find the required sales in dollars it may be easier to find the
number of units and then multiply by the selling price per unit.
1. For 20x2 the selling price per lamp will be $45.00. What is the projected contribution margin and contribution
margin ratio for each lamp sold?
Sales price per unit 45
less: total variable cost per unit 23.355575
Contribution Margin per unit (Round to seven places, $##.#######) $21.6444250 {5.01} 7
Contribution Margin Ratio (Round to seven places,% is two of those places ##.#####%) 48.09872% {5.02} 7
2. For 20x2 the selling price per lamp will be $45.00. How many lamps must be sold to
eakeven?
Total fixed cost $ 375,500.00
divide: contribution margin ratio 48.09872%
BE sales amount $ 780,686.06
divide: sales price/ unit 45
Breakeven sales in units (Round up to zero places, ###,### units) 17,349 units {5.03} 0
3. For 20x2 the selling price per lamp will be $45.00. The desired operating income in 20x2 is $264,750 . What
would sales in units have to be in 20x2 to reach the profit goal?
Total fixed cost $ 375,500.00
add: operating income 264750
total fixed cost and operating income $ 640,250.00
divide: contribution margin 48.09872%
BE sales amount $ 1,331,116.50
divide: sales price/ unit 45
Sales in units (Round up to zero places, ###,### units) 29,581 units {5.04} 0
Page 5
 6
Yuksenin Alba
3589
4. For 20x2 the selling price per lamp will be $45.00. The company would like to have a operating income equal
to 31.00% of sales. If that is to be achieved, what would be the sales in units in 20x2?
Sales price/ unit 45
desired profit range 31.00%
desired profit/ unit 13.95
variable cost/ unit 23.355575
total fixed cost $ 375,500.00
Sales in units (Round up to zero places, ###,### units) 48,802 units {6.01} 0
5. If the company believed that it could only sell 25,000 lamps, what would the new selling price have to be
so that the new contribution margin per unit is equal to last year's contribution margin per unit?
Contribution margin/ unit of last year 23.355575
total contribution margin 583889.375
total fixed cost $ 375,500.00
total units 25000
New Selling Price (Round up to two places, $###,###.## ) $ 38.38 {6.02} 2
6. For 20x2 the selling price per lamp will be $45.00 and the effective tax rate is 41%. How many units must be
sold to generated a operating income of $210,000 after taxes?
total fixed cost $ 375,500.00
add: desired operating income before taxes 355932.20338983
total fixed cost and operating income $ 731,432.20
divid: contribution margin 0.4809872
BE sales amont $ 1,520,689.54
divide: sales price/ unit 45
Sales in units (Round up to zero places, ###,### units) 33,793 units {6.03} 0
7. If the company believes that the demand will be 27,500 units for the year. What selling price
per lamp, rounded to two places, would generate a operating income of $827,500?
total fixed cost $ 375,500.00
add: desired operating income before taxes
add: desired operating income before taxes 827500
add: total variable cost 642278.3125
total sales amount $ 1,845,278.31
divide: total sales units $ 27,500.00
New selling price per lamp (Round up to two places, $###,###.## ) $ 67.10 {6.04} 2
Page 6
7
Yuksenin Alba
3589
PART 3
Budgets
Keep in mind that the budget section builds on work from the previous parts, including Part I as well as the Background Information (tabs 1-4). You should continue to use the same file with your previously submitted answers.
Division N has decided to develop its budget based upon projected sales of 35,000 lamps at
$50.00 per lamp.
The company has requested that you prepare a master budget for the year. This budget is to be used
for planning and control of operations and should be composed of:
1. Production Budget
2. Materials Budget
3. Direct Labor Budget
4. Factory Overhead Budget
5. Selling and Administrative Budget
6. Cost of Goods Sold Budget
7. Budgeted Income Statement
8. Cash Budget
Notes for Budgeting:
The company wants to maintain the same number of units in the beginning and ending inventories of
work-in-process, and electrical parts while increasing the figurines inventory to 550 pieces and
increasing the finished goods by 27.00% .
Complete the following budgets
1 Production Budget
Planned Sales 35000
Desired Ending Inventory of Finished Goods (roundup to the next unit) 3810
Total Needed 38810
Less: Beginning Inventory 3000
Total Production 35,810 units {7.01} 0
Page 7
 8
Yuksenin Alba
3589
2 Materials Budget
Figurines
Needed for Production 35,810 units {8.01} 0
Desired Ending Inventory 550 units {8.02} 0
Total Needed 36,360 units
Less: Beginning Inventory 500 units {8.03} 0
Total Purchases 35,860 units {8.04} 0
Cost per piece 9.752
Cost of Purchases (Round to two places, $##.##) $ 349,706.72 {8.05} 2
Electrical Parts
Needed for Production 35810
Desired Ending Inventory 500
Total Needed 36,310 units {8.06} 0
Less: Beginning Inventory 500
Total Purchases 35810
Cost per piece 1.325
Cost of Purchases (Round to two places, $##.##) $ 47,448.25 {8.07} 2
Lamp Shades - not inventoried they a
ive from the shop next door Just-in-time.
Needed for Production 35810
Desired Ending Inventory 0
Total Needed 35810
Less: Beginning Inventory 0
Total Purchases 35810
Cost per piece 6.36
Cost of Purchases (Round to two places, $##.##) $ 227,751.60 {8.08} 2
3 Direct Labor Budget
Labor Cost Per Lamp 2.32875
Production 35810
Total Labor Cost (Round to two places, $##.##) $ 83,392.54 {8.09} 2
4 Factory Overhead Budget
Variable Factory Overhead:
Variable Factory Overhead Cost Per Unit 0.239625
Number of Units to be Produced 35810
Total Variable Factory Overhead (Round to two places, $##.##) $ 8,580.97 {8.10} 2
Fixed Factory Overhead 300000
Total Factory Overhead (Round to two places, $##.##) $ 308,580.97 {8.11} 2
Predetermined Factory Overhead Rate based upon the budgeted
total factory OH, divided by the budgeted number of units to be produced, and then rounded to seven places, $##.#######) $ 8.61717310 {8.12} 7
Page 8
9
Yuksenin Alba
3589
5 Selling and Admin. Budget
Fixed Selling 25000
Variable Selling (Round to two places, $##.##) 114660
Fixed Administrative 50500
Variable Administrative (Round to two places, $##.##) 2597
Total Selling and Administrative (Round to two places, $##.##) $192,757.00 {9.01} 2
6
Cost of making one unit next year Round dollars to seven places, $##.#######
Material cost per unit 17.437
Labor Cost Per Lamp 2.32875
Factory overhead per unit 8.6171731
Total cost of one unit $ 28.38292310 {9.02} 7
(Round to seven places, $##.#######)
Round dollars to two places, $##.##
Beginning Inventory, Finished Goods $ 86,775.00 {9.03} 2
Production Costs:
Materials:
Figurines:
Beginning Inventory 4600
Purchased 349706.72
Available for Use 354306.72
Ending Inventory of Figurines 5363.6
Figurines Used In Production $ 348,943.12 {9.04} 2
Electrical Parts
Beginning Inventory 625
Purchased 47448.25
Available for Use 48073.25
Ending Inventory of Electrical Parts 662.5
Electrical Parts Used In Production $ 47,410.75 {9.05} 2
Lamp Shades:
Lamp Shades Used In Production $ 227,751.60 {9.06} 2
Total Materials: $ 624,105.47 {9.07} 2
Labor $ 83,392.54 {9.08} 2
Overhead $ 308,580.97 {9.09} 2
Cost of Goods Available $ 1,102,853.98 {9.10} 2
Less: Ending Inventory, Finished Goods $ 108,138.94 {9.11} 2
Cost of Goods Sold $ 994,715.04 {9.12} 2
Page 9
Cost of Goods Sold Budget - Assume FIFO (First-In, First-Out) and overhead is applied based on the number of units to be produced.
10
Yuksenin Alba
3589
7 Budgeted Income Statement
Sales 35000 50 1750000
Cost of Goods Sold $ 994,715.04
Gross Profit $ 755,284.96
Selling Expenses & Admin. Expenses $192,757.00
Net Operating Income $ 562,527.96 {10.01} 2
8 Cash Budget
Assume actual cash receipts and disbursements will follow the pattern below: (Note: Receivables and
Payables of 12/31/x1 will have a cash impact in 20x2.)
1. 17.00% of sales for the year are made in November and December. Since our customers have 60 day terms
those funds will be collected be collected in January and Fe
uary.
2. 88.00% of material purchases will be paid during the year, the remaining portion will be paid in Januay or...