FINANCIAL PROJECTIONS EXERCISE (CASE STUDY) (Ms. LeBlanc, Inc.) 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 a business ..eve. Rita LeBlanc is contemplating the start of her own business, which will be structured as an S-Corporation Her start-up costs are as follows: Uses or Application of Funds: Inventory (initial stock).. $17,000 Equipment ... 20,000 Leasehold Improvements... .. 8,000 Licenses & Permits.. Deposits ..3,975 Working Capital ..... ... 23,725 Other Start-Up Costs........... 9.063 Total.. $81,963 ...... 200 Ms. LeBlanc is requesting a small business loan to launch her business. She has met with a loan officer (at a local bank) who is willing to provide a 5-year loan at 10% interest to cover a portion of her start-up costs, if she could inject 25% in cash equity. Note: For this case project, use 25.4965 as the constant rate from a finance constant anual percentage able 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 Ms. LeBlanc 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 80% projected to be cash sales and 20% credit sales (with 30-day terms): January ... ....... $16,000 July $22,463 February ................... $16,800 August ............... .524,709 M arch ................... $17,640 September............... $27.180 April...... $18.522 October $35,334 May $19,448 November................. $45,934 June $20,421 December .... ........559,714 Returns, Allowances, & Discounts: Rita has learned from a recent trade association meeting that she should plan for merchandise returns in her industry to be 8.5% of monthly sales revenue. Cost of Goods Sold (COGS): Inventory purchases are projected to be 70% of monthly sales, and will be paid for in the month it is sold. Salaries & Wages: Ms. LeBlanc plans to withdraw $3,800.00 per month when the business is up and running." She will hire and pay a part-time worker $412.50 (including $330.00 in wages and 582.50 in payroll taxes) two weeks before the business is opened for employee orientation and training; this employee will be compensated at a rate of $11.00 per hour and will work 15 hours each week during the referenced training period. After the business is opened, the employee will work 30 hours per week. Payroll Taxes & Benefits: 25% of monthly salaries and wages. Security: The building has an alarm system, but each tenant has to pay a $75 monthly monitoring fee. Promotion: The budget will be $3,000 initially, and then $2,500 per month thereafter. Dues & Subscriptions: Ms. LeBlanc will subscribe to certain trade journals and is expected to pay $45.00 in monthly subscription fees. Accounting & Legal Fees: It will cost $1,500 to set-up her legal structure and record-keeping system, and $75 per month for the first year) to handle her accounting records. +Office Supplies: $400 will be spent initially, and $25 is projected to be spent monthly during the first year. - Maintenance & Repairs: Each tenant of the leased building shall pay a $150 monthly fee. Rent The landlord is requiring a security deposit of $3,000, plus the first month's rent ($1.500 to be paid when the lease agreement is signed and prior to the opening date of business. The monthly rent of $1,500 is due on the first date of each month. Telephone: There is a $175 connection or deposit fee, and then telephone charges of $120 monthly Utilities: The local utility company requires an $800 deposit, with a projected expense of $450 monthly Insurance Insurance coverage will cost $4,500 per year, with the first 6 months of premiums (totaling $2,250) due prior to opening the business and the balance (of $2,250) payable on July 1 Depreciation & Amortization: The equipment has an estimated useful life of seven years, and the leasehold improvements are based on a three-year lease agreement. There are certain start-up costs (totaling 55,513) prior to opening the business that can be amortized over a 5-year or 60-month period (Note: Use a straight-line method for calculating purposes.) Selling (bank card fees): 2.5% of total credit card sales, which are 75% of the monthly gross cash sales. 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 ($) signs for your financial numbers - Leave blank any category cell in which you are not recording a financial number - Tum-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 cash equity by the owner(s). 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 5. Show how you calculated monthly returns, allowances, & discounts 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 (MS. LEBLANC, INC.) START-UP COSTS (Sources and Uses of Funds) Uses or Application: Real Estate Building Acquisition (Purchase) Building Construction: Land Acquisition Construction Costs Total Building Construction .. Inventory (opening stock)..... Equipment (itemize on separate page) Leasehold Improvements: Remodeling and Redecorating Fixtures & Displays, etc. Signs (exterior & interior) Installation Labor Other (please specify) Total Leasehold Improvements Licenses & Permits .. Deposits: Lease Utility Telephone Other (please specify) Total Deposits Working Capital (funds for operating expenses for three to six months) Living Expenses...... (money for expenses for at least three months) Other Start-Up Costs (listed below are some examples): Accounting & Legal Initial Promotional Expenditure Office Supplies Insurance Other (please specify) Total Other Start-Up Costs ...... TOTAL USES (TOTAL START-UP COSTS).. Sources: Cash Injection or Equity by Owner(s) Loan Amount TOTAL SOURCES....... Note: Total Uses Total Sources (MS. LEBLANC, INC.) MONTHLY INCOME STATEMENT PROJECTIONS For Period Ending Year Month Month Tool CROSS REVENUE Sales or Fees) TOTAL GROSS REVENUE Less Returns Allowances, Discount NET SALES REVENUE Le COST OF GOODS SOLD: GROSS PROFIT OPERATING EXPENSES Salaries L Pull Tees A Benes Vehicle Delvis Accounting & Legales Office Supplies Selle checked and free) B Debo Miscellaneous TOTAL OPERATING EXPENSES NET PROFIT (LOSS) ertes Note: Include assumptions and explanations for income Matement projections ce separate pe (MS. LEBLANC, INC.) MONTHLY CASH FLOW STATEMENT PROJECTIONS For Period Ending Year Pre Start-up Position Month Month Month - Month 3 T T CASH ON HAND (Beginning of month) SH RECEIPTS: Cash Sales Collections from Credit Account I Loan or Other Cash Injection TOTAL CASH RECEIPTS TOTAL CASH AVAILABLE CASH DISBURSEMENTS: Purchases (in ) Salaries Wars Commissions Payroll Taxes Aflenefits Security Promotion advertising.ee) Vehicle A Delivery Dues A Subscriptions Accounting & Legal Fees Office Supplies Operating Supplies Selling (che credit card fee ) Other Expenses specify each) Miscellane SUBTOTAL ( foreparatie cu De Service to pay Capital has specify each) Other Couls Reserver crow (speciach) Om Windows TOTAL CASH DISBURSEMENTS CASH POSITION Edometh) Note: Include assumptions and explanations for cash flow statement projections on separate page(s) (MS. LEBLANC, INC.) MONTHLY BALANCE SHEET PROJECTION For Period Ending Year Pre Start-up Position ASSETS Current Assets: Cash Investments (Marketable Securities) Accounts Receivable Inventory Prepaid Expenses Other (specify each) Total Current Assets Fixed Assets: Land Buildings Leasehold Improvements Furniture/Fixtures Machinery & Equipment Vehicles (Autos & Trucks) Less Accumulated Depreciation & Amort. Total Net Fixed Assets Other (specify each) Total Assets LIABILITIES Current Liabilities: Notes Payable--Bank Current Portion of Long-Term Debt Accounts Payable--Trade Accruals (payroll) Income Taxes Payable Other (specify each) Total Current Liabilities Long-Term Liabilities: Notes Payable Other (specify each) Total Long-Term Liabilities Total Liabilities NET WORTH Owner Equity Capital Stock Additional Paid-In Capital Retained Earnings Total Net Worth Total Liabilities and Net Worth on par Note: Include assumptions and explanations for balance sheet projections on separate page(s) FINANCIAL PROJECTIONS EXERCISE (CASE STUDY) (Ms. LeBlanc, Inc.) 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 a business ..eve. Rita LeBlanc is contemplating the start of her own business, which will be structured as an S-Corporation Her start-up costs are as follows: Uses or Application of Funds: Inventory (initial stock).. $17,000 Equipment ... 20,000 Leasehold Improvements... .. 8,000 Licenses & Permits.. Deposits ..3,975 Working Capital ..... ... 23,725 Other Start-Up Costs........... 9.063 Total.. $81,963 ...... 200 Ms. LeBlanc is requesting a small business loan to launch her business. She has met with a loan officer (at a local bank) who is willing to provide a 5-year loan at 10% interest to cover a portion of her start-up costs, if she could inject 25% in cash equity. Note: For this case project, use 25.4965 as the constant rate from a finance constant anual percentage able 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 Ms. LeBlanc 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 80% projected to be cash sales and 20% credit sales (with 30-day terms): January ... ....... $16,000 July $22,463 February ................... $16,800 August ............... .524,709 M arch ................... $17,640 September............... $27.180 April...... $18.522 October $35,334 May $19,448 November................. $45,934 June $20,421 December .... ........559,714 Returns, Allowances, & Discounts: Rita has learned from a recent trade association meeting that she should plan for merchandise returns in her industry to be 8.5% of monthly sales revenue. Cost of Goods Sold (COGS): Inventory purchases are projected to be 70% of monthly sales, and will be paid for in the month it is sold. Salaries & Wages: Ms. LeBlanc plans to withdraw $3,800.00 per month when the business is up and running." She will hire and pay a part-time worker $412.50 (including $330.00 in wages and 582.50 in payroll taxes) two weeks before the business is opened for employee orientation and training; this employee will be compensated at a rate of $11.00 per hour and will work 15 hours each week during the referenced training period. After the business is opened, the employee will work 30 hours per week. Payroll Taxes & Benefits: 25% of monthly salaries and wages. Security: The building has an alarm system, but each tenant has to pay a $75 monthly monitoring fee. Promotion: The budget will be $3,000 initially, and then $2,500 per month thereafter. Dues & Subscriptions: Ms. LeBlanc will subscribe to certain trade journals and is expected to pay $45.00 in monthly subscription fees. Accounting & Legal Fees: It will cost $1,500 to set-up her legal structure and record-keeping system, and $75 per month for the first year) to handle her accounting records. +Office Supplies: $400 will be spent initially, and $25 is projected to be spent monthly during the first year. - Maintenance & Repairs: Each tenant of the leased building shall pay a $150 monthly fee. Rent The landlord is requiring a security deposit of $3,000, plus the first month's rent ($1.500 to be paid when the lease agreement is signed and prior to the opening date of business. The monthly rent of $1,500 is due on the first date of each month. Telephone: There is a $175 connection or deposit fee, and then telephone charges of $120 monthly Utilities: The local utility company requires an $800 deposit, with a projected expense of $450 monthly Insurance Insurance coverage will cost $4,500 per year, with the first 6 months of premiums (totaling $2,250) due prior to opening the business and the balance (of $2,250) payable on July 1 Depreciation & Amortization: The equipment has an estimated useful life of seven years, and the leasehold improvements are based on a three-year lease agreement. There are certain start-up costs (totaling 55,513) prior to opening the business that can be amortized over a 5-year or 60-month period (Note: Use a straight-line method for calculating purposes.) Selling (bank card fees): 2.5% of total credit card sales, which are 75% of the monthly gross cash sales. 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 ($) signs for your financial numbers - Leave blank any category cell in which you are not recording a financial number - Tum-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 cash equity by the owner(s). 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 5. Show how you calculated monthly returns, allowances, & discounts 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 (MS. LEBLANC, INC.) START-UP COSTS (Sources and Uses of Funds) Uses or Application: Real Estate Building Acquisition (Purchase) Building Construction: Land Acquisition Construction Costs Total Building Construction .. Inventory (opening stock)..... Equipment (itemize on separate page) Leasehold Improvements: Remodeling and Redecorating Fixtures & Displays, etc. Signs (exterior & interior) Installation Labor Other (please specify) Total Leasehold Improvements Licenses & Permits .. Deposits: Lease Utility Telephone Other (please specify) Total Deposits Working Capital (funds for operating expenses for three to six months) Living Expenses...... (money for expenses for at least three months) Other Start-Up Costs (listed below are some examples): Accounting & Legal Initial Promotional Expenditure Office Supplies Insurance Other (please specify) Total Other Start-Up Costs ...... TOTAL USES (TOTAL START-UP COSTS).. Sources: Cash Injection or Equity by Owner(s) Loan Amount TOTAL SOURCES....... Note: Total Uses Total Sources (MS. LEBLANC, INC.) MONTHLY INCOME STATEMENT PROJECTIONS For Period Ending Year Month Month Tool CROSS REVENUE Sales or Fees) TOTAL GROSS REVENUE Less Returns Allowances, Discount NET SALES REVENUE Le COST OF GOODS SOLD: GROSS PROFIT OPERATING EXPENSES Salaries L Pull Tees A Benes Vehicle Delvis Accounting & Legales Office Supplies Selle checked and free) B Debo Miscellaneous TOTAL OPERATING EXPENSES NET PROFIT (LOSS) ertes Note: Include assumptions and explanations for income Matement projections ce separate pe (MS. LEBLANC, INC.) MONTHLY CASH FLOW STATEMENT PROJECTIONS For Period Ending Year Pre Start-up Position Month Month Month - Month 3 T T CASH ON HAND (Beginning of month) SH RECEIPTS: Cash Sales Collections from Credit Account I Loan or Other Cash Injection TOTAL CASH RECEIPTS TOTAL CASH AVAILABLE CASH DISBURSEMENTS: Purchases (in ) Salaries Wars Commissions Payroll Taxes Aflenefits Security Promotion advertising.ee) Vehicle A Delivery Dues A Subscriptions Accounting & Legal Fees Office Supplies Operating Supplies Selling (che credit card fee ) Other Expenses specify each) Miscellane SUBTOTAL ( foreparatie cu De Service to pay Capital has specify each) Other Couls Reserver crow (speciach) Om Windows TOTAL CASH DISBURSEMENTS CASH POSITION Edometh) Note: Include assumptions and explanations for cash flow statement projections on separate page(s) (MS. LEBLANC, INC.) MONTHLY BALANCE SHEET PROJECTION For Period Ending Year Pre Start-up Position ASSETS Current Assets: Cash Investments (Marketable Securities) Accounts Receivable Inventory Prepaid Expenses Other (specify each) Total Current Assets Fixed Assets: Land Buildings Leasehold Improvements Furniture/Fixtures Machinery & Equipment Vehicles (Autos & Trucks) Less Accumulated Depreciation & Amort. Total Net Fixed Assets Other (specify each) Total Assets LIABILITIES Current Liabilities: Notes Payable--Bank Current Portion of Long-Term Debt Accounts Payable--Trade Accruals (payroll) Income Taxes Payable Other (specify each) Total Current Liabilities Long-Term Liabilities: Notes Payable Other (specify each) Total Long-Term Liabilities Total Liabilities NET WORTH Owner Equity Capital Stock Additional Paid-In Capital Retained Earnings Total Net Worth Total Liabilities and Net Worth on par Note: Include assumptions and explanations for balance sheet projections on separate page(s)