Budgeting Project

profilebkdlce
student_excel_worksheet.2013.xlsx
Home>Business & Finance homework help>Accounting homework help>Budgeting Project

General Information

Fanciful, Inc
Historical Information
Sales in Gallons
Year Super Stupendous
2008 411,000 None
2009 412,000 None
2010 405,000 186,250
2011 430,000 223,500
2012 420,000 268,200
Projected Selling Price for 2013 per gallon
Super Stupendous
$10.30 $15.45
Inventory and Material Information
Beginning Inventory Desired Ending Inventory
Cans 56,550 cans 61,700 cans
Pigment
Super 63,300 lbs. 70,000
Stp'dous 49,700 53,750 lbs.
Finished Good Inv.
Super 31,700 gal. 35,000 gal.
Stp'dous 24,850 gal. 27,000 gal.
2012 Prices
Cans $0.40
Super pigment $2.75 per pound
Stupendous pigment $3.75 per pound
Expected 2013 Prices
Cans $0.02 increase over 2012 prices
Super pigment $0.14 increase over 2012 prices
Stupendous pigment $0.19 increase over 2012 prices
Usage Standards
Super 2 lbs. per gallon
Stupendous 2 lbs. per gallon
Direct Labor and Machine Hour Information
2012 labor rate - both departments $8.25 per hour
Expected 2013 rate increase $0.25 Per hour rate increase over 2012
Production Standards and Information
Super Stp'dous
Machine hours/gallon 0.12 hours 0.12 hours
Labor hours per machine hr. 1.25 hours 1.25 hours
2012 machines available 26 20
Annual capacity per machine 15,000 gal. 15,000 gal.
Machine hours per machine 1,800 hours 1,800 hours
Maximum annual hours per employee 2,000 hours 2,000 hours
Employees per supervisor 8 8
Overhead Information
2012 Information
Variable Fixed
Indirect materials $0.20 per gal
Indirect labor rate-annual $50,000 per supervisor
Employee fringe benefits 20% of wages
Health benefits per employee $1,500 per employee
Utilities $0.40 per Mhr.
Maintenance $0.20 per Mhr. $10,000 annually*
Insurance $50,000 annually*
Property taxes $10,000 annually*
Supplies $5,000 annually*
Depreciation - mfg. $250,000 annually**
* These items are allocated to depts. based upon production levels in gallons.
**The 2012 allocation was $141,300 for Super, and $108,700 for Stupendous.
It is expected that the following changes will occur in 2013:
Variable Fixed
Indirect materials No Change
Indirect labor rate-annual 2.50% increase per employee
Employee fringe benefits No Change
Health benefits per employee $200 increase per employee
Utilities No Change
Maintenance $0.05 in. per Mhr. $500 annual increase*
Insurance $500 annual increase*
Property taxes 8% annual increase*
Supplies $200 annual increase*
Depreciation - mfg. 2012 equip. No Change
Depreciation - new purchases Five-year life**
* These items are allocated to depts. based upon production levels in gallons.
**The 2012 allocation was $141,300 for Super, and $108,700 for Stupendous.
2012 Depreciation Super Stupendous
$141,300 $108,700
Cash Increase Debt
Purchases for each new piece of equipment $5,000 $25,000
Selling Department Information
2012 Information
Variable Fixed
Commissions $0.35 per can
Salaries $15,000 per representative
Fringe benefits 20% commissions 20% salaries
Health benefits $1,500 per representative
Advertising $10 per 100 cans sold
Meals & entertainment $50 per week per representative
Depreciation $7,500
It is expected that the following changes will occur in 2013:
Variable Fixed
Commissions No Change
Salaries No Change
Fringe benefits No Change No Change salaries
Health benefits $200 increase per representative
Advertising $12 per 100 cans sold
Meals & entertainment $60 per week per representative
Depreciation No Change
It is expected number of sales reps. Employees during 2013 10
Administrative Department Information
2012 Information
Variable Fixed
Salaries $250,000 annual
Fringe benefits 20% wages
Health benefits $1,500 per employee
Professional fees $20,000 annually
Office supplies $0.03 per gal. sold
Telephone $0.02 per gal. sold
Depreciation $6,000 annually
It is expected that the following changes will occur in 2013:
Variable Fixed
Salaries 3% annual increase
Fringe benefits No Change
Health benefits $200 increase per employee
Professional fees $1,500 annual increase
Office supplies $0.01 inc. per gal. sold
Telephone $0.0025 inc. per gal. sold
Depreciation No Change
It is expected number of admin. employees during 2013 9
It is expected that interest rates will be: 7.50%
2012 Balance Sheet
Cash 150,000
Accounts receivable 827,000
Inventory - raw materials 383,037
Inventory - finished goods 551,835
Plant and equipment 1,775,000
Less accumulated depreciation -615,000
Total assets 3,071,872
Accounts payable 383,016
Accrued wages 76,097
Accrued other 74,083
Long-term debt 1,125,000
Common stock 400,000
Additional paid-in 495,000
Retained earnings 518,676
Total liability and equity 3,071,872

Sales Projection

Fanciful, Inc.
Sales Volume Projection
Sales in Gallons
Year Super Stupendous
2008 411,000 None
2009 412,000 None
2010 405,000 186,250
2011 430,000 223,500
2012 420,000 268,200
Super Paint Volume Projection Using Exponential Smoothing
Year Actual Sales in Gallons Weight Weighted Sales Sales Volume Projection for 2013
2008 411,000
2009 412,000
2010 405,000
2011 430,000
2012 420,000
Totals
Stupendous Paint Volume Projection Using Growth Function
Known x's Known y's New x
Year Stupendous Year
2010 186,250 2013
2011 223,500
2012 268,200
Projection of Stupendous 2013 Sales Volume

Sales Budget

Fanciful Inc.
Sales Budget
For the Year Ended December 31, 2013
Super Paint Stupendous Paint Total
Projected Sales Volume
Selling Price
Projected Sales

Production Budget

Fanciful Inc.
Production Budget
For the Year Ended December 31, 2013
Super Paint Stupendous Paint Total
Projected Sales Volume
Desired Ending Inventory
Units Needed
Beginning Inventory
Projected Production

Direct Materials Budget

Fanciful Inc.
Direct Materials Budget
For the Year Ended December 31, 2013
Super Paint Stupendous Paint Total
Cans (Units)
Projected Production-gallons xxxxxxxxxxx xxxxxxxxxxx
Desired ending inventory xxxxxxxxxxx xxxxxxxxxxx
Units needed xxxxxxxxxxx xxxxxxxxxxx
Beginning inventory (gal) xxxxxxxxxxx xxxxxxxxxxx
Purchases needed xxxxxxxxxxx xxxxxxxxxxx
Cost per unit xxxxxxxxxxx xxxxxxxxxxx $0.00
Cost of can purchases xxxxxxxxxxx xxxxxxxxxxx $0
Pigments (pounds)
Projected Production-gallons
Pounds per gallon
Pound needed for production
Desired ending inventory
Pounds needed
Beginning inventory
Purchases needed in pounds
Cost per pound $0.00 $0.00
Cost of pigments $0 $0 $0
xxxxxxxxx xxxxxxxxx $0

Direct Labor Budget

Fanciful Inc.
Direct Labor Budget
For the Year Ended December 31, 2013
Super Paint Stupendous Paint Total
Projected employees needed
Projected production
Machine hours needed per gallon
Machine hours needed
Labor hours per machine hours
Labor hours needed Please use ROUND function here
Maximum hours per employee
Projected employees needed Watch for rounding here!!
Please be sure this is a whole number
Projected labor costs Remember to round up!
Labor hours needed (from above) Suggestion - use ROUNDUP function.
Predicted labor rate $0.00 $0.00
Labor dollars needed This is the total wage figure for cash payments and accrual
Direct labor fringe benefits This is an "other expense"
Direct labor health benefit This is an "other expense"
Total direct labor costs $0 $0 $0

Mfg Overhead Budget

Fanciful Inc.
Manufacturing Overhead Budget
For the Year Ended December 31, 2013
Super Paint Stupendous Paint Total
Number of supervisors
Direct labor employees needed
Direct labor employees/supervisor
Supervisors needed Watch for whole number here!
Use round up function
Manufacturing Overhead
Variable overhead
Indirect materials $0 $0 $0
Utilities
Variable maintenance
Total variable overhead
Fixed Overhead
Supervisor salaries
Supervisor fringe benefits
Supervisor health insurance
Fixed maintenance
Insurance
Property taxes
Supplies
Depreciation - manufacturing Remember to depreciation old and new equipment
Total fixed overhead
Total manufacturing overhead $0 $0 $0

Capital Expenditures Budget

Fanciful Inc.
Capital Expenditures Budget
For the Year Ended December 31, 2013
Super Paint Stupendous Paint Total
Machine hours needed
Machine hours per machine
Number of machines needed* Remember to round up to a whole machine!!
Machines Jan 1, 2013
Machine purchases needed ($30,000 per machine)
Cost per machine $0 $0 $0
Total cost of desired purchases $0 $0 $0
Cash outlay for purchases $0 $0 $0
Increase in debt for purchases $0 $0 $0
* Remember to round up! For example:
If your calculation determines that you will need 30.1 machines, you will have to purchase 31 machines.

Cost of Goods Mfg

Fanciful Inc.
Budgeted Cost of Goods Manufactured
For the Year Ended December 31, 2013
Direct Materials
Beginning Direct Materials Inventory Remember to look at the Beginning Balance Sheet
Material Purchases
Direct Materials Available for Use
Ending Direct Materials Inventory
Total Raw Materials Used
Direct Labor
Overhead
Cost of Goods Manufactured
Unit costs for products
Super Stupendous Total
Cost of materials per unit $0.00 $0.00 Remember the cost of the cans
Unit cost for direct labor
Unit Cost for overhead
Total unit cost for 2013 production
Units in finished goods inventory
Value of finished goods inventory $0 $0 $0 Use this figure on the ending balance sheet

Sales Department Budget

Fanciful Inc.
Selling Department Budget
For the Year Ended December 31, 2013
Fixed Variable Total
Commissions $0 $0
Salaries
Selling fringe benefits
Selling health benefits
Advertising
Meals & Entertainment
Depreciation
Totals $0 $0 $0

Administrative Depart. Budget

Fanciful Inc.
Administrative Budget
For the Year Ended December 31, 2013
Fixed Variable Total
Salaries $0 $0
Administrative fringe benefits
Administrative health benefits
Professional fees
Office supplies $0
Telephone
Depreciation
Total administative costs $0 $0 $0

Proforma Income Statement

Fanciful Inc.
Budgeted Income Statement (Absorption)
For the Year Ended December 31, 2013
Sales $0
Cost of Sales
Beginning finished goods inventory $0 Remember to look at the beginning balance sheet
Cost of goods manufactured
Good available for sale
Ending inventory Remember to look at COGMfg statement
Cost of goods sold
Gross margin 0
Selling expenses
Administrative expenses
Total selling and admin. expenses
Operating income
Interest expense
Income before tax
Income tax (40% rate)
Net income $0

Cash Budget

Fanciful Inc.
Cash Budget
For the Year Ended December 31, 2013
Increase in Cash Decrease in Cash Total
Cash receipts $0
Cash payments for materials $0
Wages and commissions paid
Other expenses paid
Interest paid on long-term debt
Income taxes paid
Cash paid for new fixed assets
Long-term debt repayment
Dividend paid
Total increases and decreases
Prior year cash xxxxxxxxxx xxxxxxxxxx
Cash balance December 31, 2013 xxxxxxxxxx xxxxxxxxxx Use this balance on the balance sheet

Proforma Balance Sheet

Fanciful Inc.
Balance Sheet
December 31, 2013
Assets
Cash $0
Accounts receivable
Inventory - raw materials Remember to look at the COGMfg
Inventory - finished goods Remember to look at the COGMfg
Plant and equipment
Less accumulated depreciation
Total Assets $0
Liabilities
Accounts payable $0
Accrued wages Be sure NOT to include employee benefits
Accrued other Do not include non-cash expenses but remember employee benefits
Long-term debt
Total liabilities $0
Stockholders' equity
Common stock 0
Additional paid-in capital
Retained earnings
Total stockholders' equity
Total liabilities and stockholders' equity $0

Strengths&Weaknesses

Strengths & Weaknesses - and Recommendations
What strengths does Fanciful, Inc. have?
What weaknesses does Fanciful, Inc. have?
What are your recommendations?