ENTREPRENEURSHIP 470 FINANCIAL PROJECTIONS EXERCISE (CASE STUDY) (John Taylor - dba Sweet Tooth Candies) This case exercise is designed to give you some practical, hands-on experience in preparing pro forma financial projections. Please note that the referenced case does not include all of the costs associated with starting and operating business ..........$17.000 John Taylor is contemplating the start of his own business, which will be structured as a sole proprietorship His start-up costs are as follows: Uses or pplication of Funds Inventory (initial stock).......... Equipment................... ... 20,000 Leasehold Improvements ...... ... 5.000 Licenses & Permits........... 50 Deposits... 2,750 Working Capital............ .. 12.000 Other Start-Up Costs...... .......... 2.900 Total ... 559,700 Mr. Taylor has saved his money and can contribute S1 1.940.00 to help launch the business, however, he is requesting a small business loan for the remaining start-up costs. Mr. Taylor has identified a bank that will consider lending him the requisite start-up capital with the following terms: 4 years at 10% interest. Note: For this case project, se 30.4357 as the constant rate from a finance constant annual percentage table) to determine the monthly loan payment (including monthly interest and principal): however, please note that an on-line loan & amortization calculator (eg. www.bankrute.com) can be used for loan purposes. Below are some assumptions that Mr. Taylor have made regarding his proposed business for the first 12-months. Use the attached sample financial statement projections spreadsheets to prepare: (1) Sources & Uses of Funds Spreadsheet. (2) Pro-forma Income Statement for the first 3-months of operation. (3) Pro-forma Cash Flow Statement for the first 3-months of operation. (4) Pro-forma Balance Sheet after the loan. Sales & Terms: The sales forecast are as follows, with 60% projected to be cash sales and 40% credit sales (with 30-day terms): January ..... $12,000 July ..................... $28,989 February ........ $13,200 August ...................... 534,787 March ................... S15,840 September................. 541,744 April ......................... 519,965 October $50,093 May................... $21,962 November................ $60,112 June ............... $24,158 December ........... $72,134 Cost of Goods Sold (COGS): Inventory purchases are projected to be 72% of monthly sales, and will be paid for in the month it is sold. Page 2 Case Study - Mr. Taylor - dba Sweet Tooth Candies Salaries & Wages: Mr. Taylor has made plans to hire a part-time worker when the business is started, who will work 20 hours per week and be paid $8.50 per hour. Note: Mr. Taylor feels that he will need to withdraw a minimum of $3,500 per month during the first year of business operations. Payroll Taxes & Benefits: 21% of monthly salaries and wages. Promotion: The initial promotional budget will be $1,200, and then 2.5% of monthly gross sales. Vehicle & Delivery: Mr. Taylor will provide deliveries on a very limited basis, and has projected $300 per month for this expense. Accounting & Legal Fees: It will cost $500 to set-up his legal structure and record-keeping system Orice Supplies: $500 will be spent initially, and S25 is projected to be spent each month (from March through December) for the first year. or Rent: The landlord requires a $2,100 deposit, payable when the lease agreement is signed; the monthly rent is $700 and is due on the first day of each month. Telephone: There will be a $200 connection or deposit fee, and telephone charges of $135 per month Utilities: The local utility company requires a $450 deposit, with a projected monthly expense of $375. Insurance: Insurance coverage will cost $1,400 per year, with the first 6-months of premiums (totaling $700) due prior to opening the business and the balance (of S700) payable on or before June 30th Depreciation & Amortization: The equipment's estimated useful life is seven years. Mr. Taylor is planning to sign a two-year lease and will make certain leasehold improvements to the leased facility. Additionally, Mr. Taylor will incur certain start-up costs (totaling $2,250) prior to opening the business that he learned could be amortized over a 60-month period. (Note: Use a straight-line method for calculating purposes.) Selling (bank card fees): The bank card fees are 2.5% of total credit card sales, which are 80% of the monthly gross cash sales. - Bad Debts: Mr. Taylor has projected that 1% of monthly gross credit sales will be uncollectible. CALCULATIONS FOR ASSUMPTIONS MADE ON FINANCIAL PROJECTIONS (Name of Case) INSTRUCTIONAL NOTES: - Round all final financial calculations (answers), using whole numbers only. For the percent (%) column on the P&L statement, round to one decimal place and show the percent sign. - Add commas to financial numbers, where applicable - Do not use dollar (S) signs for your financial numbers Leave blank any category cell in which you are not recording a financial number - Turn in the original copy of your financial spreadsheets, including the following calculations. (Note: Make a copy of your spreadsheets before submittal, to be used for in-class review.) 1. Calculate the requisite asti equity by the owner(s). 11,940.00 (Given 2. Calculate the amount to be borrowed, if applicable. 3. Calculate the annual and monthly debt service. For this case, the constant rate (which is the weighted cost of the loan) is %, which should be converted to a decimal for calculation purposes. Annual Debt Service (D/S): Loan amount x constant rate Annual Debt Service (Loan Payment) Monthly D/S - ? Interest Year 1): Loan amount x annual interest rate = Total Interest (Year 1) Monthly Interest=? Principal (Year 1): Annual debt service - annual interest paid - Total Principal (Year 1) 4. Show how you calculated monthly gross cash and credit sales A 5. Show how you calculated monthly returns, allowances, & discounts N 6. Show how you calculated cost of goods sold 7. Show how you calculated monthly wages 8. Show how you calculated monthly payroll taxes 9. Show how you calculated monthly promotion budget 10. Show how you calculated monthly vehicle & delivery 11. Show how you calculated monthly insurance 12. Depreciation & Amortization: (Calculate annual and monthly) Add the monthly totals for 9a., 9b., and 9c., and record the sum total under this category. 13. Calculate monthly selling expense 14. Calculate monthly bad debts ENTREPRENEURSHIP 470 FINANCIAL PROJECTIONS EXERCISE (CASE STUDY) (John Taylor - dba Sweet Tooth Candies) This case exercise is designed to give you some practical, hands-on experience in preparing pro forma financial projections. Please note that the referenced case does not include all of the costs associated with starting and operating business ..........$17.000 John Taylor is contemplating the start of his own business, which will be structured as a sole proprietorship His start-up costs are as follows: Uses or pplication of Funds Inventory (initial stock).......... Equipment................... ... 20,000 Leasehold Improvements ...... ... 5.000 Licenses & Permits........... 50 Deposits... 2,750 Working Capital............ .. 12.000 Other Start-Up Costs...... .......... 2.900 Total ... 559,700 Mr. Taylor has saved his money and can contribute S1 1.940.00 to help launch the business, however, he is requesting a small business loan for the remaining start-up costs. Mr. Taylor has identified a bank that will consider lending him the requisite start-up capital with the following terms: 4 years at 10% interest. Note: For this case project, se 30.4357 as the constant rate from a finance constant annual percentage table) to determine the monthly loan payment (including monthly interest and principal): however, please note that an on-line loan & amortization calculator (eg. www.bankrute.com) can be used for loan purposes. Below are some assumptions that Mr. Taylor have made regarding his proposed business for the first 12-months. Use the attached sample financial statement projections spreadsheets to prepare: (1) Sources & Uses of Funds Spreadsheet. (2) Pro-forma Income Statement for the first 3-months of operation. (3) Pro-forma Cash Flow Statement for the first 3-months of operation. (4) Pro-forma Balance Sheet after the loan. Sales & Terms: The sales forecast are as follows, with 60% projected to be cash sales and 40% credit sales (with 30-day terms): January ..... $12,000 July ..................... $28,989 February ........ $13,200 August ...................... 534,787 March ................... S15,840 September................. 541,744 April ......................... 519,965 October $50,093 May................... $21,962 November................ $60,112 June ............... $24,158 December ........... $72,134 Cost of Goods Sold (COGS): Inventory purchases are projected to be 72% of monthly sales, and will be paid for in the month it is sold. Page 2 Case Study - Mr. Taylor - dba Sweet Tooth Candies Salaries & Wages: Mr. Taylor has made plans to hire a part-time worker when the business is started, who will work 20 hours per week and be paid $8.50 per hour. Note: Mr. Taylor feels that he will need to withdraw a minimum of $3,500 per month during the first year of business operations. Payroll Taxes & Benefits: 21% of monthly salaries and wages. Promotion: The initial promotional budget will be $1,200, and then 2.5% of monthly gross sales. Vehicle & Delivery: Mr. Taylor will provide deliveries on a very limited basis, and has projected $300 per month for this expense. Accounting & Legal Fees: It will cost $500 to set-up his legal structure and record-keeping system Orice Supplies: $500 will be spent initially, and S25 is projected to be spent each month (from March through December) for the first year. or Rent: The landlord requires a $2,100 deposit, payable when the lease agreement is signed; the monthly rent is $700 and is due on the first day of each month. Telephone: There will be a $200 connection or deposit fee, and telephone charges of $135 per month Utilities: The local utility company requires a $450 deposit, with a projected monthly expense of $375. Insurance: Insurance coverage will cost $1,400 per year, with the first 6-months of premiums (totaling $700) due prior to opening the business and the balance (of S700) payable on or before June 30th Depreciation & Amortization: The equipment's estimated useful life is seven years. Mr. Taylor is planning to sign a two-year lease and will make certain leasehold improvements to the leased facility. Additionally, Mr. Taylor will incur certain start-up costs (totaling $2,250) prior to opening the business that he learned could be amortized over a 60-month period. (Note: Use a straight-line method for calculating purposes.) Selling (bank card fees): The bank card fees are 2.5% of total credit card sales, which are 80% of the monthly gross cash sales. - Bad Debts: Mr. Taylor has projected that 1% of monthly gross credit sales will be uncollectible. CALCULATIONS FOR ASSUMPTIONS MADE ON FINANCIAL PROJECTIONS (Name of Case) INSTRUCTIONAL NOTES: - Round all final financial calculations (answers), using whole numbers only. For the percent (%) column on the P&L statement, round to one decimal place and show the percent sign. - Add commas to financial numbers, where applicable - Do not use dollar (S) signs for your financial numbers Leave blank any category cell in which you are not recording a financial number - Turn in the original copy of your financial spreadsheets, including the following calculations. (Note: Make a copy of your spreadsheets before submittal, to be used for in-class review.) 1. Calculate the requisite asti equity by the owner(s). 11,940.00 (Given 2. Calculate the amount to be borrowed, if applicable. 3. Calculate the annual and monthly debt service. For this case, the constant rate (which is the weighted cost of the loan) is %, which should be converted to a decimal for calculation purposes. Annual Debt Service (D/S): Loan amount x constant rate Annual Debt Service (Loan Payment) Monthly D/S - ? Interest Year 1): Loan amount x annual interest rate = Total Interest (Year 1) Monthly Interest=? Principal (Year 1): Annual debt service - annual interest paid - Total Principal (Year 1) 4. Show how you calculated monthly gross cash and credit sales A 5. Show how you calculated monthly returns, allowances, & discounts N 6. Show how you calculated cost of goods sold 7. Show how you calculated monthly wages 8. Show how you calculated monthly payroll taxes 9. Show how you calculated monthly promotion budget 10. Show how you calculated monthly vehicle & delivery 11. Show how you calculated monthly insurance 12. Depreciation & Amortization: (Calculate annual and monthly) Add the monthly totals for 9a., 9b., and 9c., and record the sum total under this category. 13. Calculate monthly selling expense 14. Calculate monthly bad debts