# 7: Accounting

The CEO of Smartphone Apps, LLC is preparing a loan application. Using the data below (only), prepare an Income Statement . Within this Income statement, include totals for Gross Margin, Sales General, and Administrative, Earnings before Interest and Taxes, Pretax Income, and Net Income. Also compute the Profit Margin on Sales. Submit using a spreadsheet.

Net Revenue / $900,000
Hourly Labor Costs / $402,547
Material Costs / $30,125
Marketing / $27,000
Depreciation / $58,122
Research and Development / $16,000
Administrative / $139,741
Interest Expense / $21,608
Taxes / 25% / of pretax Income

8: Income statement with Depreciation

The following case will be used in Assignments 8, 9 and 10. Assignment 8 requires a proposal Income statement. Assignment 9 requires a proposal Cash Flow statement, and Assignment 10 will use the result of assignment 8 and 9 as a financial model to experiment with various assumptions and decisions.

General Phone Apps (GPA) is evaluating a proposal to internally develop a software capability that is intended to enhance their application (app) development process by automating testing and simplifying product conversion among different operating systems. Since it will be cloud based, it also will facilitate group development projects and enable employees to more easily work from different locations. This is not a product to be sold, but rather it will assist internal development of their app software, so it is depreciable.

The development and conversion process is estimated to take one year in year 0) and cost $1,000,000. This investment includes all programming training, loading of existing products and testing the resulting conversion. An internal project over the past year has been completed that evaluated the feasibility and created a macro design of the proposed system (sunk costs).

The new software is expected to both increase sales and decrease development costs. The sales for the present year (year 0) are $2.,000,000 and without this new software capability would likely grow 10% annually. The new software is forecast to enable a sales of 33.33% per year, instead of only 10%. The annual cost of fulfilling orders and customer support (COGS) is forecast at 50% of revenue and expected to continue at this level.

The change in Marketing and Sales expense related to this project would be an increase of $75,000 annually and unchanged over the projects time horizon. The annual cost of the cloud service will be $150,000 in year 1 and increase 10% annually after that.

A three year time horizon is to be used for the evaluation, although the software is expected to be used much longer. The GPA tax rate is 25% and they use a MARR of 15%. Three-year MACRS depreciation has been chosen for the projects $1 million development and implementation cost.

Submit a spreadsheet containing an Income Statement for this proposal. Use the standard Income statement format that includes totals for COGS, SG&A, EBIT and Net Earnings.

9: Cash Flow Statement

This assignment is a continuation of last week’s (week 8) assignment for which the solution is attached.

A buyer has been found for the existing servers (that will no longer be needed) for $50,000 that will be received in year 1. As of year 1, the servers will have been fully depreciated and have a book value of zero.

The software resulting from the project will be proprietary and not sold, but it is estimated that it would be valued at the end of year 3 for $300,000.

Accounts Receivable is $250,000 in the present year (year 0 and is forecast to decrease 15% annually in year 1 and in each year thereafter. Accounts Payable is presently $150,000 (year 0) and is expected to increase to $200,000 by the end of year 1 and hold constant at that level. No change in inventory working capital is expected.

Prepare a cash flow statement for the proposal and determine the present worth, annual worth, and internal rate of return. Should the project be approved if the MARR for GPA is 10%?

10: Financial Modeling

This assignment is a continuation of last week’s (week 9) assignment. The solution for the week 9 assignment is attached.

  1. Submit a spreadsheet that shows a sensitivity analysis on the percentage increase in sales from 20% through 40% in increments of 5%.
  2. Determine the breakeven point in your spreadsheet for the percentage increase in sales.
  3. Evaluate the following scenarios. The Pessimistic and Optimistic scenarios were generated by outside consultants. The “TV Marketing” scenario is an internal proposal of using major television ads during sport events. Discuss the implications of your scenario analysis in a paragraph or two.

Scenario / Annual Sales % increase / COGS % / Marketing
Pessimistic / 20% / 65% / $75,000
Optimistic / 40% / 45% / $75,000
TV Marketing / 50% / 50% / $1,000,000

11: Public Regulated Utility

Virginia Natural Gas Company (VNGC) must provide a regulation and metering unit to a new subdivision near Norfolk. They need to determine the revenue requirement so they can determine rates.

They already own right-of-way and must now install the equipment at a cost of $170,000 with operating and maintenance costs of $9,000 per year. The useful life of the equipment is 25 years with no salvage value after that time.

VNGC will borrow the needed capital, and the rate is 11 percent over 25 years with uniform principal payments plus interest on the remaining principal. Depreciation over 20 years follows MACRS-GDS . The effective tax rate is 40 percent.

The attached spreadsheet for the entire life of the project has been constructed for management and they have approved it.

You have been assigned to:

  1. Determine the constant (for all years) revenue requirement that will makes the Present worth of the entire project equal to zero.
  2. Determine the minimum constant (for all years) revenue requirement that would enable paying shareholders a dividend of 3% of the revenue requirement. This will require adding a line item to the spreadsheet in the cash flow area that deducts this dividend from free cash flow.

Note that all Revenue Requirements are set up to be equal to the revenue requirement in the data block. Therefore, changing the revenue requirement in the data block will change the value for all years.

The spreadsheet Goal Seek capability is recommended for doing this assignment.