Posted: February 17th, 2022

Financial Modeling Exam

  
The next examination is due no later than 9 AM Monday, October 22nd. You’re to e-mail me the examination in an XLSX file named after your self and containing your part. For instance, in case your identify is Leslie King, the file needs to be known as “Leslie.King.A.xlsx”. DO NOT FAIL TO FOLLOW THE INSTRUCTIONS.
The essential arrange for the examination might be discovered within the file: f380.fall18.exam1.information.xlsx. That file comprises 9 worksheets that you’re to finish. Every worksheet is known as (and the quantity in parentheses is the factors allotted to the issue). The next info ought to Help you full the issues:
EARLY [15] Banks usually attempt to discover methods to induce clients to repay loans early. Take into account Premium Financial institution, which affords a +10 mortgage. Principally, the financial institution calculates a mortgage cost within the standard means, then will increase the cost by 10%. For instance, a traditional mortgage for $300,000 at 5% annual curiosity would require 30 years of month-to-month funds of $1,610.46. The financial institution will increase the cost to $1,610.46+ $161.05 = $1,771.51. The result’s that the mortgage is paid off way more shortly. Write a spreadsheet that calculates a standard mortgage cost, will increase it by 10 p.c and supplies a mortgage amortization schedule for the mortgage assuming the client pays the elevated cost month-to-month. The spreadsheet must also report the lifetime of the mortgage (how lengthy it takes the client to repay the mortgage) in months and use solely these cells essential. This spreadsheet ought to work for any mortgage from 1-30 years and requiring month-to-month funds.
FVGA [15] The longer term worth of a always rising annuity is:

The place PMT1 is the primary cost (subsequent yr), g is the expansion price within the annuity, r is the rate of interest earned on funding, and n is the size of the rising annuity. This equation calculates the longer term worth instantly after the final cost. Write a spreadsheet that permits the person to enter an preliminary cost (PMT1, the variety of years, an rate of interest, and a development price. The spreadsheet returns the Future Worth of the Rising Annuity and an amortization schedule. Prohibit n to being lower than or equal to 20 years, and r>g.
RETIRE [15] Write a spreadsheet that permits the person to enter his (or her) age at the moment, wage at the moment, development price in wage (assumed fixed all through his or her working life), preliminary funding in each the bond and inventory fund (which can or is probably not $zero and may differ throughout funds), proportion of wage invested, age at retirement and deliberate age at dying. The spreadsheet permits the person to speculate a portion of his or her wage in two retirement automobiles (or funds) – a bond fund whose return is four% and a inventory fund with a return of 10%. The proportion invested in bonds equals the last decade of the person’s age. For instance, of their 20s (i.e. from 20 by 29), they’ll make investments 20% in bonds and 80% in inventory. Within the 30s, 30% in bonds and 70% in inventory and so forth. Instantly after retirement, ALL FUNDS shall be transferred into an account with a assured fixed return of three% per yr. The funds are to be withdrawn beginning one yr after retirement and ending with the final cost on the age at dying (there needs to be a zero stability after the final withdrawal). The spreadsheet ought to return the annual retirement profit and present that the terminal worth is zero. The spreadsheet ought to work for all ages from 16 to 100 and use solely these cells essential.
BREWSKI [10] This tab comprises information on beer for all fifty states plus the District of Columbia. The variables are :
BCPC: Beer consumption (in gallons), per capita.
INC: Per capita annual revenue
MB: complete variety of microbreweries within the state
TAX: Tax per gallon assessed on beer
FPCT: Proportion of the state’s inhabitants that’s feminine
POP: State inhabitants in tens of millions
TEMP: Imply state temperature
Calculate the variety of microbreweries per million inhabitants. (Name it MBPC). Then, create an indicator (or dummy) variable that takes on a price of 1 if the imply temperature is above 60 levels and nil in any other case (name it TDUM). Run a a number of regression predicting beer consumption per capita (BCPC) as a perform of INC, MBPC, FPCT, and TDUM. Point out the numerous coefficients.
RAISE [10] This worksheet comprises wage ranges for twelve workers at Climate-Tite Home windows. Every worker is slated to obtain a elevate that probably has three elements. First, is an across-the-board price of residing adjustment (COLA) (everyone will get an x% elevate). Second, is an incentive bonus based mostly on productiveness. Every worker is anticipated to supply 10,000-12,000 home windows per yr, however can earn an extra one p.c elevate per 1,000 home windows over 12,000 produced. (For instance, should you produce 17,000 home windows, you’ll get an extra 5% elevate.) Based mostly on previous efficiency, the manufacturing supervisor has supplied estimates of the utmost variety of home windows every employee might produce. Third, every feminine worker MUST obtain an extra elevate of $2,500 to right previous pay inequities. (Assume the gender fairness elevate is NOT affected by the price of residing adjustment). The elevate pool for this yr should not exceed $85,000. Use solver to find out the utmost price of residing adjustment (COLA) the agency can supply. (Word, the motivation raises are based mostly on this coming yr’s salaries, not final yr’s.)
NPV [15] This worksheet comprises money flows after tax for 2 capital budgeting tasks. For every venture, calculate the NPV assuming a reduction price of 12% and the IRR. For every venture, calculate NPVs for entire low cost charges from zero% to 30%, and graph each NPV profiles on the identical graph. You’ll discover that the 2 traces cross. Decide this crossover price – the speed at which the 2 tasks have the identical NPV.
SIM [10] Take into account the perform . Generate 100 integer values of X between -10 and +10, and graph Y as a perform of F. Your graph needs to be dynamic – every calculation (F9) ought to redraw the graph.
AOI [5] Earlier than the Fact in Lending Act, auto sellers used to make use of a trick known as add on curiosity. Suppose you acquire a $30,000 automotive and financed it over 5 years at 6% curiosity. To calculate your cost, they’d take $30,000x5yearsx.06=$9,000. Your month-to-month cost could be ($30,000+$9,000)/12x5years = $650 per 30 days. Write a spreadsheet that permits the person to enter the quantity borrowed, the lifetime of mortgage in years and the annual rate of interest. The spreadsheet calculates the add-on curiosity cost, what the cost needs to be if calculated accurately, and the efficient annual rate of interest on the mortgage if the add-on curiosity cost is used.
WEEK [5] For any mortgage, mortgage size (as much as 30 years) and rate of interest, the spreadsheet calculates month-to-month cost within the peculiar trend. Then, the borrower makes ½ a month-to-month cost each two weeks. (Assume bi-weekly compounding at a price of r/26). Write a spreadsheet that determines how lengthy it takes to repay the mortgage in years.

Order | Check Discount