financial charts and graphs for a business plan---Financial Template

FINANCIAL WORKSHEET INTRODUCTION This workbook is designed to help prepare financial charts and graphs for a business plan. The Business Plan Guidebook for Multi-Use Facilities explains the use of these sheets and the narrative of a Business Plan. This Workbook is a sample of a financial plan for a Multi-Use Facilty. Use the file "Business Plan Financial Template.xls" to create your financial plan. OPERATIONS AND MANAGEMENT WORKSHEET Operation and Maintenance Expense Personnel Utilities Materials and Equipment Other Annual Estimate $27,595 $14,550 $1,800 $2,050 Total Annual Expenses Details $45,995 Personnel Administrative Personnel Maintenance Personnel Custodial Personnel Other Personnel Totals from Detail Below $19,714 $3,996 $3,885 $0 Utilities Electricity Fuel Oil Water Sewer Other Unit month gal month month Personnel Total $27,595 Cost per Unit $250 $3 $75 $75 $0 x Units per Year 12 3000 12 12 0 = Annual Cost $3,000 $9,750 $900 $900 $0 Utility Total $14,550 = Annual Cost $500 $350 $1,200 $0 $0 Materials and Equipment Maintenance & Repair Materials Custodial Cleaning Supplies Office Materials and Supplies Other Other - Monthly Cost $50 $50 $50 $0 $0 Annual Cost $600 $600 $600 $0 $0 Other Building Insurance Liability Insurance Other Other Other Unit Annual Annual Monthly Annual Annual Cost per Unit $500 $350 $100 $0 $0 x Units per Year 1 1 12 0 0 Materials & Equipment Total $1,800 Other ExpenseTotal $2,050 OPERATIONS AND MANAGEMENT WORKSHEET Personnel Expenses Detail Administrative Personnel Facility Manager (City Manager-Partial Monthly Salary) Bookkeeper 10 Other Position A 0 Other Position B 0 48 0 0 48 0 50 0 0 0 12 480 0 0 240 0 350 0 0 0 $1,000 $12.00 $0.00 $0.00 $15.00 $0.00 $10.00 $0.00 $0.00 $0.00 $12,000 $5,760 $0 $0 $3,600 $0 $3,500 $0 $0 $0 $960 $461 $0 $0 $288 $0 $280 $0 $0 $0 $360 $173 $0 $0 $108 $0 $105 $0 $0 $0 $13,320 $6,394 $0 $0 Hours per Week x Weeks per Year = x Annual Hourly Hours Rate = Annual Wages + + Payroll Employee Taxes Benefits 8.00% 3.00% = Annual Cost Totals $19,714 Maintenance Personnel Public Works Position B 5 0 7 0 0 0 $3,996 $0 $3,996 Custodial Personnel Janitor Position B $3,885 $0 $3,885 Other Personnel Position A Position B $0 $0 $0 Personnel Subtotal $27,595 Notes REPAIR AND REPLACEMENT (R&R) WORKSHEET Repair and Replacement Expense Equipment Boilers & Furnaces Water Heaters Teen Center Computers Teen Center Gaming Equipment Other Equipment Other Equipment - Number of Items (multiplied by) (divided by) (equals) Cost to Replace $1,200 $8,000 $1,000 $500 $0 $0 $1,000 $5,000 $1,000 Useful Life in Years Annual Estimate $240 $2,667 $250 $400 $0 $0 $800 $714 $333 1 1 2 4 0 0 4 1 1 5 3 8 5 1 1 5 7 3 Furnishings Desks Furniture Other Furnishings - Annual Repair and Replacement Total Notes $5,404 CAPITAL REPLACEMENT WORKSHEET Capital Replacement Expense Facilty Cost: Inflation Rate: Expected Life in Years: Future Value of Facility (Cost with inflation): Percent Local Cash Required for Replacement: 25% Capital Replacement Amount: Expected Interest Rate: $1,513,710 3.5% 30 $4,248,672 (formula for future value of an asset) 25% (future value multiplied by % local $1,062,168 cash required) 4.0% (capital replacement amount Annual Capital Replacement Expense $18,939 multiplied by expected interest rate) Capital Replacement Fund Year Start of Year Balance 1 $0 2 $18,939 3 $38,635 4 $59,119 5 $80,422 6 $102,577 7 $125,619 8 $149,582 9 $174,504 10 $200,423 11 $227,378 12 $255,412 13 $284,567 14 $314,888 15 $346,422 16 $379,218 17 $413,325 18 $448,797 19 $485,687 20 $524,053 21 $563,954 22 $605,451 23 $648,607 24 $693,490 25 $740,168 26 $788,714 27 $839,201 28 $891,707 29 $946,314 30 $1,003,105 31 $1,062,168 32 $1,123,593 Annual Deposit $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 Interest Earnings $0 $758 $1,545 $2,365 $3,217 $4,103 $5,025 $5,983 $6,980 $8,017 $9,095 $10,216 $11,383 $12,596 $13,857 $15,169 $16,533 $17,952 $19,427 $20,962 $22,558 $24,218 $25,944 $27,740 $29,607 $31,549 $33,568 $35,668 $37,853 $40,124 $42,487 $44,944 End of Year Balance $18,939 $38,635 $59,119 $80,422 $102,577 $125,619 $149,582 $174,504 $200,423 $227,378 $255,412 $284,567 $314,888 $346,422 $379,218 $413,325 $448,797 $485,687 $524,053 $563,954 $605,451 $648,607 $693,490 $740,168 $788,714 $839,201 $891,707 $946,314 $1,003,105 $1,062,168 $1,123,593 $1,187,475 CAPITAL REPLACEMENT WORKSHEET 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 $1,187,475 $1,253,913 $1,323,008 $1,394,867 $1,469,600 $1,547,323 $1,628,154 $1,712,219 $1,799,646 $1,890,571 $1,985,132 $2,083,476 $2,185,754 $2,292,122 $2,402,746 $2,517,794 $2,637,444 $2,761,881 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $18,939 $47,499 $50,157 $52,920 $55,795 $58,784 $61,893 $65,126 $68,489 $71,986 $75,623 $79,405 $83,339 $87,430 $91,685 $96,110 $100,712 $105,498 $110,475 $1,253,913 $1,323,008 $1,394,867 $1,469,600 $1,547,323 $1,628,154 $1,712,219 $1,799,646 $1,890,571 $1,985,132 $2,083,476 $2,185,754 $2,292,122 $2,402,746 $2,517,794 $2,637,444 $2,761,881 $2,891,295 Notes REVENUE WORKSHEET Monthly Rate $ 1,260 $ 1,500 $ $ $ $ $ 400 500 500 300 100 Revenue Source Lease Revenue USPO Lease Health Clinic Lease Rental Revenue Teen Center Rent Tribal Council Office Rent City Council Office Rent Library Rent Other Revenue User Fees # of Units 1 1 1 2 3 1 1 Subtotals $ $ $ $ $ $ $ 15,120 18,000 Annual Estimate $ 33,120 $ 4,800 12,000 18,000 3,600 $ 1,200 38,400 1,200 Total Annual Revenue $ Notes 72,720 U.S.P.O. Lease - Current lease = $970, rate will increase with increased space and better facility. Health Clinic Lease - Current Rate, no increase expected. Teen Center Rent - Estimated rent charge per Sq. Ft. Tribal Council Office Rent - Estimated rent charge per Sq. Ft. City Council Office Rent - Estimated rent charge per Sq. Ft. Library Lease - Estimated rent charge per Sq. Ft. User Fees - Estimated use of community rooms by guests and community orgs. NET INCOME WORKSHEET Revenue Lease Revenue Rental Revenue Other Revenue $33,120 $38,400 $1,200 Total Revenue Expenses Operation and Maintenance Costs Repair and Replacement Costs Capital Replacement Account $45,995 $5,404 $18,939 $72,720 Total Expense $70,337 Net Operating Income (Loss) $2,383 Misc. Calculations Calculate Payroll Tax Percent Federal FICA Federal Medicare State Unemployment (or ECS) Employers % TOTAL 6.20% 1.45% 2.30% 9.95% Calculate Hourly Salary from Monthly Salary Monthly Salary $3,600.00 Annual Salary $43,200.00 Weeks per year 52 Weekly Salary $830.77 Hours per Week 40 Hourly Salary $20.77