Explore BrainMass

Investments

Not what you're looking for? Search our solutions OR ask your own Custom question.

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

Please see the attached excel sheet. I tried to solve the problem but I need assistance to complete it. Please explain briefly.

Smallerfacility (X) Larger facility (Y)

Space 10,000 15,000
Rent 100000 150000
1st year weak sales 250,000
1st year succesfull sales 600,000
Probalility for success 0.7
Probalility for failure 0.3
promotion and miscellaneous expenses 30,000 50,000
Tax 28%
Inventory 100,000 200,000
COGS Sales*0.60
Other Variable costs 0.10*sales 0.10*sales
Other fixed costs 40,000 60,000
Required rate of return 10% 10

Computing net income break even point
Total revenue=Total fixed costs+Total Var.costs+NI
Total revenue sales sales
Fixed costs 240,000 410,000
Variable costs 30,000+0.25* sales 50,000+0.10*sales
Total costs 270,000+0.25*sales 460,000+0.10*sales
BES = (sales-0.25 sales=270,000; sales=270,000/0.75) Similarly for larger facility in column D 360000 511111.1111

Is the above answer correct ? Is it the correct approach? Do we need to take COGS too? Please let me know detailed approach/solution

Cash flow calculation
Sales (first year is half of those after the first year) 0.5*Sales
Total costs 270,000+0.25*sales
Pre-tax profit (Sales- Total costs) 270000-0.5*Sales
Tax (270000-0.5*Sales)*0.28
Net Profit (Cash flow) (270000-0.5*Sales)-(270,0000-0.5*sales)*0.28

Annuity Factor 6.14
PV(CF) ((270000-0.5*Sales)-(270,0000-0.5*sales)*0.28)*6.14
NPV (((270000-0.5*Sales)-(270,0000-0.5*sales)*0.28)*6.14)-240,000

Now NPV=0 ; Hence C46=0; Sales can be had from C46

Is the above approach correct ? What changes are required for Larger facility (D column)? Please make changes with detailed solutions