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)