FINANCIAL PROJECTIONS EXERCISE (CASE STUDY) (Mr. Howard, Inc.) this case exercise is designed to give you some practical, hands-on experience in prep p e forma ancial projections. Please note that the referenss case does not include all of the costs associated with Austin Jacques Howard is contemplating the start of his own business, which will be structured as a C- Corporation. His start-up costs are as follows: $10,000 24,000 6,495 125 Inventory (initial stock) Equipment Leasehold Improvements... Licenses & Permits Deposits Working Capital Other Start-Up Costs Total 20 15.200 6.419 563.070 Mr. Howard is requesting a small business loan to Launch his business. He has met with a loan officer at a local bank and was informed that he would need to contribute 20% of the total start-up costs. Additionally, the bank has offered an interest rate of 9% for five years. Note: For this case project, use 24.91 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-low loan amortization calculator (eg. www.bankrute.com) can be used for loan purposes Below are some assumptions that Mr. Howard have made regarding his proposed business for the first year. 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 90% projected to be cash sales and 10% credit sales (with 30-day terms): January $15,000 July......................... $27,000 February ........... $13,500 August $29,000 March $15.000 September.................$25,000 April.... $21,000 October ............ $30,000 May.......................... $22,000 November ................. $35.000 June ... $25,000 December ................ $50,000 Cost of Goods Sold (COGS): Inventory purchases are projected to be 65% of monthly sales, and will be paid for in the month it is sold. Salaries & Wages Mr. Howard plans to himself a salary of $2.500.00 per month, onc plans to himself a salary of $2.500.00 per month once the business is opened; however, pay a part-time worker a total of $10.00 (inclusine S173.50 in wages and $36.44 in payroll employee orientation and training before the opening date of business. Once the business is officially opened, this employee will work 20 hours per week and be paid $10.50 per hour. Payroll Taxes & Benefits: 21% of monthly salaries and wages. Securty: The building has an alarm system, but each tenant has to pay a 550 monthly monitoring fee. Promotion: The budget will be $3.000 initially, and then $1,000 per month thereafter Ducs & Subscriptions: Mr. Howard will subscribe to certain trade journals and is expected to pay $15.00 in monthly subscription fees. Accounting & Legal Fees: It will cost $1.200 to set-up his legal structure and record-keeping system, and Mr. Howard is planning to pay an accountant $70 per month for the first year to handling his books. Orice Supplies: $300 will be spent initially, and $50 is projected to be spent monthly during the first year, Maintenance & Repairs: Each tenant of the leased building shall pay a $75 monthly fee. Rent: The landlord is requiring a security deposit of $2,000, plus the first month's rent (5800) to be paid when the lease agreement is signed and prior to the opening date of business. The monthly rent of 5800 IS due on the first date of each month. Telephone: There is a $140 connection or deposit fee, and then telephone charges of 595 monthly Utilities: The local utility company requires a $700 deposit, with a projected expense of $400 monthly Insurance: Insurance coverage will cost $1.800 per year, with the first 6 months of premiums (totaling $900) due prior to opening the business and the balance (of $900) 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 $4,835) 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 CALCULATIONS FOR ASSUMPTIONS MADE ON FINANCIAL PROJE (Name of Case) INSTRUCTIONAL NOTES: Round all final financial calculations (answers) usine whole numbers only. For the percent (%) column on the column on the P & L statement round to one decimal place and show the percent syn. - 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 Fum-in the original copy of your financial spreadsheets, including the following calculations. (Note: Make a copy of your readsheets 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. 5. 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 retums, 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 (MR. HOWARD, INC.) START-UP COSTS Lace 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 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 (MR. HOWARD, INC.) MONTHLY INCOME STATEMENT PROJECTIONS For Period Ending Year GROSS REVENUE Sales TOTAL GROSS REVENUE NET SALES REVENUE GROSS PROFIT OPERATING EXPENSES Plexes as A Dars Sur ng Alapal Fees Omer Supplies Utilities Depreciatim & Animation Selling checkerede card Q Miscellaneous TOTAL OPERATING EXPENSES Teal Operating Income Em NET PROTT OSS NET PROFIT LOSS) heres Davides Wide Note: Include assumptions and explanations for income statement projections on separate page(s) (MR. HOWARD, INC.) MONTHLY CASH FLOW STATEMENT PROJECTIONS For Period Ending Year CASH ON HAND (Bege of CASH RECEIPTS Cash Sales Collection from Crede A Loan or her nection TOTAL CASH RECEIPTS TOTAL CASH AVAILABLE CASH DESBURSEMENTS: Prodvertise) Travel & Entertain Selling the credit card for Megos SUBTOTAL (and out for operating cos Dee Service ( open) Capital Purchase specify each Other Surpos Reserve and specify a TOTAL CASH DISKURSEMENTS CASH POSITION (Edelmeth) Note: Include assumptions and explanations for cash flow statement projections on separate page(s) (MR. HOWARD, 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 Not Fixed Assets Other (Specity 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 Note: Include assumptions and explanations for balance sheet projections on separate page(s) FINANCIAL PROJECTIONS EXERCISE (CASE STUDY) (Mr. Howard, Inc.) this case exercise is designed to give you some practical, hands-on experience in prep p e forma ancial projections. Please note that the referenss case does not include all of the costs associated with Austin Jacques Howard is contemplating the start of his own business, which will be structured as a C- Corporation. His start-up costs are as follows: $10,000 24,000 6,495 125 Inventory (initial stock) Equipment Leasehold Improvements... Licenses & Permits Deposits Working Capital Other Start-Up Costs Total 20 15.200 6.419 563.070 Mr. Howard is requesting a small business loan to Launch his business. He has met with a loan officer at a local bank and was informed that he would need to contribute 20% of the total start-up costs. Additionally, the bank has offered an interest rate of 9% for five years. Note: For this case project, use 24.91 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-low loan amortization calculator (eg. www.bankrute.com) can be used for loan purposes Below are some assumptions that Mr. Howard have made regarding his proposed business for the first year. 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 90% projected to be cash sales and 10% credit sales (with 30-day terms): January $15,000 July......................... $27,000 February ........... $13,500 August $29,000 March $15.000 September.................$25,000 April.... $21,000 October ............ $30,000 May.......................... $22,000 November ................. $35.000 June ... $25,000 December ................ $50,000 Cost of Goods Sold (COGS): Inventory purchases are projected to be 65% of monthly sales, and will be paid for in the month it is sold. Salaries & Wages Mr. Howard plans to himself a salary of $2.500.00 per month, onc plans to himself a salary of $2.500.00 per month once the business is opened; however, pay a part-time worker a total of $10.00 (inclusine S173.50 in wages and $36.44 in payroll employee orientation and training before the opening date of business. Once the business is officially opened, this employee will work 20 hours per week and be paid $10.50 per hour. Payroll Taxes & Benefits: 21% of monthly salaries and wages. Securty: The building has an alarm system, but each tenant has to pay a 550 monthly monitoring fee. Promotion: The budget will be $3.000 initially, and then $1,000 per month thereafter Ducs & Subscriptions: Mr. Howard will subscribe to certain trade journals and is expected to pay $15.00 in monthly subscription fees. Accounting & Legal Fees: It will cost $1.200 to set-up his legal structure and record-keeping system, and Mr. Howard is planning to pay an accountant $70 per month for the first year to handling his books. Orice Supplies: $300 will be spent initially, and $50 is projected to be spent monthly during the first year, Maintenance & Repairs: Each tenant of the leased building shall pay a $75 monthly fee. Rent: The landlord is requiring a security deposit of $2,000, plus the first month's rent (5800) to be paid when the lease agreement is signed and prior to the opening date of business. The monthly rent of 5800 IS due on the first date of each month. Telephone: There is a $140 connection or deposit fee, and then telephone charges of 595 monthly Utilities: The local utility company requires a $700 deposit, with a projected expense of $400 monthly Insurance: Insurance coverage will cost $1.800 per year, with the first 6 months of premiums (totaling $900) due prior to opening the business and the balance (of $900) 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 $4,835) 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 CALCULATIONS FOR ASSUMPTIONS MADE ON FINANCIAL PROJE (Name of Case) INSTRUCTIONAL NOTES: Round all final financial calculations (answers) usine whole numbers only. For the percent (%) column on the column on the P & L statement round to one decimal place and show the percent syn. - 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 Fum-in the original copy of your financial spreadsheets, including the following calculations. (Note: Make a copy of your readsheets 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. 5. 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 retums, 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 (MR. HOWARD, INC.) START-UP COSTS Lace 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 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 (MR. HOWARD, INC.) MONTHLY INCOME STATEMENT PROJECTIONS For Period Ending Year GROSS REVENUE Sales TOTAL GROSS REVENUE NET SALES REVENUE GROSS PROFIT OPERATING EXPENSES Plexes as A Dars Sur ng Alapal Fees Omer Supplies Utilities Depreciatim & Animation Selling checkerede card Q Miscellaneous TOTAL OPERATING EXPENSES Teal Operating Income Em NET PROTT OSS NET PROFIT LOSS) heres Davides Wide Note: Include assumptions and explanations for income statement projections on separate page(s) (MR. HOWARD, INC.) MONTHLY CASH FLOW STATEMENT PROJECTIONS For Period Ending Year CASH ON HAND (Bege of CASH RECEIPTS Cash Sales Collection from Crede A Loan or her nection TOTAL CASH RECEIPTS TOTAL CASH AVAILABLE CASH DESBURSEMENTS: Prodvertise) Travel & Entertain Selling the credit card for Megos SUBTOTAL (and out for operating cos Dee Service ( open) Capital Purchase specify each Other Surpos Reserve and specify a TOTAL CASH DISKURSEMENTS CASH POSITION (Edelmeth) Note: Include assumptions and explanations for cash flow statement projections on separate page(s) (MR. HOWARD, 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 Not Fixed Assets Other (Specity 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 Note: Include assumptions and explanations for balance sheet projections on separate page(s)