1. Business
2. Finance
3. Leasing
4. 17667

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
Additional cost choosing X Sales*0.15
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