Competencies
- Interpret how the components of a balance sheet and income statement communicate the financial position of an organization.
- Interpret the components of a cash-flow statement to describe the liquidity of an organization.
- Evaluate financial ratios to differentiate profitability and liquidity across organizations.
- Analyze leverage and activity ratios to review operating performance across organizations.
- Determine current values of cash streams utilizing discounted cash flow techniques.
- Critique organizational investments using forecasting scenarios.
Student Success Criteria
View the grading rubric for this deliverable by selecting the "This item is graded with a rubric" link, which is located in the Details & Information pane
.
Scenario
You recently went to a dealership for an oil change on your vehicle, as no independent oil change companies or auto repair shops are within 50 miles of your home. You paid $250 for this oil change on your Chevy Camaro.
Given your entrepreneurial spirit and frustration with the cost of the oil change, you have an idea for a mobile oil change company. You have completed a marketing survey to determine potential interest in such a company. The marketing survey results suggested that 50% of vehicle owners would entertain using a mobile oil change company for oil changes if such a company existed.
You live in a town with 30,000 registered vehicles, of which their owners take 15,000 to the dealerships for oil changes. Most owners take their vehicles to the dealership three times a year.
You will need the following to start this business and to gain funding from investors.:
- 3 Mobile Oil Change Trucks (General Modifiable Flatbed Commercial Trucks)
- Oil Changing Supplies (i.e., motor oil, clean cloths, drip pans, and oil filters)
- Oil Changing Tools (i.e., Motor Oil Tank, Motor oil extractor, and a Fluid Flush Machine Mobile Oil Dispensing Kit)
- Insurance (i.e., Auto for all three trucks and General Liability Insurance for the Business)
- Budget for 3 Employees (Hourly Pay)
Pro forma financial statements, a financial plan, and a business plan are required for all potential investors and creditors. Prepare a 10-15 slide PowerPoint presentation and include financial proforma statements with the above information to present to potential investors and creditors.
Instructions
Using PowerPoint (or similar) and this Module 7 Feasibility Calculations Template – 2023, you have been asked to develop a feasibility study presentation to determine the feasibility of this idea for a new company.
Module 7 Feasibility Calculations Template - 2023.xlsx
Your feasibility study presentation should include the following:
- Develop a conservative forecasted sales budget based on the survey results using the Excel template. Make sure your price is less than what the dealership charges.
- Within the Excel spreadsheet, Identify fixed and variable costs for each oil change. Using these values, develop an operating budget to determine the contribution margin for an individual oil change and the total amount for the forecasted sales budget.
- Develop a proforma income statement, balance sheet, and statement of cash flows for the first year of business. The business Tax Rate is 20%.
- Based on these proforma financial statements, calculate one financial ratio in each category of profitability, liquidity, and solvency ratios.
- Based on a 40-hour work week, estimate how many oil changes one person can realistically perform. Given the projected sales forecast, develop a capital investment proposition for additional trucks with anticipated useful lives of 5 years. Use the discounted cash flow method at an interest rate of 6%. Develop a 10-15 slide PowerPoint presentation summarizing these figures above and provide a recommendation to invest in this mobile oil-changing business based on the feasibility report provided.
NOTE – Be sure the documents display proper grammar, spelling, punctuation, and sentence structure.
Assessment Requirements/Submission Requirement:
- Submit a 10-15 slide PowerPoint presentation, including attribution for outside sources.
- Submit the Excel Feasibility Spreadsheet to show all calculations used for the scenario.
A – 4 – Mastery
Accurate and appropriately conservative forecasted sales budget based on the survey results.
A – 4 – Mastery
Correctly identifies fixed and variable costs for each oil change and develops a realistic operating budget.
A – 4 – Mastery
Accurate and complete proforma income statement, balance sheet, and statement of cash flows for the first year of business.
A – 4 – Mastery
Accurately calculates all profitability, liquidity, and solvency ratios.
A – 4 – Mastery
Accurately develops a capital investment proposition and PowerPoint presentation for potential investors.
Calculations
| Part 1 – Sales budget | |||||
| Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | Year | |
| Expected unit sales | – 0 | ||||
| Unit selling price($) | |||||
| Total sales | – 0 | – 0 | – 0 | – 0 | – 0 |
| Part 2 – Operating budget – Per Oil Change | |||||
| Sales revenue | |||||
| Variable costs | |||||
| Contribution margin per oil change | 0 | ||||
| Fixed costs | |||||
| Net income | 0 | ||||
| Part 3 Prepare income statement, balance sheet and cash flows statement | |||||
| Proforma Income Statement | |||||
| December 31 | |||||
| Sales revenue | – 0 | ||||
| Cost of goods sold | – 0 | ||||
| Gross profit | – 0 | ||||
| Operating expenses | |||||
| Income before interest and tax | – 0 | ||||
| Interest Expense | |||||
| Income before tax | – 0 | ||||
| tax expense | – 0 | ||||
| Net income | – 0 | ||||
| Proforma Balance Sheet | |||||
| December 31 | |||||
| Assets | |||||
| Current assets | |||||
| Cash and equivalents | |||||
| Short-term investments | |||||
| Receivables | |||||
| Merchandise inventories | |||||
| Other current assets | 0 | ||||
| Total current assets | 0 | ||||
| Property and equipment | |||||
| Less accumulated deprecation | Reflect negatively. | ||||
| Net property and equipment | 0 | ||||
| Other assets | 0 | ||||
| Total assets | 0 | ||||
| Liabiliities and Stockholders' equity | |||||
| Current liabilities | |||||
| Accounts payable | |||||
| Accrued liabilities | |||||
| Accrued income taxes | |||||
| Accrued compensation payable | |||||
| Other current liabilities | |||||
| Total current liabilities | 0 | ||||
| Long-term liabilities | |||||
| Long-term debt | |||||
| Other long-term liabilities | |||||
| Total long-term liabilities | 0 | ||||
| Total liabilities | 0 | ||||
| Stockholders' equity | |||||
| Common stock | 0 | ||||
| Retained earnings and other | 0 | ||||
| Total Liabilities and stockholders' equity | 0 | ||||
| Proforma Cash Flows Statement | |||||
| December 31 | |||||
| Cash flows from operating activities | |||||
| Cash receipts from operating activities | |||||
| Cash payments from operating activities | |||||
| Net cash provided by operating activities | 0 | ||||
| Cash flows from investing activities | |||||
| Purchased equipment | |||||
| Net cash used by investing activities | 0 | ||||
| Cash flows from financing activities | |||||
| Issurance of note payable | |||||
| Net cash provided by financing activities | 0 | ||||
| Net increase in cash | 0 | ||||
| Cash at beginning of period | 0 | ||||
| Cash at end of period | 0 | ||||
| Part 4 – Calculate ratios | |||||
| Ratios formula | Ratio name | Ratio type | Amount | ||
| Liquidity | |||||
| Profitability | |||||
| Solvency | |||||
| Part 5 – Capital Investment Decision | |||||
| FV (future value) | |||||
| N (number of years or periods) | |||||
| I (interest rate) | |||||
| PMT (net cash flow) | – 0 | ||||
| PV (present value) | $0 | ||||
| Present value of future cash flows from oil change | $0 | ||||
| Initial investment of equipment | |||||
| Net present value | 0 |
Sales Revenue (selling price per oil change by estimated sales units for the year COGS( Variable cost per oil change + fixed costs per oil change x expected unit sales) Gross Profit (sales – COGS) Operating expenses (estimate additional costs) Ex. 375,000 Income before interest and tax (Gross profit-operating expenses) Interest Expense (estimate) Ex. 27,000 Income before tax (Income before interest + tax-interest expense) Tax Expense (estimate tax rate, it is safe to use a general business rate of 20%) So take income before tax and multiply it by 20% Net Income (income before tax – tax expense)
All numbers under assets are estimated
All numbers under liabilities and equity are estimated This is a sole proprietorship so there is no stock of any kind
Cash receipts from operating activities are estimated Cash payments from operating activities are estimated and reflected negatively. Purchasing of equipment, estimate the total price of equipment to complete oil changes (reflect negatively) Notes Payable: Assume a bank loan for the business
Future Value is "0" Life of equipment stated in the deliverable Discount Rate Net Cash flows per year (net income from proforma income statement + annual depreciation = yearly net cash flows) Present Value (interest rate x number of periods x net cash flow payments x future value, 0)
Present value is from previous part Initial Investment (estimate the cost of equipment, could have done this previously in the balance sheet) (reflect negatively) Net Present Value ( present value – initial investment)
