M4 Spreadsheet

Need Project Spreadsheet filled out by 6:00am 1/17

[removed]

Breakeven Analysis

Breakeven Analysis

($)

(%)

Variable Costs

0.0%

0.0%

Fixed Costs

$ 250

$ 3,000

$ –

$ –

$ –

Other (specify) $ –

$ –

$ –

$ 2,000

04

262

Enter your company name here

Cost Description

Fixed Costs

Variable Costs

Mixed Costs

These costs go in both the fixed and variable columns. Use dollars in fixed and a percentage in variable.

Cost of Goods Sold

45.0%

Inventory

0.0%

Raw Materials

Direct Labor (Includes Payroll Taxes)

Salaries (includes payroll taxes)

$ 2,000

Supplies

$ 1,000

Repairs & maintenance

$ 3,000

Advertising

$ 250

Car, delivery and travel

$ 750

Accounting and legal

Rent

1.0%

Telephone

$ 500

Utilities

$ 600

Insurance

$ 800

Taxes (Real estate, etc.)

$ –

Interest

Depreciation

Other (specify)

Miscellaneous expenses

Principal portion of debt payment

Owner’s draw

Total Fixed Costs

$ 14,150

Total Variable Costs

46.0%

Enter your sales units

100

Breakeven Sales level =

262

Breakeven Sales in Units

Some of the material has been sourced from: http://www.score.org/downloads/Break-Even%20Analysis1.xls

Total will be calculated automatically.
Total will be calculated automatically.
Fixed costs are only those costs that stay the same even when unit sales changes. Not all of these items in this list will be fixed for your particular case. Each one needs to be evaluated.
Variable costs are only those costs that change in equilevant terms when sales units change. Not all of these items in this list will be fixed for your particular case. Each one needs to be evaluated. If you use CGS, you will not use the other three. Change titles if needed. Enter the percent of sales.
These costs are a combination of both variable and mixed. For example your cell phone bill has a monthly rent that never changes and a price per minute for usage.
Cost Volume Profit (CVP) analysis allows you to determine how changes in costs, changes in the units(volume), changes in sales or sales units, or changes in variable cost effect the overall profit of the company. Using this model you can adjust these items and see the result on breakeven.

Business Investment Proposal
By :
Jan, 16 2017

Company Summary
Dr. Pepper Snapple Group is a beverage company
The beverage production is categorized into two, that is: Non-carbonated soft drinks and Carbonated soft drinks
It key business activities involve production, marketing and supply

Dr. Pepper Snapple Group, Inc. is soft drink Company based in the United States, and it focuses on the production, marketing, and supply of soft drinks.
The company’s beverage products are categorized in two, the non-carbonated soft drinks and the carbonated beverages that are flavored.
company operates in three segments for it to be able to meet its customers’ demands.
*

Cont.
This company is made up of three Key departments
Soft drinks concentrates department
Packaging department
Latin American soft drinks department
All of these departments work interdependently to achieve mass production

The first segment, the beverage concentrates is accountable for production and sale of the carbonated beverages among other branded syrups and concentrates
The second section which is the packaging segment is responsible for production and distribution of the packaged soft drinks among other products through the direct delivery system to the retail outlets.
The third portion focuses on producing and supplying syrups, concentrates and finished soft drink products.
*

Cont.
Dr. Pepper Snapple Group Company currently holds 14.7% of market shares
This performance places our company at third place after Pepsi-Cola
Pepsi-Cola performs better with about 35.3% of market shares

Dr. Pepper Snapple Group, Inc. holds a market share of 14.7% positioning it in the third place after Pepsi-Cola which has 35.3% of its market share.
Dr. Pepper Snapple Group, Inc. has defined its briefcase efficiently through concentrating on their sales and marketing resources.
The company’s marketing strategy will enable the organization to focus on the various market analyses which will allow the company to identify the impact the critical brands produced and how they could improve them to gain a more significant market share
*

Market Achievement Analysis
This Company has achieved 90% success
80% of potential customers fully satisfied
The least weight loss of an average is described in the market structure graph (below)

This new Investment project produces beverages that have been successful according to a recent research study. 90% of the users have been able to lose at least 15 pounds where 80% of them have been able to maintain the weight outcome. The advantage that we have is that this company has been able to secure its public relation through its success making it easier to adapt to our level of competence. The weight loss is a mark of success of achieving the zero sugar objective – that enhances the inorganic and organic beverages
*

The market demand structure

Through the information gathered from the market, analysis sources show that the company expense will not be affected because the products that the targeted population need are not new products in the market. Also through the third-party distribution plan and the increase of the in-store activity contributes significantly to minimizing the cost of the advertisement.
Supplies on the other hand are more when the prices are higher. This means that it will be Wanda’s wish to make more sales at higher prices, though this is not the case in her speculations. The increase in supply at high price is disadvantaged by the decreasing demand from her products. This then leads to a condition whereby she will always be having surpluses (producing much than she should). Therefore, from this point of reasoning it is advisable that she reduces her selling price and successively increase her sales and maximize her profits at sales per unit demand rather than sales per unit price.
*

Five Year Income Statement
2013 2014 2015 2016 2017
Sales ($)
Beverage A 213,721 359,756 478,992 697,768 896,755
Beverage B 290,314 351,074 467,978 599,654 901,715
Total Sales 504,035 710,830 946,970 1,297,422 1,798,470
Cost of Sales 208,714 390,405 500,750 703,960 900,000
Gross Profit 295,321 320,425 446,220 593,462 898,470
Operating Cost ($)
Advertisement 20,500 20,500 20,500 20,500 50,000
Labour 75,000 75,000 80,000 85,000 100,000
Transportation 50,000 50,000 65,000 70,000 75,000
Total Op. Cost 145,500 145,500 165,500 175,500 215,000

Breakeven Analysis
IRR = (NPV) 0 = P0 + P1/(1+IRR) + P2/(1+IRR)2 + P3/(1+IRR)3 + . . . +Pn/(1+IRR)n
0=937,500+937,500(1+IRR)
0=937,500+937,500+937,500IRR
0=1,875,000+937,500IRR
937,500IRR=-1,875,000
IRR=-2%

The break-even point is calculated by comparing the amount of units that have to be sold in order to cover for the fixed and variable costs. In the case of Dr. Pepper Snapple Group, Inc, the break-even point analysis will be based on the number of units that the company has to sell in order to cover for all expenses.
*

Cont.
NPV = 937,500+(75*12,500) (1-2%)
=937,500+937,500(1-0.02)
=937,500+918750
=1,856,250
Break-even point in units= Fixed Costs/(Sales price per unit-variable cost per unit)
=$100,000/$75-$67
=$100,000/$8
=12,500 units

From the above calculation, there is an indication that the company has to take part in the production, sales and supply activities after this Investment is made. The initial investment capital is regained after the sales of 12, 500 units of beverages each of which are selling at equal sales price.
*

Market Plan Analysis
Begins with understanding the market – get to know taste and preference
Get familiar with consumption trends – in seasons and events
Have multiple sources of income
Clearly identify line of production
Project targeted population size for planning purposes
Have a clear budget

To have an accurate and relevant marketing plan there should be a complete understanding of the market that is targeted by the business. This will help in noticing taste and preference of the potential customers. This will also help in getting familiar with the trend of consumption and which is more helpful in knowing what is consumed when? And in what quantity. This also helps in controlling surplus production that will require another facility like warehouse which increases cost of production.
*

Cont.

Marketing plan needs an analysis of the strengths, weaknesses, opportunities and threats of the firm, market, industry, competition available and the immediate environment. It also needs clearly stated objectives together with the strategies to be used in achieving them. An action plan will help with the ease of flow of activities and forecasting will help in avoiding over expenditure or under expenditure which aids in controlling the whole system of marketing.
*

Location Analysis

The location of a company ought to be conventional in consideration of its general business policy. Therefore, in the case where an organization yearns to take part in leadership under connectivity models, the organization has to bear in mind putting in place storage facilities and branches in areas with coherence to its management strategy and that of their potential consumers. It is advisable that every business managers together with their employees to usually have information on how to located new businesses elsewhere this will help that organization avoid null pieces of advice.
*

Cost of Current Equity
Merits
Demerits
There is no need to repay
The risks experienced is low
Every stock sold for different shareholders means diffusion of ownership
It experiences greater expenses

When you use equity capital, you have no obligation to make interest payments or to repay equity investors’ initial investment. Debt capital, on the other hand, requires periodic interest payments and repayment of the borrowed principal. In general, a business that uses more equity than debt has a lower risk of bankruptcy. If a business suffers a setback and fails to make its interest payments, its creditors can force it into bankruptcy.With every share of stock you sell to investors, you dilute, or reduce, your ownership stake in your small business. Because equity investors typically have the right to vote on important company decisions, you can potentially lose control of your business if you sell too much stock.Although equity does not require interest payments, it typically has a greater overall cost than debt capital. Stockholders shoulder more risk from their perspective compared to creditors because they are last in line to get paid if the company goes bankrupt
*

WACC
Weighted Average Capital Cost
(1-Tc) x Rd x E/V x (d/v) + Re
= 8.5(1-40%) x 24.6/(24.6+82) + 82/(24.6 +82) x 13.34
=1.176 +10.26
= $11.446

Weighted average cost of capital (WACC) is the average after-tax cost of a company’s various capital sources, including common stock, preferred stock, bonds and any other long-term debt. A company has two primary sources of financing – debt and equity – and, in simple terms, WACC is the average cost of raising that money. WACC is calculated by multiplying the cost of each capital source (debt and equity) by its relevant weight, and then adding the products together to determine the WACC value.
*

ROI Analysis
Measure of Rate of Returns
Rate of returns from a project can be measured in two different ways; by using single period calculation or by multiple period calculations. The rate of return involves the overall measurer of whether the investments made in the business are profitable or not. In this project we can measure the rate of re turns using the data in the table.
Single period
Return (R) = Vf – Vi ; rate of returns (r) = R/t
Vi
Where; Vf is the current value, that involves interest,
Vi is the former value and t is a given period of time.

Multiple period =

Planned susceptible access of financial data that will aid in auditing process
Develop proper marketing criteria to increase awareness and sales volume
Endorse innovation and creativity
CONCLUSION:
Generally, a proposal for a project in an organisation can be affected by both internal factors of the organisation like insufficient funds and external factors like explained briefly above. Since the above project has proved to be profitable despite the heavy investments, I qualify it for trial having studied both outcomes over time.
*

Cont.
The calculation of the period of returns (PB) & the Net present Value
 
Return period = Starting Input
period of flow of cash
Total Investments = $900,000
Period of flow of cash = ($800,000/5) =$160,000
= $900,000/$160,000 = 5.626 yrs

NPV= Total Input+ flow of cash/(1+rate of cash flow)
= 142857.17 + 128000 + 114286 + 101910.89 + 90909.1 + 81218.3
= 659181.46 – 400000 = $259181.46

Recommendation
Following the above analysis, there is consideration of the fact that:
This business proposal is likely to produce twice as much return of 2017 in after five years of Investments
As the financial analyst and business advisor of this company, I confidently recommend the funding of the new Investment project that will cost $900,000.
The disbursement of this cash will be done in phases depending on the project schedule to avoid elapse of cash flow.

References
Miles, J. A., & Ezzell, J. R. (2010). The weighted average cost of capital, perfect capital markets, and project life: a clarification. Journal of Financial and Quantitative Analysis, 15(03), 719-730.
Arditti, F. D., & Levy, H. (2013). The weighted average cost of capital as a cut off rate: a critical analysis of the classical textbook weighted average. Financial management, 24-34.
Pricing, I., & Tribunal, R. (2012). Weighted average cost of capital. Zimmerer, T., Scarborough, N. M., & Wilson, D. (2005). Essentials of entrepreneurship and small business management. Pearson/Prentice Hall.
Hodgetts, R. M., Kuratko, D. F., & Kuratko, D. F. (1998). Effective small business management. Fort Worth: Dryden Press.
Pratt, S. P., Reilly, R. F., & Schweihs, R. P. (2000). Valuing a business (p. 45). McGraw-Hill Companies.
Higgins, R. C. (2012). Analysis for financial management. McGraw-Hill/Irwin.

MS60

1

0 Course Project Guidelines

Your course project will consist of a 1

5

–

2

0-slide Microsoft PowerPoint presentation. These slides will help you present your investment idea to the President and CEO of the public company. As such, the slides must be well crafted to help convince the leader of the company of the need for the investment, the possible risks, and potential returns. Remember, the slides should outline the key points to be made and not overwhelm the viewer with too many details. You will provide the details in the speaker notes for each slide. The slide presentation must include:

1.

Cover page

listing the company, project, date, and presenter.

2. Sufficient background so that a potential investor understands the business.

3

. The investment idea and summary justification.

4

. Enough historic data from the worksheet you develop in Modules 3 and 4 to give an investor an understanding of revenues, costs, expenses, cash flows, and potential returns in dollars and using capital budgeting analysis concepts to demonstrate viability.

5. The break-even of the project.

6. Your final analysis summary that details why the company should invest the money in this project.

7. Speaker notes in your Microsoft PowerPoint presentation to include background information that you would communicate verbally in a presentation. This speaker notes content should be the length necessary to explain the outline presented in the slides. Each slide must have the requisite speaker notes to explain the material/data presented in the slides as if you are making a formal presentation and expect to verbalize those words.

This slide presentation is due before the end of class on Day 5 of Module 5 and is worth

25

% of your course final grade or 2

50

points. Combined with the other submitted elements of the project, the total points allocated to this course project will be 500 points or 50% of your grade. The grading of this project will be extensive to match the percentage of course grade. Make sure you provide substantial work in the creating of this project.

Breakdown of Course Project Work

Module	Major Task	Points
1	Select public company and begin planning project.
2	Seek approval of the company, project investment idea, and justification by completing the Project Approval Input in the link provided.				30
3	Begin working on the Excel worksheet provided with the project to outline the revenues, costs, expenses, and resulting cash flows.
4	Submit the final Excel worksheet showing all data and calculations.
5	Submit Microsoft PowerPoint presentation complete with speaker notes before the end of class Day 4.	470

Grading Criteria

30

Calculations are complete and accurate.

30

30

40

50

Assignment Components	Proficient	Max Points
By end of Module 2, complete the Project Approval Input and answer the questions provided.	Selects US public company and provides name and stock symbol. Explains interest in the company and in the investment project.
Excel Worksheet Requirements:
Identify the various revenues, expenses, costs, expenses, and cash flows. If a manufacturing company and investment deals with projects, the analysis breaks down costs into fixed and variable, direct and indirect.	All costs, revenues, expenses, and cash flows required to implement the project are identified, listed, and summed appropriately	180
Calculate the CVP or break-even point for the project.		Calculations are complete and accurate.	15
Calculate NPV and IRR. Provides the numeric viability of the project investment.	25
Slide Presentation Requirements:
Includes a minimum of 15 slides	Each slide is formatted consistently with proper spelling and grammar.
Cover page	Cover page listing the company, project, date, and presenter	10
Company summary	Sufficient written background so that a potential investor understands the business.		40
Data from Excel Worksheet	Enough historic data from the graded worksheet to give an investor an understanding of revenues, costs, expenses, cash flows, and potential returns in dollars and using capital budgeting analysis concepts to demonstrate viability.
Analysis slides	Present the breakeven and other types of analysis for the project.		50
Final recommendations	Provide your final analysis summary that details why the company should invest the money in this project.
Speaker notes on each slide	Speaker notes in your PowerPoint presentation to include background information that you would communicate verbally in a presentation. This background information should be the length necessary to explain the outline presented in the slides. Each slide must have the requisite speaker notes to explain the material/data presented in the slides as if you are making a formal presentation and expect to verbalize those words.
Total:		500

CVP

*

*

*

Sales price per unit

$75.00

*

Variable Cost per unit

$67.00

Fixed Cost

$100,000.00

Targeted Net Income

$0.00

(assume 0 if you want to calculate breakeven)

Calculated Volume

12,500

calculated

* inputted by user

Break-Even Point

=$100,000/$75-$67

=$100,000/$8

=12,500 units

Internal Rate of Return

0=937,500+937,500(1+IRR)

0=937,500+937,500+937,500IRR

0=1,875,000+937,500IRR

937,500IRR=-1,875,000

IRR=-2%

Net Present Value

NPV=-937,500+(75*12,500) (1-2%)

=937,500+937,500(1-0.02)

=937,500+918750

=1,856,250