Introduction
ZH9755
I SEE THE LIGHT
Zachary, when you are ready to have your work graded you will upload this file ZH9755.xls, or ZH9755.xlsx,
to the same screen that the project was downloaded from:
F715 150126
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 XXXXXXXXXX or call XXXXXXXXXX.
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 unknowing add items to a workbook that inhibits the upload process. The conversion to an earlier version
Somehow clean the files and they then upload without problem. If the problem continue simple send your file as an
attachment with your username and password… XXXXXXXXXX
Windows Operating System
Select File
Select Save As
Select Save As Type:
Select Microsoft 5.0/95 Workbook (*.xls)
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 .xls 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 Microsoft 5.0/95 Workbook (*.xls)
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 .xls 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 $ XXXXXXXXXX
2 Number of Passengers 3
3
4 Cost per Passenger
5 Without rounding XXXXXXXXXX =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 XXXXXXXXXXsq 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
Zachary Henry 9755 ZH9755
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 lamp parts is placed. Each lamp kit consists of the parts required to complete one lamp; a figurine, a lamp shade and the required electrical components. There are presently 10 different figurines that come in six different colors; 60 models.
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 XXXXXXXXXXFixed and Variable Cost Determinations - Unit Cost Calculations
Part XXXXXXXXXXCost Volume Relationships - Profit Planning
Part XXXXXXXXXXBudgets
Part XXXXXXXXXXProcess Costing
Part XXXXXXXXXXJob Order Costing
Part XXXXXXXXXXStandard Costing - Variance Analysis
Part XXXXXXXXXXCapital 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 XXXXXXXXXX or call XXXXXXXXXX.
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 @ $30.00 750,000.00
Gross Profit $ 375,000.00
Selling Expenses:
Fixed $ 23,000.00
Variable (Commission per unit) @ $3.00 75,000.00 $ 98,000.00
Administrative Expenses:
Fixed $ 42,000.00
Variable @ $2.00 50,000.00 92,000.00
Total Selling and Administrative Expenses: 190,000.00
Net Profit $ 185,000.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
Lamp Kits 500 @ $16.00 8,000.00
Work in Process 0 - 0
Finished Goods 3000 @ $30.00 90,000.00
Total Cu
ent Assets $ 200,210.00
Fixed Assets
Equipment $ 20,000.00
Accumulated Depreciation 6,800.00
Total Fixed Assets 13,200.00
Total Assets $ 213,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 147,410.00
Total Stockholder's Equity 159,410.00
Total Liabilities and Stockholder's Equity $ 213,410.00
Page 2
3
Zachary Henry
10028
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:
Lamp Kit: $ XXXXXXXXXX per lamp
Electrical Sets - 0 per lamp
Lamp Shade - 0 per lamp
Direct Labor: XXXXXXXXXX per lamp (4 lamps/hr.)
Variable Overhead: XXXXXXXXXX per lamp
Fixed Overhead: XXXXXXXXXX per lamp (based on normal capacity of 25,000 lamps)
Cost per lamp: $ XXXXXXXXXX per lamp
Expected increases for 20x2
When calculating projected increases round to TWO ($0.00) decimal places.
1. Material Costs are expected to increase by 2.50% .
2. Labor Costs are expected to increase by 2.50%.
3. Variable Overhead is expected to increase by 3.00%.
4. Fixed Overhead is expected to increase to $275,000.
5. Fixed Administrative expenses are expected to increase to $44,000.
6. Variable selling expenses (measured on a per lamp basis) are expected to increase
by 3.00%.
7. Fixed selling expenses are expected to be $25,000 in 20x2.
8. Variable administrative expenses (measured a per lamp basis) are expected to
increase by 2.00%.
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Â
Zachary Henry
10028
I See The Light, Inc
Schedule of Projected Costs
Variable Manufacturing Unit Cost
20x1 Cost Projected Percent Increase 20x2 Cost Rounded to 2 Decimal Places
Lamp Kit {4.01} 2
Labor {4.02} 2
Variable Overhead {4.03} 2
Projected Variable Manufacturing Cost Per Unit {4.04} 2
Total Variable Cost Per Unit
20x1 Cost Projected Percent Increase 20x2 Cost Rounded to 2 Decimal Places
Variable Selling {4.05} 2
Variable Administrative {4.06} 2
Projected Variable Manufacturing Unit Cost {4.04} 2
Projected Total Variable Cost Per Unit {4.07} 2
Schedule of Fixed Costs
20x1 Cost Projected Percent Increase 20x2 Cost
Fixed Overhead {4.08} 2
(normal capacity of _________ lamps @ __ )
Fixed Selling {4.09} 2
Fixed Administrative {4.10} 2
Projected Total Fixed Costs {4.11} 2
Page 4
 5Â
Zachary Henry
10028
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 $ XXXXXXXXXXWhat is the projected contribution margin and contribution
margin ratio for each lamp sold?
Contribution Margin per unit (Round to two places, $##.##) {5.01} 2
Contribution Margin Ratio (Round to four places,% is two of those places ##.##%) {5.02} 4
2. For 20x2 the selling price per lamp will be $ XXXXXXXXXXThe desired net income in 20x2 is $192,500 . What
would sales in units have to be in 20x2 to reach the profit goal?
Breakeven sales in units (Since we cannot sell part of a unit round up to the next unit if needed) {5.03} 0
3. For 20x2 the selling price per lamp will be $ XXXXXXXXXXIf the fixed cost increase by $35,000