Homework answers / question archive / There is an error in the "CapitalBudget" tab of the spreadsheet

There is an error in the "CapitalBudget" tab of the spreadsheet

Computer Science

Share With

---

There is an error in the "CapitalBudget" tab of the spreadsheet. Cell B5 accidentally has hardcoded the change in NWC where the price of the equipment should be. The price of the equipment should be in Cell B5, and the change in NWC should be in Cell B6. You can simply type these numbers out based on the facts given to you in the case.
 

Ths wil be helphil when yon madre the Hest and Worst Case growl rate scenarios,

‘Numerical Ingots expecied ffumryon ae highlighted in yellow and Forania inction Inguis ave hietliehied in hine.

 

Seep 1:Reat te Ful Case

Onthe “Caimi Budge” Waistert

Step 2:Calatae te veitts of Eqityent Weis of Detrir thefien Use te ancient bond da: poviedin te cee

Step 3:Calatate te Coat of Equity te fren. Use te CAPA and tte Miter dete provided cn onthe Wotahest ‘Mite,

Sep 4:Cabautae the Cost of Der ike the fin Use the inforstetin provited chou te firs bonds to tue te YT

Seep 5:Caloiae te atertex cost of dat Use the sien tm nei te fen

Step &:Use te reais frm ape 2-5 wo cdaubte the WACC (Weigted Ayeram Coat of Canis) ior thefien

Seep 7 Inga te zppnprie Inte! zh Outs

 

DSPORTANT. AL onsh inflows need to be POSITIVE and all cash cnaflows netd to be NEGATIVE.

Seep 2:Iqpu te zppnpree Cah tows fam Operations

Sep Sly tezpnpre Teaie!Cah bes

Sep 10:Compue te Na Cah fons ir Yer 0-4

Sep 11:Compue te PY of Na Cath dows ir Yes 0-4 Chou cnetne we te EXCELiormé or WO or ue te settenetia ioemiz ix PV of: bmp am)

Seep 12:Compue te NPV of te Nat cath dows - Ths canbe cone zs te am of te PV's inSep ll o wig eb NPV orm

 

Now 2 Excd's NPV formnda neads to be aeced by a faster of (t+) - Ref to the module 14 noteson CANVAS for deals

Seep 13:Inicae te Accept Reject decison ior ttesncet fhetycaer

 

Now & Copy and poste the NPV vatvesin calls C38-C30 asyom will nead to ingest the NPV 's fox the 3 soenarios here- DO NOT REFERENCE values.

Seep 14:Compue te NPV or the Beat Cose scaarb by change tte Bat Cae gonth te incafB1] an inticee the Accept Reject Dechin ir hs cern

 

SeeNate t above -Thisisnisere it will behelyghl

Seep 15:Compue te NPV ir the Worst Cae seratio byctargngio the Wont Cae gonth ee incalB11 ant intiaee the Accept Reject Dechai ior ths corer

Onthe "hPWProfile” Wakshea:

Sep 16: Camper the ebb 0 arestes NPV prfit ir the Mos-Lief Scar Tre gaph wil besunmatic ly serasd ir you
Scenari:

 

Veronica Sarkozy, the CFO for the firm PSUWC Desiner Jeans Company, LLC, woke up with a start at 4:00 am

0n 427.2022, due to the phone ringing. Itwas the firme sens financial anatyst, vacationing in Europe, calling with

bad news Veronica was supposed to present the project eva bation, at the end of the week, for the Board's

proposal that they invest in new equipment which would enable them ip add a new product ine. Currently PSUW'C.

has four successfil products and they are c onsilering setting a new Designer Jeans tine.

 

‘The sof of financial ana brats had been working hard over the last w weeks collecting dst and had prepared a

model cresting a financial forec ast about the proposed projects viabilir:

 

Desser had struck on the neh of 4.262022 wherein ra bvare all but wiped our the work of the analysts Veronica

feeded to prepare a financial ana iris of the project to present the Board withrecommendstinns All the staff had

already left for their annual vacation and Veronica was working alone. Veronica quickly reached the offre and

managed to salvage what was left of the excel spreadsheetprepared for the presentation. Vi"hat follows is some

‘basic information that Veroni. a knew and was able @ retreve about the project.

 

PSUWC's existing plant has ex ess capac ity, in a fully depreciated bulking, to install and run the new equipmentto

produce the new Designer Jeans ine. Due w relatively rapid advances in technology, the project was expected to

‘be discontinued infour years.

 

‘The new Designer Jeans was expected eel for $ 110 per unit and had projected sales of 4900 units inthe first

year, witha projeced Qviost-L ikely scenario) 34.0 % growth rate per year for subsequent years. A total investment

of $ 969,000 for new equipment was required. The equipnent hed fred maine nance contracts of $ 344,624 per

year with a aivam vabe of $ 117,110 and variable costs were 7 % of revermes. Veronica also needed  comiler

both the Best-Case and Worst Case scenarios inthe ana lysé with erowthraes of 34.00 % and 2.40 %

respectively.

 

‘The new equipment would be depreciated to zero using stra Bhe tine depreciation. The new project required an

increase in working capital of $ 172,600 and $ 22.438 of this increase would be offet with account payable.

PSUWT currenth has 1175000 shares of siock oustanding at a current price of $ 74.00. Even though the company”

fas copanding stock, itis notpubicly traded and therefore there & no publicty avaiable financial information.

However, after anatysis management believes that its equity beta is 1.41.

 

‘The company aso has 118000 bonds ourstanding. witha current price of $ 1,040.00. The bonds pay inerest em-

annually at a coupon zs of 5.70%. The bonds have a par value of $1,000 and wil raure in 5 years. The sverae

corporate tax rate was 31 4%.

 

Management betiews the S&P 500 is a reasonabe proxy for the marketportiblic. Therefore, the cost of equity is:

calculsed using the company’s equity bet and the market risk premium based on the S&P 500 annual expec ed

fate of retum - Veronica would calculate the monthly expected market ret using 5 years of past monthly price

data available in the worksheet Marke tista. The would then be multiptied by 12 to estimate the annual expec ed

fate. Veronica remembered that if the expected rate of reurnfor the warket vas Oo bw, wo high, or metive.a

forward boking rate of anhetorcal average of about9.5% would have tp be used, a8 the cab ulated valve for the

current 5-year period may not be representative of the firure. Veronica would consider a E(Rix) between 8-12%

accepa bie. Veronica woutl cakulse the mariet rek premium E(Rn) - Rf from the previbus calculations using the

tik-free rae data available in the worksheet Marietista. Veronica noted that the risk-free ra was on an annual

basis

 

Veronica needed to calculate the rate at which the project woul have to be decouned p cakulse the Net Present

Vale (NPY) of the proposed project based on the decision of raising capital and the current captal market

environvent. This discount rae, the WACC, would obviousty influence the NPV and could affect the decision of

whether © accept or reject the project. Tha nidfutl’, afl the information needed to cat ulate this was available.

Veronica needed to clearly show ail the calculations and sources for all parameter estiva tes used in the ca iculation of

the WACC (and utimately the NPV).