作者:
Wayne L. Winston
出版社: Duxbury Press
出版年: 24 December, 2002
页数: 953
定价: $110.95
装帧: Hardcover
ISBN: 9780534424350
出版社: Duxbury Press
出版年: 24 December, 2002
页数: 953
定价: $110.95
装帧: Hardcover
ISBN: 9780534424350
豆瓣评分
> 更多短评 1 条
Practical Management Science (with CDROM Update) : Spreadsheet Modeling and Applications的话题 · · · · · · ( 全部 条 )
什么是话题
无论是一部作品、一个人，还是一件事，都往往可以衍生出许多不同的话题。将这些话题细分出来，分别进行讨论，会有更多收获。
Practical Management Science (with CDROM Update) : Spreadsheet Modeling and Applications的书评 · · · · · · ( 全部 0 条 )
读书笔记 · · · · · ·
我来写笔记
大师 (Appreciate the Moment)
Notes and Formulas from [哈佛商学院Harvard Business School] SM Spreadsheet Modeling by Maestro Frank Source Course at: http://hbsp.harvard.edu/list/onlinecourses CH13 “Status Bar”= Quickcheck Tool CH4 Formulas =SUM(E4:E11) =AVERAGE(E4:E11) =PI()*($D$10/2)^2*$D$11*D15 FORMULA: Profit=RevenueCost =Price*Demand Cost ...20120810 09:39 1人喜欢
Notes and Formulas from [哈佛商学院Harvard Business School] SM Spreadsheet Modeling by Maestro Frank Source Course at: http://hbsp.harvard.edu/list/onlinecoursesCH13“Status Bar”= Quickcheck ToolCH4 Formulas =SUM(E4:E11)=AVERAGE(E4:E11)=PI()*($D$10/2)^2*$D$11*D15FORMULA: Profit=RevenueCost =Price*Demand Cost =Price*Demand (PerCost*Demand +Fixed Cost) =(PricePerCost)*Demand Fixed CostPrice*Demand (PerCost*Demand +Fixed Cost)=(7000200*$D10)*$D10($E$3+(7000200*$D10)*E$9) =(7000200*$D10)*$D10$E$3(7000200*$D10)*E$9(PricePerCost)*Demand Fixed Cost =(7000200*$D10)*($D10*E$9)$E$3CH5 Useful FunctionsIF StatementsIF(logical test,[value if true],[value if false])IF(,,)=IF(E2<C2,B2,(1D2)*B2)=IF((E2C2)>=0,B2*(1D2),B2)IF(,,IF(,,)) Nested IF statement=IF(G2<C2,B2,IF(G2<E2,(1D2)*B2,(1F2)*B2))IF(AND(,),,IF(,,))=IF(AND(E2="x",G2>=C2),(1F2)*B2,IF(G2>=C2,(1D2)*B2,B2))Notice: We need to place x in quotes because it is text.OR(,)NOT()Ex. Currently we charge $30 for a Sacher Tort and our competitor charges $25. If our competitor charges below $20 we will lower our price to $25. Write a formula which returns our price after we enter the competitor's price in the spreadsheet.=IF(B5<20,25,30)Ex. In the game of craps two dice are rolled with a total between 2 and 12 resulting. A 7 or 11 on first roll results in a win, a total of 2, 3 or 12 results in a loss, and otherwise the game continues. Write a formula that returns the game status after the first two dice are rolled.=IF(OR(D11=7,D11=11),"WIN",IF(OR(D11=2,D11=3,D11=12),"LOSS","CONTINUE"))=IF(OR(C8=2,C8=3,C8=12),"loss",IF(OR(C8=7,C8=11),"win","continue")) Notice: 1. Use OR(,), NOT AND(,)! 2. Do not forget type: D11=Text FunctionsEg. Extracts the numbersFIND(text to find, cell to find text, starting character)Find 1st Space in E3=FIND(" ",E3,1) LEFT(cell to find text, num_chars)Extracts the left hand most characters (4  1 = 3) =LEFT(E3,F31)Convert text to valueValue()Eg. Extracts the last name =FIND(" ",D7,1) finds the location of the space=LEN(D7) computes the number of charactersRIGHT (cell to find text, num chars)=RIGHT(D7,F7E7) Extracts the last nameNote: F7E7 computes the number of characters in the cell less the location of the space. Not F7E71Eg. Extracts the middle name =FIND(" ",D7,1) finds the position of the 1st space=FIND(" ",D7,E7+1) finds the 2nd space in her name.Note this works because we start one position (E7+1) after the first space.MID (cell to find text, start num, num chars)=MID(D7,E7+1,F7E71) start one character after the first space[in cell E7] and extract all characters between the first and second space[ in cell D7 to cell G7].Ex. The sheet Movies gives the number of copies of each movie in stock at a video store. Put the name of the movie in one column and the number of copies in the next column.=FIND(" ",B3,1)=RIGHT(B3,LEN(B3)C3)=VALUE(LEFT(B3,C31))Ex. The sheet ID gives a product ID, followed by a product name followed by a product price. Extract the product name to a separate column. The price always is expressed using 4 characters.=FIND(" ",B4,1)=LEN(B4)=MID(B4, G4+1, H45G4)[Data Tab] >> [Text to Columns]Eg. email=G7&"@Napoleon.com"In sheet addresses combine the street address, state, and zip code into a single cell.=B3&" "&C3&" "&D3Date and TimeEx. Extract the year, day of week, day of month, and month.YEAR(date) returns the date's year.MONTH(date) returns the date's month.WEEKDAY(date,2) returns the day of the week for the date, with 1 = Monday, 2 = Tuesday, ...7 = Sunday.DAY(date) returns the date's day of the month.Ex. Extract the hour, minute and second.HOUR(time) returns the hour for the given time.MINUTE(time) returns the number of full minutes after the hour for the given time.SECOND(time) returns the number of seconds for the given time.=DATE(Year, Month, Day)=Time(Hour, Minute, Second)=TODAY() returns the current date=NOW() returns the current date and time.August 15, 2009, is 40,040 days after January 1, 1900. 5:40 PM is 74% of the way between midnight and the start of the next day.Range Names[Formulas Tab] >> [Create from Selection][Formulas Tab] >> [Name Book][F3] =Paste Name[F5] =Go To[Formulas Tab] >> [Define Name]>[ Apply Names]LookupVLOOKUP(lookup value, table range, column index) //Note: V stands for ‘vertical’ =VLOOKUP(D11,lookcost,2) //true means ‘Approximate Match’=VLOOKUP(A20,lookprice,2,false) // false means ‘Exact Match’=HLOOKUP(B6,dateprice,2)Ex. Rows 312 give the age and salary of some of your friends. In rows 1518 write formulas to enter each person's age and salary.=VLOOKUP(E15,$E$2:$G$12,2,FALSE)=VLOOKUP(E15,$E$2:$G$12,3,FALSE)Ex. When a customer orders < 100 Pear Charlottes, that customer pays $25 per cake. When the order is between 100 and 199 Pear Charlottes, they pay $23.00 per cake. If they order at least 200 Pear Charlottes the customer pays $20.00 per cake. Write a formula that will compute the cost of ordering any number of Pear Charlottes.Order No. Price0 $ 25.00 100 $ 23.00 200 $ 20.00 =VLOOKUP(J8,$J$3:$K$5,2)Index and Match=INDEX($A$2: $E$15,13,2)=MATCH("Pear Charlotte",$F$2: $F$15,0) //0 means ‘Exact Match’, 1 means ‘Less Than’, 1 ‘Greater Than’ Notice: Pear Charlotte is in row 10 of the worksheet but row 9 of the lookup range so Excel returns a 9. Note: Pear Charlotte is in " " because it is text.Ex. The worksheet Indexmatchhw.xlsx contains our employees names and salaries. Write a formula that would return the name of the employee with the largest salary. Hint: The MAX function returns the largest number in a column.=MAX(B5:B12)Ex. Suppose we can enter an employee's name in cell D1. Write a formula in cell E12 that returns the employee's salary.=INDEX(B5:B12,MATCH(D1,C5:C12,0),1)Error TrappingIFERROR(excel, formula, desired message)=IFERROR(E9*F9,"_")Ex. File Errortrap.xlsx contains in columns A and B prices at which homes were bought and sold. If a home has not yet been sold, the sell price column says not sold yet. In column C compute the profit earned on each sold house. If a house has not been sold, your formula should say not sold yet.=IFERROR(B4A4,"not sold yet")Rounding• ROUND(number, n) rounds the number to n significant digits. If there is a tie on which number to round to, Excel rounds to the highest number. For example ROUND(3.5,0) yields 4. Also ROUND(3.4,0)+ returns 3 and ROUND(3.6,0) returns 4.• INT(number) rounds the number down to the nearest integer. For example INT(3.4) returns 3 and INT(4.7) returns 5.• ROUNDUP(number, n) rounds the number up (away from 0) using n significant digits. Using n = 0 results in the number being rounded up to the nearest integer. For example, ROUNDUP(3.2,0) returns 4 and ROUNDUP(PI(), 2) returns 3.15.• ROUNDDOWN(number, n) rounds the number down (toward 0) using n significant digits. Using n = 0 results in the number being rounded down to the nearest integer. For example, ROUNDDOWN(3.2,0) returns 3 while ROUNDOWN(PI(), 2) returns 3.14.• For positive numbers there is no difference between the ROUNDDOWN and INT function. For example INT(4.8) = ROUNDDOWN(4.8,0) = 4. For negative numbers, however, the ROUNDDOWN and INT functions may yield different results. For example INT(4.3) = 5 but ROUNDDOWN(4.3,0) = 4.=INT(D17/30)=VLOOKUP(E17,$D$11:$E$14,2)Ex. This file gives the dates when several houses were bought and sold. Use rounding functions to compute the number of full years each house was owned before it was sold. Assume 365 days = 1 year. For example, a house kept for 700 days was kept for one year. Hint: Subtracting dates gives the number of days between the dates. =ROUNDDOWN((E4D4)/365,0)Ex. Compute the number of years, rounded off to the nearest integer, for which each house was owned.=ROUND ((E4D4)/365,0)Sumproduct=SUMPRODUCT(D4:D11,E4:E11) computes 49*10 + 36*13Ex. Determine (with a single formula) the total number of cakes produced.=SUMPRODUCT(D4:D11,E4:E11)Ex. Determine (with a single formula) the total revenue generated by the produced cakes.=SUMPRODUCT(D4:D11,E4:E11,F4:F11)Ex. Determine (with a single formula) the total cost of buying sugar, butter, and flour from our 6 suppliers.=SUMPRODUCT(D17:F22,D26:F31)Random=RAND() generates a value that is equally likely to be anywhere between 0 and 1.Eg. There is a 30% chance that demand for Sacher Tortes today will be for 3 tortes, a 50% chance demand for Sacher Tortes will be for 4 tortes, and there is a 20% chance that today's demand will be for 5 Sacher Tortes.Answer: Entering in cell D5 the formula =RAND() generates a number between 0 and 1. = IF(D5<0.3,3,IF(D5<0.8,4,5))Notice: 0.8=30%+50%Ex. There is a 60% chance the demand will equal 4 and a 40% chance the demand will equal 8.=RAND()=IF(D10<0.6,4,8) =RAND()=VLOOKUP(D5,B4:C5,2)=RANDBETWEEN(100,200) each integer between 100 and 200 has an equal chance (1/101) of appearing.Financial Functions [Formulas Tab] >> [Insert Function] >> select Category [Financial]=XNPV(rate,B3:E3,$B$2:$E$2)Net Present Value (NPV) converts all cash flows to today's dollars by: cash * (1/(1+discount rate)^n )Internal Rate of Return (IRR) the discount rate that makes a project's NPV =0. For example if we discount a project's cash flows at 12% and get an NPV of 0, we can say this project, roughly speaking, is earning a 12% annual return on our investments. The relationship between IRR and NPV is as follows: NPV>0 if and only if Project IRR>discount rate.Payment PMT(rate, nper,pv,fv,type)• Rate is the annual rate on the loan. If periods are years, this equals the annual rate. If periods are months, this is the annual rate divided by 12.• Nper is the number of equal payments needed to pay back loan.• PV is the amount borrowed. PV should be entered with a positive sign since we will receive a check for the amount of the loan.• FV is the amount of the ending payment needed to pay off the loan. If there is no ending payment, then FV=0. FV should be entered with a negative sign since we are paying money.• Use type =1 or True if the loan payments are made at the beginning of the month. Use type = 0 or false if loan payments are made at the end of the month. PV function: computes the value of an annuity. FV function: computes the future accumulation when a constant sum of money is deposited each period. RATE function: computes the effective rate given number of payments, size of each payment, and amount borrowed. NPER function: computes the number of periods required to pay back a loan given the rate, size of payments, and amount borrowed. IPMT, PPMT, CUMPRINC and CUMIPMT: these functions help us calculate an amortization schedule which determines the breakdown each period between principal and interest.Ex. An NBA player will be a rookie in November 2010. Each year for six years he is paid $1 million a month during the months of November—April till December 2011. Assuming a discount rate of 10% per year, what is the NPV of his salaries as of January 15, 2008?1/15/2008 11/15/2010 12/15/2010 1/15/2011 2/15/2011 3/15/2011 4/15/2011 11/15/2011 12/15/20110 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 =XNPV(0.1, B5:J5, B4:J4)Ex. An oil company is going to drill a well costing $50 million on 5/15/2009. For each of the next 20 years they expect to receive $5 million in revenues. Assuming each year's revenue is concentrated midyear, what is the IRR for this oil well?=XIRR($B$19:$V$19,$B$18:$V$18,E$20)=XIRR(B9:V9,B8:V8)What is the monthly payment 20year loan with monthly payments and a 6% annual rate under the following three conditions:• EndofMonth payments =PMT(C4/12,C3,C5,0,0)• Beginningofmonth payments =PMT(C4/12,C3,C5,0,1)• Endofmonth payments with an ending $30,000 payment =PMT(C4/12, C3, C5, 30000,0)Ex. What is the monthly payment (endofmonth payments) on a 30year and a 8% annual rate of $300,000 loan?=PMT(0.08/12,30*12,300000,0,0)Ex. What is the annual depreciation we can charge on a $4000 machine with a salvage value of $500 and a 5year useful life?=SLN(4000,500,5)Counting COUNT(cell range) counts how many cells in cell range containing numbers. COUNTA(cell range) counts how many cells in cell range are not blank (contain text or numbers). COUNTBLANK(cell range) counts how many cells in cell range are blank.Statistical Functions[Formulas Tab] >> [Insert Function] >> select Category [Statistical] =MAX(cell_range) returns the largest number in a cell range. =MIN(cell_range) computes the smallest number in a cell range. =LARGE(cell_range, k) returns the kth largest number in a cell range. =SMALL(cell_range, k) returns the kth smallest number in a cell range. =AVERAGE(cell_range) computes the average of all numbers in a cell range. =VAR(cell_range) returns the variance [329.41 dollars2] of the numbers in a cell range. =STDEV(cell_range) returns the standard deviation [18.15 dollars] of the numbers in a cell range. =RANK(E5, cell range,0) returns the rank of a number k in a cell range 0 refers to descending: the largest number obtaining a rank of 1; 1 to ascending: the smallest number is rank of 1.Conditional Counting=COUNTIF(Location, "SOUTH") sales in South=COUNTIF(Units, 84) sales for 84 units=COUNTIF(Revenue,">=200") sales for >=$200Note that >=200 is enclosed in " " because >= is text.=COUNTIFS(Location, "SOUTH", Salesperson, "CHARLENE") sales in south by Charlene=COUNTIFS(Month, "APRIL", Revenue, ">700") sales in April for >$700Ex. How many transactions involved at least 50 units and were completed by Charlene in November for Napoleons?=COUNTIFS(Units, ">=50", Salesperson, "CHARLENE", Month, "NOVEMBER", Product, "Napoleon (piece)")Conditional Sums=SUMIF(Month, H4, Revenue)=SUMIFS(Units, Salesperson, $H18, Location,I$17)Ex. Compute the total units and revenue for each location.=SUMIF(Location, H32, Units)=SUMIF(Location, H32, Revenue)Ex. Summarize by month the total revenue generated by each salesperson.=SUMIFS(Revenue, Salesperson, $H39, Month, I$38)Remove Duplicates[Data Tab] >> [Remove Duplicates] >> select () Continue with the Current SelectionSorting[Data Tab] >> [Sort] Filtering Data[Data Tab] >> [Filter]Note: If we select [Data Tab] >> [Filter] again, then the dropdown arrows are cleared and our original data returns. Subtotal[Data Tab] >> [Subtotal]Ex. For each month, obtain a breakdown of the units and revenue for each product.Note: month is the main sort, product is the second sort.Pivot Tables and Pivot Chart[Insert Tab] >> [Pivot Table]Format Values: Right click inside the pivot table  choose ()[Show Field List]. Right click on [Sum of Revenues] >> [Value Field Setting] >> [Number format]Average Values: Right click on [Sum of Revenues] >> [Value Field Setting] >> [Average]Show as Percentage: Right click on [Sum of Revenues] >> [Value Field Setting] >> [Show Values As] Tab >> [Percentage of Column]Note: Filter can be used in Pivot Tables[Insert Tab] >> [Pivot Table]> [Pivot Chart][Home Tab] >> [Conditional Formatting]> [New Rule…] > [Use a formula to determine which cells to format]=F8>F7=E12=MAX(E$12:E$15) test whether E12 is the largest number in its column. =E12=MAX($E12:$H12) highlights in red for each region the product with the largest sales. CH6 Charts[Insert Tab]CH7 Importing Data into Excel[Open] data.txt[Data Tab] >> [From Web]CH8 Spreadsheet ModelingRules to Guide Intelligent ModelingLayout: Spreadsheet inputs and decision variables should never be hardcoded in formulas; inputs and decision variables should always be entered in their own cells. A common layout structure is to place model inputs and decision variables on the top of a worksheet, followed by worksheet calculations. The model outputs may then be placed below the calculations or to the right of the model inputs and decision variables. Colors: Yellow for Inputs Blue for Decision Variables Orange for Calculations Gray for OutputsUse Influence Charts: to help understand Model RelationshipsUse Range Names:For formula =A50*B100, this formula would look something like price*demand, which is much easier to understand. Notice: Be wary of using range names in a multiworksheet workbook. Successful use of range names in multiworksheet workbooks often requires knowledge of advanced concepts such as sheet level worksheet names.Documentation: Comments or Annotations in cells Text BoxesUse [Copy Cross] to efficiently create formulas: Notice: When copying formulas, make sure your use of absolute and relative addressing (the $ signs discussed in an earlier chapter) is correct.Test: A model should also be tested for sensibility. For example, if the unit cost of producing a pastry increases, we would expect Le Napoleon's NPV from a new bakery to decrease and the profit maximizing price to increase. [Conditional formatting] can be used to clarify a sensitivity analysis. For example, we can use conditional formatting to highlight the profitmaximizing price for different unit costs of producing a pastry. This is useful because if we change other inputs or decision variables, then the red formatting will still indicate the NPV maximizing price. Using Excel's Goal Seek feature, we can easily determine the value of each input that would make the project NPV =0. This helps us determine how sensitive our final decision is to changes in each input. Error Trapping: use IFERROR (or often an IF statement) to inform the model user of his or her input error. [Formulas Tab] >> [Error Checking] [Formulas Tab] >> [Evaluate Formulas]Understand your model's limitationsInfluence Charts [Insert Tab] >> [Shapes] Year t Profit = Year t Revenue  Year t Total Cost Year t Revenue = (Year t units sold)*(Year t Price) Year t Units Sold = Minimum (Capacity, Year t Demand) Year t + 1 Demand = (Year t Demand)*(Year t + 1 Demand Growth rate) Year t + 1 Unit Cost = (Year t Unit cost)*(Year t + 1 Unit Cost Growth Rate)Set up the Model Year t Profit = Year t Revenue  Year t Total Cost In cell D18 we compute the cost of building the bakery by entering the formula =Plant_fixed_cost + Cost_per_unit_of_capacity * Annual_Capacity. In row 25 copying from cell E25 to the range F25:N25 the formula =E23 * E21 computes the total cost incurred during each year of sales. In cell D26 we compute our Year 2012 profit as simply the negative of the bakery building cost. Copying from cell E26 to the cell range F26:N26 the formula =E24  E25 computes profit during each year of bakery operation as each year's revenues less variable costs. Year t Revenue = (Year t units sold)*(Year t Price) In cell E22 we copy the Year 1 price from cell D5. Then by copying from cell F22 to the range G22:N22 the formula =E22*(1 + Annual_price_rate_of_increase) we ensure that our price grows at the assumed rate of 5% per year. In row 24 copying from cell E24 to the range F24:N24 the formula =E22 * E21 computes revenue during each year of operation as the product of sales price and units sold. Year t Units Sold = Minimum (Capacity, Year t Demand) Copying from E21 to the cell range F21:N21 the formula =MIN(E20, Annual_Capacity) ensures that during each year we sell the minimum of bakery capacity and demand. Year t + 1 Demand = (Year t Demand)*(Year t + 1 Demand Growth rate) Next we enter the 2013 demand as 420000  60000 * price by entering in cell E20 the formula =Year_1_demand_intercept  Year_1_demand_slope * D5. In cell F19 we compute the 2014 demand growth with the formula =IF(F16 <= Years_till_demand_steady_state + 1, Beginning_growth_rate, Steady_state_growth_rate). As long as we are in the fast growth phase, demand grows by 15%. After the fast growth phase is over, demand grows by 5%. Copying this formula to the range G19:N19 generates the growth rate in demand through 2022. Entering in cell F20 the formula =E20 * (1 + F19) computes Year 2014 demand as Year 2014 growth rate * Year 2013 demand. Copying this formula to the cell range G20:N20 computes the demand for all later years. Year t + 1 Unit Cost = (Year t Unit cost)*(Year t + 1 Unit Cost Growth Rate) In cell E23 we copy the Year 1 Unit cost of producing a pastry from cell D13. Then copying from F23 to G23:N23 the formula = E23 * (1 + Annual_unit_cost_rate_of_increase) ensures that during each year unit costs grow at the assumed rate of 6% per year. Finally in cell B29 we use the function wizard to enter the formula =XNPV(Discount_rate, D26:N26, D17:N17). We find that (as of the first date on the list 1/1/2012) our assumptions imply the bakery will create $1,016,044 of value for Le Napoleon. This NPV is computed as of the first cash flow's date, 1/1/2012.WhatIF Data Tables [Data Tab] >> [WhatIF Analysis] >> [Data Table…] [Formulas Tab] >> [Calculation Options] Automatic  This setting is the usual setting. All formulas and data tables recalculate when data or formulas are entered into the spreadsheet. Automatic Except for Tables  If you choose this setting, then your formulas will recalculate whenever you enter data or formulas into your spreadsheet. Your data tables will not recalculate unless you hit the F9 key. This setting is useful in large spreadsheets because in large spreadsheets recalculation of data tables can be time consuming. Manual  When Calculation Mode is on Manual, no formulas or data table will recalculate unless you hit the F9 key. Manual calculation is useful in a large spreadsheet, because in large spreadsheets recalculation of formulas can be time consuming. Remember that in Excel 2007 a spreadsheet can have up to 1,048,576 rows and up to 16.394 columns.WhatIF Goal Seek [Data Tab] >> [WhatIF Analysis] >> [Goal Seek …]Auditing Trace Dependents [Formulas Tab] >> [Trace Dependents] [Formulas Tab] >> [Trace Precedents] Flexibility=IF(F17<=Years_of_Operation, MIN(F21,Annual_Capacity), "No Operation")Comment and Text Box [Review Tab] >> [New Comment] [Insert Tab] >> [Shapes]> [Text Box]Error Trapping 1. If price is high enough, we might see the unrealistic occurrence of a negative demand. To remedy this problem, we will modify our year 1 demand formula to ensure that negative year 1 demand cannot occur. = MAX(Year_1_demand_interceptYear_1_demand_slope*D5,0) //return the usual demand; unless negative demand will return 0. 2. Clearly the years of high growth must not exceed the number of years of operation. We will use an IF statement to ensure that if this condition is violated, the user is informed of that fact. = IF(Years_till_steady_state+1>Years_of_Operation, "Years till steady state cannot exceed years of operation",""). //If we have entered a value for Years of High Growth which exceeds Years of operation cell, B8 will return the error message "Years till steady state cannot exceed years of operation." Otherwise cell B8 will remain blank. 3. Number of years of operation should not be allowed to exceed 20. We will use DATA VALIDATION to ensure that this is the case. Using Data Validation, we can prevent the entry of a number >20 in cell D16. [Data Tab] >> [Data Validation…] Using [F9] and [Evaluate Formulas] Select part of a formula and press [F9] [Formulas Tab] >> [Evaluate Formulas]Scenarios 1. [Data Tab] >> [Data Validation…] > Settings Tab: Change “Whole Number” to “List”2. Draw a lookup table3. = VLOOKUP(D1,lookup,2,FALSE)Spinner [Options] >> [Customize Ribbon] > check “Developer Tab” [Developer] >> [Insert]> [Spin] Right click [Spinner] > [Format Control]Limitations Here are some limitations of our model: We have assumed a linear demand curve. Often the relation between price and demand is not linear. The twostage growth model may not be accurate. To test the validity of the twostage growth model we could check whether the demand growth for past bakery openings has followed the twostage growth model. Our model for building cost as a function of capacity may not be accurate. Modelers often assume that building a plant with capacity x incurs a cost of a+ bxk., where 0<k<1. This type of model ensures that the cost of adding additional units of capacity decreases as more units are added. Finally, none of our parameters are known with certainty. If we are highly uncertain about our model inputs, we should use Monte Carlo Simulation to model the probability distribution of each input and generate a range of possible NPV's. Then we could make statements such as "I am 80% sure the bakery will have a positive NPV." CH9 Excel Solver: optimization problems [Data Tab] >> [Solver] Objective: ProfitBy changing Variables: $B$5:$B$18Constraints: $B$5:$B$18 = integer //A fractional number of a product is not acceptable. $B$5:$B$18 <= $I$5:$I$18 //Do not make more of a product than customers are willing to buy. $E$19:$G$19 <= $E$3:$G$3 //Cannot use more baker time, flour, or sugar than what we have available.Profit = SUMPRODUCT(H5:H18,B5:B18)CH10 3D Formulas, Excel Table and Circular ReferencesConsolidate 3D Formulas=SUM(Jan:May!J6)Excel Table Select the cell range F3:G17 and select [Control] + [T].Ex. Enter a formula in cell H12 that will compute sales for any product. The formula should work if new products are added to the product list. The pie chart should update automatically if new product data is entered into the worksheet.=RANK([@Units],[Units],0) //0 refers descending; 1 refers ascending.This formula may be read as ranking the Units entry in the current row with respect to the Units column.Circular References [Options] >> [Formulas] > check “Enable Iterative Calculation”CH11 Monte Carlo Simulation@Risk Addin Software: http://www.palisade.com/bookdownloads/booksoftware.asp?isbn=0000000003252 The key to building an accurate Monte Carlo simulation model of a situation is to determine an appropriate random variable to represent each source of uncertainty in the real life situation. There are two types of random variables: discrete and continuous random variables.=RISKDISCRETE(E8:E11,F8:F11) A continuous random variable has a zero probability of assuming any particular value. For example, there is a probability 0 that a randomly selected person is exactly 6 feet tall. To see this note that to be exactly 6 feet tall a person's height would have to be 6.00000000000000000000000000000000000000 (there should really be an infinite number of 0's here!) feet tall, and the probability that a person's height exactly matches this number is to all intents and purposes zero. We model a continuous random variable by defining a PROBABILITY DENSITY FUNCTION (pdf) for the random variable. The total area under a pdf must equal 1. In our development we will use two simple continuous random variables: the Uniform and Triangular random variable. A uniform random variable (written U[a, b]) is simply a random variable that is equally likely to assume any value between a and b. We can model a U[a, b] random variable with the @risk function:= RISKUNIFORM(50,200) // Note the density is uniform or constant everywhere on the interval [50, 200]. The triangular random variable has become popular because it generalizes the common practice of analyzing a business case by creating a best case, worst case, and most likely scenario. The triangular random variable simply allows for the possibility that any value between the best and worst case has a chance of happening.=RISKTRIANG(40000,60000,70000) Eg. Le Napoleon is trying to decide whether or not to introduce a new pastry to compete with similar offerings of other companies. Three key quantities associated with this decision are highly uncertain:• Fixed cost of developing the pastry. We will assume that there is a 50% chance that the fixed cost is $10,000; a 30% chance the fixed cost will be $30,000; and a 20% chance the fixed cost will be $50,000. [Fixed Cost]=RiskDiscrete(H5:H7,I5:I7).• The best case for annual market size of the new pastry is 70,000 units; the worst case is 40,000 units, and the most likely case is 60,000 units, [Market Size] =RiskTriang(40000,60000,70000)• Le Napoleon's market share of the new pastry is equally likely to be anywhere between 20% and 50%. [Market share] =RiskUniform(0.2,0.5) We see that fixed cost can be modeled as a discrete random variable (using the RISKDISCRETE function), market size can be modeled as a triangular random variable (using RISKTRIANG function), while market share can be modeled as a uniform random variable (using RISKUNIFORM function). [Units Sold]=ROUND(D8*D9,0) [Revenue]=Units_Sold*Price [Variable Cost]=Units_Sold*Unit_Cost [Profit]=RevenueVariable_CostFixed_Cost. Move the cursor to the [Profit] cell (D13) and then choose the [Add Output] icon. This makes cell D13 an Output cell and @RISK will keep detailed statistics about this cell. Next, change the number of [iterations] to 1000. This is equivalent to hitting the F9 key 1000 times and having @RISK track the resulting profits for each trial or "iteration. [@RISK Tab] >> [Start Simulation]回应 20120810 09:39

大师 (Appreciate the Moment)
Notes and Formulas from [哈佛商学院Harvard Business School] SM Spreadsheet Modeling by Maestro Frank Source Course at: http://hbsp.harvard.edu/list/onlinecourses CH13 “Status Bar”= Quickcheck Tool CH4 Formulas =SUM(E4:E11) =AVERAGE(E4:E11) =PI()*($D$10/2)^2*$D$11*D15 FORMULA: Profit=RevenueCost =Price*Demand Cost ...20120810 09:39 1人喜欢
Notes and Formulas from [哈佛商学院Harvard Business School] SM Spreadsheet Modeling by Maestro Frank Source Course at: http://hbsp.harvard.edu/list/onlinecoursesCH13“Status Bar”= Quickcheck ToolCH4 Formulas =SUM(E4:E11)=AVERAGE(E4:E11)=PI()*($D$10/2)^2*$D$11*D15FORMULA: Profit=RevenueCost =Price*Demand Cost =Price*Demand (PerCost*Demand +Fixed Cost) =(PricePerCost)*Demand Fixed CostPrice*Demand (PerCost*Demand +Fixed Cost)=(7000200*$D10)*$D10($E$3+(7000200*$D10)*E$9) =(7000200*$D10)*$D10$E$3(7000200*$D10)*E$9(PricePerCost)*Demand Fixed Cost =(7000200*$D10)*($D10*E$9)$E$3CH5 Useful FunctionsIF StatementsIF(logical test,[value if true],[value if false])IF(,,)=IF(E2<C2,B2,(1D2)*B2)=IF((E2C2)>=0,B2*(1D2),B2)IF(,,IF(,,)) Nested IF statement=IF(G2<C2,B2,IF(G2<E2,(1D2)*B2,(1F2)*B2))IF(AND(,),,IF(,,))=IF(AND(E2="x",G2>=C2),(1F2)*B2,IF(G2>=C2,(1D2)*B2,B2))Notice: We need to place x in quotes because it is text.OR(,)NOT()Ex. Currently we charge $30 for a Sacher Tort and our competitor charges $25. If our competitor charges below $20 we will lower our price to $25. Write a formula which returns our price after we enter the competitor's price in the spreadsheet.=IF(B5<20,25,30)Ex. In the game of craps two dice are rolled with a total between 2 and 12 resulting. A 7 or 11 on first roll results in a win, a total of 2, 3 or 12 results in a loss, and otherwise the game continues. Write a formula that returns the game status after the first two dice are rolled.=IF(OR(D11=7,D11=11),"WIN",IF(OR(D11=2,D11=3,D11=12),"LOSS","CONTINUE"))=IF(OR(C8=2,C8=3,C8=12),"loss",IF(OR(C8=7,C8=11),"win","continue")) Notice: 1. Use OR(,), NOT AND(,)! 2. Do not forget type: D11=Text FunctionsEg. Extracts the numbersFIND(text to find, cell to find text, starting character)Find 1st Space in E3=FIND(" ",E3,1) LEFT(cell to find text, num_chars)Extracts the left hand most characters (4  1 = 3) =LEFT(E3,F31)Convert text to valueValue()Eg. Extracts the last name =FIND(" ",D7,1) finds the location of the space=LEN(D7) computes the number of charactersRIGHT (cell to find text, num chars)=RIGHT(D7,F7E7) Extracts the last nameNote: F7E7 computes the number of characters in the cell less the location of the space. Not F7E71Eg. Extracts the middle name =FIND(" ",D7,1) finds the position of the 1st space=FIND(" ",D7,E7+1) finds the 2nd space in her name.Note this works because we start one position (E7+1) after the first space.MID (cell to find text, start num, num chars)=MID(D7,E7+1,F7E71) start one character after the first space[in cell E7] and extract all characters between the first and second space[ in cell D7 to cell G7].Ex. The sheet Movies gives the number of copies of each movie in stock at a video store. Put the name of the movie in one column and the number of copies in the next column.=FIND(" ",B3,1)=RIGHT(B3,LEN(B3)C3)=VALUE(LEFT(B3,C31))Ex. The sheet ID gives a product ID, followed by a product name followed by a product price. Extract the product name to a separate column. The price always is expressed using 4 characters.=FIND(" ",B4,1)=LEN(B4)=MID(B4, G4+1, H45G4)[Data Tab] >> [Text to Columns]Eg. email=G7&"@Napoleon.com"In sheet addresses combine the street address, state, and zip code into a single cell.=B3&" "&C3&" "&D3Date and TimeEx. Extract the year, day of week, day of month, and month.YEAR(date) returns the date's year.MONTH(date) returns the date's month.WEEKDAY(date,2) returns the day of the week for the date, with 1 = Monday, 2 = Tuesday, ...7 = Sunday.DAY(date) returns the date's day of the month.Ex. Extract the hour, minute and second.HOUR(time) returns the hour for the given time.MINUTE(time) returns the number of full minutes after the hour for the given time.SECOND(time) returns the number of seconds for the given time.=DATE(Year, Month, Day)=Time(Hour, Minute, Second)=TODAY() returns the current date=NOW() returns the current date and time.August 15, 2009, is 40,040 days after January 1, 1900. 5:40 PM is 74% of the way between midnight and the start of the next day.Range Names[Formulas Tab] >> [Create from Selection][Formulas Tab] >> [Name Book][F3] =Paste Name[F5] =Go To[Formulas Tab] >> [Define Name]>[ Apply Names]LookupVLOOKUP(lookup value, table range, column index) //Note: V stands for ‘vertical’ =VLOOKUP(D11,lookcost,2) //true means ‘Approximate Match’=VLOOKUP(A20,lookprice,2,false) // false means ‘Exact Match’=HLOOKUP(B6,dateprice,2)Ex. Rows 312 give the age and salary of some of your friends. In rows 1518 write formulas to enter each person's age and salary.=VLOOKUP(E15,$E$2:$G$12,2,FALSE)=VLOOKUP(E15,$E$2:$G$12,3,FALSE)Ex. When a customer orders < 100 Pear Charlottes, that customer pays $25 per cake. When the order is between 100 and 199 Pear Charlottes, they pay $23.00 per cake. If they order at least 200 Pear Charlottes the customer pays $20.00 per cake. Write a formula that will compute the cost of ordering any number of Pear Charlottes.Order No. Price0 $ 25.00 100 $ 23.00 200 $ 20.00 =VLOOKUP(J8,$J$3:$K$5,2)Index and Match=INDEX($A$2: $E$15,13,2)=MATCH("Pear Charlotte",$F$2: $F$15,0) //0 means ‘Exact Match’, 1 means ‘Less Than’, 1 ‘Greater Than’ Notice: Pear Charlotte is in row 10 of the worksheet but row 9 of the lookup range so Excel returns a 9. Note: Pear Charlotte is in " " because it is text.Ex. The worksheet Indexmatchhw.xlsx contains our employees names and salaries. Write a formula that would return the name of the employee with the largest salary. Hint: The MAX function returns the largest number in a column.=MAX(B5:B12)Ex. Suppose we can enter an employee's name in cell D1. Write a formula in cell E12 that returns the employee's salary.=INDEX(B5:B12,MATCH(D1,C5:C12,0),1)Error TrappingIFERROR(excel, formula, desired message)=IFERROR(E9*F9,"_")Ex. File Errortrap.xlsx contains in columns A and B prices at which homes were bought and sold. If a home has not yet been sold, the sell price column says not sold yet. In column C compute the profit earned on each sold house. If a house has not been sold, your formula should say not sold yet.=IFERROR(B4A4,"not sold yet")Rounding• ROUND(number, n) rounds the number to n significant digits. If there is a tie on which number to round to, Excel rounds to the highest number. For example ROUND(3.5,0) yields 4. Also ROUND(3.4,0)+ returns 3 and ROUND(3.6,0) returns 4.• INT(number) rounds the number down to the nearest integer. For example INT(3.4) returns 3 and INT(4.7) returns 5.• ROUNDUP(number, n) rounds the number up (away from 0) using n significant digits. Using n = 0 results in the number being rounded up to the nearest integer. For example, ROUNDUP(3.2,0) returns 4 and ROUNDUP(PI(), 2) returns 3.15.• ROUNDDOWN(number, n) rounds the number down (toward 0) using n significant digits. Using n = 0 results in the number being rounded down to the nearest integer. For example, ROUNDDOWN(3.2,0) returns 3 while ROUNDOWN(PI(), 2) returns 3.14.• For positive numbers there is no difference between the ROUNDDOWN and INT function. For example INT(4.8) = ROUNDDOWN(4.8,0) = 4. For negative numbers, however, the ROUNDDOWN and INT functions may yield different results. For example INT(4.3) = 5 but ROUNDDOWN(4.3,0) = 4.=INT(D17/30)=VLOOKUP(E17,$D$11:$E$14,2)Ex. This file gives the dates when several houses were bought and sold. Use rounding functions to compute the number of full years each house was owned before it was sold. Assume 365 days = 1 year. For example, a house kept for 700 days was kept for one year. Hint: Subtracting dates gives the number of days between the dates. =ROUNDDOWN((E4D4)/365,0)Ex. Compute the number of years, rounded off to the nearest integer, for which each house was owned.=ROUND ((E4D4)/365,0)Sumproduct=SUMPRODUCT(D4:D11,E4:E11) computes 49*10 + 36*13Ex. Determine (with a single formula) the total number of cakes produced.=SUMPRODUCT(D4:D11,E4:E11)Ex. Determine (with a single formula) the total revenue generated by the produced cakes.=SUMPRODUCT(D4:D11,E4:E11,F4:F11)Ex. Determine (with a single formula) the total cost of buying sugar, butter, and flour from our 6 suppliers.=SUMPRODUCT(D17:F22,D26:F31)Random=RAND() generates a value that is equally likely to be anywhere between 0 and 1.Eg. There is a 30% chance that demand for Sacher Tortes today will be for 3 tortes, a 50% chance demand for Sacher Tortes will be for 4 tortes, and there is a 20% chance that today's demand will be for 5 Sacher Tortes.Answer: Entering in cell D5 the formula =RAND() generates a number between 0 and 1. = IF(D5<0.3,3,IF(D5<0.8,4,5))Notice: 0.8=30%+50%Ex. There is a 60% chance the demand will equal 4 and a 40% chance the demand will equal 8.=RAND()=IF(D10<0.6,4,8) =RAND()=VLOOKUP(D5,B4:C5,2)=RANDBETWEEN(100,200) each integer between 100 and 200 has an equal chance (1/101) of appearing.Financial Functions [Formulas Tab] >> [Insert Function] >> select Category [Financial]=XNPV(rate,B3:E3,$B$2:$E$2)Net Present Value (NPV) converts all cash flows to today's dollars by: cash * (1/(1+discount rate)^n )Internal Rate of Return (IRR) the discount rate that makes a project's NPV =0. For example if we discount a project's cash flows at 12% and get an NPV of 0, we can say this project, roughly speaking, is earning a 12% annual return on our investments. The relationship between IRR and NPV is as follows: NPV>0 if and only if Project IRR>discount rate.Payment PMT(rate, nper,pv,fv,type)• Rate is the annual rate on the loan. If periods are years, this equals the annual rate. If periods are months, this is the annual rate divided by 12.• Nper is the number of equal payments needed to pay back loan.• PV is the amount borrowed. PV should be entered with a positive sign since we will receive a check for the amount of the loan.• FV is the amount of the ending payment needed to pay off the loan. If there is no ending payment, then FV=0. FV should be entered with a negative sign since we are paying money.• Use type =1 or True if the loan payments are made at the beginning of the month. Use type = 0 or false if loan payments are made at the end of the month. PV function: computes the value of an annuity. FV function: computes the future accumulation when a constant sum of money is deposited each period. RATE function: computes the effective rate given number of payments, size of each payment, and amount borrowed. NPER function: computes the number of periods required to pay back a loan given the rate, size of payments, and amount borrowed. IPMT, PPMT, CUMPRINC and CUMIPMT: these functions help us calculate an amortization schedule which determines the breakdown each period between principal and interest.Ex. An NBA player will be a rookie in November 2010. Each year for six years he is paid $1 million a month during the months of November—April till December 2011. Assuming a discount rate of 10% per year, what is the NPV of his salaries as of January 15, 2008?1/15/2008 11/15/2010 12/15/2010 1/15/2011 2/15/2011 3/15/2011 4/15/2011 11/15/2011 12/15/20110 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 =XNPV(0.1, B5:J5, B4:J4)Ex. An oil company is going to drill a well costing $50 million on 5/15/2009. For each of the next 20 years they expect to receive $5 million in revenues. Assuming each year's revenue is concentrated midyear, what is the IRR for this oil well?=XIRR($B$19:$V$19,$B$18:$V$18,E$20)=XIRR(B9:V9,B8:V8)What is the monthly payment 20year loan with monthly payments and a 6% annual rate under the following three conditions:• EndofMonth payments =PMT(C4/12,C3,C5,0,0)• Beginningofmonth payments =PMT(C4/12,C3,C5,0,1)• Endofmonth payments with an ending $30,000 payment =PMT(C4/12, C3, C5, 30000,0)Ex. What is the monthly payment (endofmonth payments) on a 30year and a 8% annual rate of $300,000 loan?=PMT(0.08/12,30*12,300000,0,0)Ex. What is the annual depreciation we can charge on a $4000 machine with a salvage value of $500 and a 5year useful life?=SLN(4000,500,5)Counting COUNT(cell range) counts how many cells in cell range containing numbers. COUNTA(cell range) counts how many cells in cell range are not blank (contain text or numbers). COUNTBLANK(cell range) counts how many cells in cell range are blank.Statistical Functions[Formulas Tab] >> [Insert Function] >> select Category [Statistical] =MAX(cell_range) returns the largest number in a cell range. =MIN(cell_range) computes the smallest number in a cell range. =LARGE(cell_range, k) returns the kth largest number in a cell range. =SMALL(cell_range, k) returns the kth smallest number in a cell range. =AVERAGE(cell_range) computes the average of all numbers in a cell range. =VAR(cell_range) returns the variance [329.41 dollars2] of the numbers in a cell range. =STDEV(cell_range) returns the standard deviation [18.15 dollars] of the numbers in a cell range. =RANK(E5, cell range,0) returns the rank of a number k in a cell range 0 refers to descending: the largest number obtaining a rank of 1; 1 to ascending: the smallest number is rank of 1.Conditional Counting=COUNTIF(Location, "SOUTH") sales in South=COUNTIF(Units, 84) sales for 84 units=COUNTIF(Revenue,">=200") sales for >=$200Note that >=200 is enclosed in " " because >= is text.=COUNTIFS(Location, "SOUTH", Salesperson, "CHARLENE") sales in south by Charlene=COUNTIFS(Month, "APRIL", Revenue, ">700") sales in April for >$700Ex. How many transactions involved at least 50 units and were completed by Charlene in November for Napoleons?=COUNTIFS(Units, ">=50", Salesperson, "CHARLENE", Month, "NOVEMBER", Product, "Napoleon (piece)")Conditional Sums=SUMIF(Month, H4, Revenue)=SUMIFS(Units, Salesperson, $H18, Location,I$17)Ex. Compute the total units and revenue for each location.=SUMIF(Location, H32, Units)=SUMIF(Location, H32, Revenue)Ex. Summarize by month the total revenue generated by each salesperson.=SUMIFS(Revenue, Salesperson, $H39, Month, I$38)Remove Duplicates[Data Tab] >> [Remove Duplicates] >> select () Continue with the Current SelectionSorting[Data Tab] >> [Sort] Filtering Data[Data Tab] >> [Filter]Note: If we select [Data Tab] >> [Filter] again, then the dropdown arrows are cleared and our original data returns. Subtotal[Data Tab] >> [Subtotal]Ex. For each month, obtain a breakdown of the units and revenue for each product.Note: month is the main sort, product is the second sort.Pivot Tables and Pivot Chart[Insert Tab] >> [Pivot Table]Format Values: Right click inside the pivot table  choose ()[Show Field List]. Right click on [Sum of Revenues] >> [Value Field Setting] >> [Number format]Average Values: Right click on [Sum of Revenues] >> [Value Field Setting] >> [Average]Show as Percentage: Right click on [Sum of Revenues] >> [Value Field Setting] >> [Show Values As] Tab >> [Percentage of Column]Note: Filter can be used in Pivot Tables[Insert Tab] >> [Pivot Table]> [Pivot Chart][Home Tab] >> [Conditional Formatting]> [New Rule…] > [Use a formula to determine which cells to format]=F8>F7=E12=MAX(E$12:E$15) test whether E12 is the largest number in its column. =E12=MAX($E12:$H12) highlights in red for each region the product with the largest sales. CH6 Charts[Insert Tab]CH7 Importing Data into Excel[Open] data.txt[Data Tab] >> [From Web]CH8 Spreadsheet ModelingRules to Guide Intelligent ModelingLayout: Spreadsheet inputs and decision variables should never be hardcoded in formulas; inputs and decision variables should always be entered in their own cells. A common layout structure is to place model inputs and decision variables on the top of a worksheet, followed by worksheet calculations. The model outputs may then be placed below the calculations or to the right of the model inputs and decision variables. Colors: Yellow for Inputs Blue for Decision Variables Orange for Calculations Gray for OutputsUse Influence Charts: to help understand Model RelationshipsUse Range Names:For formula =A50*B100, this formula would look something like price*demand, which is much easier to understand. Notice: Be wary of using range names in a multiworksheet workbook. Successful use of range names in multiworksheet workbooks often requires knowledge of advanced concepts such as sheet level worksheet names.Documentation: Comments or Annotations in cells Text BoxesUse [Copy Cross] to efficiently create formulas: Notice: When copying formulas, make sure your use of absolute and relative addressing (the $ signs discussed in an earlier chapter) is correct.Test: A model should also be tested for sensibility. For example, if the unit cost of producing a pastry increases, we would expect Le Napoleon's NPV from a new bakery to decrease and the profit maximizing price to increase. [Conditional formatting] can be used to clarify a sensitivity analysis. For example, we can use conditional formatting to highlight the profitmaximizing price for different unit costs of producing a pastry. This is useful because if we change other inputs or decision variables, then the red formatting will still indicate the NPV maximizing price. Using Excel's Goal Seek feature, we can easily determine the value of each input that would make the project NPV =0. This helps us determine how sensitive our final decision is to changes in each input. Error Trapping: use IFERROR (or often an IF statement) to inform the model user of his or her input error. [Formulas Tab] >> [Error Checking] [Formulas Tab] >> [Evaluate Formulas]Understand your model's limitationsInfluence Charts [Insert Tab] >> [Shapes] Year t Profit = Year t Revenue  Year t Total Cost Year t Revenue = (Year t units sold)*(Year t Price) Year t Units Sold = Minimum (Capacity, Year t Demand) Year t + 1 Demand = (Year t Demand)*(Year t + 1 Demand Growth rate) Year t + 1 Unit Cost = (Year t Unit cost)*(Year t + 1 Unit Cost Growth Rate)Set up the Model Year t Profit = Year t Revenue  Year t Total Cost In cell D18 we compute the cost of building the bakery by entering the formula =Plant_fixed_cost + Cost_per_unit_of_capacity * Annual_Capacity. In row 25 copying from cell E25 to the range F25:N25 the formula =E23 * E21 computes the total cost incurred during each year of sales. In cell D26 we compute our Year 2012 profit as simply the negative of the bakery building cost. Copying from cell E26 to the cell range F26:N26 the formula =E24  E25 computes profit during each year of bakery operation as each year's revenues less variable costs. Year t Revenue = (Year t units sold)*(Year t Price) In cell E22 we copy the Year 1 price from cell D5. Then by copying from cell F22 to the range G22:N22 the formula =E22*(1 + Annual_price_rate_of_increase) we ensure that our price grows at the assumed rate of 5% per year. In row 24 copying from cell E24 to the range F24:N24 the formula =E22 * E21 computes revenue during each year of operation as the product of sales price and units sold. Year t Units Sold = Minimum (Capacity, Year t Demand) Copying from E21 to the cell range F21:N21 the formula =MIN(E20, Annual_Capacity) ensures that during each year we sell the minimum of bakery capacity and demand. Year t + 1 Demand = (Year t Demand)*(Year t + 1 Demand Growth rate) Next we enter the 2013 demand as 420000  60000 * price by entering in cell E20 the formula =Year_1_demand_intercept  Year_1_demand_slope * D5. In cell F19 we compute the 2014 demand growth with the formula =IF(F16 <= Years_till_demand_steady_state + 1, Beginning_growth_rate, Steady_state_growth_rate). As long as we are in the fast growth phase, demand grows by 15%. After the fast growth phase is over, demand grows by 5%. Copying this formula to the range G19:N19 generates the growth rate in demand through 2022. Entering in cell F20 the formula =E20 * (1 + F19) computes Year 2014 demand as Year 2014 growth rate * Year 2013 demand. Copying this formula to the cell range G20:N20 computes the demand for all later years. Year t + 1 Unit Cost = (Year t Unit cost)*(Year t + 1 Unit Cost Growth Rate) In cell E23 we copy the Year 1 Unit cost of producing a pastry from cell D13. Then copying from F23 to G23:N23 the formula = E23 * (1 + Annual_unit_cost_rate_of_increase) ensures that during each year unit costs grow at the assumed rate of 6% per year. Finally in cell B29 we use the function wizard to enter the formula =XNPV(Discount_rate, D26:N26, D17:N17). We find that (as of the first date on the list 1/1/2012) our assumptions imply the bakery will create $1,016,044 of value for Le Napoleon. This NPV is computed as of the first cash flow's date, 1/1/2012.WhatIF Data Tables [Data Tab] >> [WhatIF Analysis] >> [Data Table…] [Formulas Tab] >> [Calculation Options] Automatic  This setting is the usual setting. All formulas and data tables recalculate when data or formulas are entered into the spreadsheet. Automatic Except for Tables  If you choose this setting, then your formulas will recalculate whenever you enter data or formulas into your spreadsheet. Your data tables will not recalculate unless you hit the F9 key. This setting is useful in large spreadsheets because in large spreadsheets recalculation of data tables can be time consuming. Manual  When Calculation Mode is on Manual, no formulas or data table will recalculate unless you hit the F9 key. Manual calculation is useful in a large spreadsheet, because in large spreadsheets recalculation of formulas can be time consuming. Remember that in Excel 2007 a spreadsheet can have up to 1,048,576 rows and up to 16.394 columns.WhatIF Goal Seek [Data Tab] >> [WhatIF Analysis] >> [Goal Seek …]Auditing Trace Dependents [Formulas Tab] >> [Trace Dependents] [Formulas Tab] >> [Trace Precedents] Flexibility=IF(F17<=Years_of_Operation, MIN(F21,Annual_Capacity), "No Operation")Comment and Text Box [Review Tab] >> [New Comment] [Insert Tab] >> [Shapes]> [Text Box]Error Trapping 1. If price is high enough, we might see the unrealistic occurrence of a negative demand. To remedy this problem, we will modify our year 1 demand formula to ensure that negative year 1 demand cannot occur. = MAX(Year_1_demand_interceptYear_1_demand_slope*D5,0) //return the usual demand; unless negative demand will return 0. 2. Clearly the years of high growth must not exceed the number of years of operation. We will use an IF statement to ensure that if this condition is violated, the user is informed of that fact. = IF(Years_till_steady_state+1>Years_of_Operation, "Years till steady state cannot exceed years of operation",""). //If we have entered a value for Years of High Growth which exceeds Years of operation cell, B8 will return the error message "Years till steady state cannot exceed years of operation." Otherwise cell B8 will remain blank. 3. Number of years of operation should not be allowed to exceed 20. We will use DATA VALIDATION to ensure that this is the case. Using Data Validation, we can prevent the entry of a number >20 in cell D16. [Data Tab] >> [Data Validation…] Using [F9] and [Evaluate Formulas] Select part of a formula and press [F9] [Formulas Tab] >> [Evaluate Formulas]Scenarios 1. [Data Tab] >> [Data Validation…] > Settings Tab: Change “Whole Number” to “List”2. Draw a lookup table3. = VLOOKUP(D1,lookup,2,FALSE)Spinner [Options] >> [Customize Ribbon] > check “Developer Tab” [Developer] >> [Insert]> [Spin] Right click [Spinner] > [Format Control]Limitations Here are some limitations of our model: We have assumed a linear demand curve. Often the relation between price and demand is not linear. The twostage growth model may not be accurate. To test the validity of the twostage growth model we could check whether the demand growth for past bakery openings has followed the twostage growth model. Our model for building cost as a function of capacity may not be accurate. Modelers often assume that building a plant with capacity x incurs a cost of a+ bxk., where 0<k<1. This type of model ensures that the cost of adding additional units of capacity decreases as more units are added. Finally, none of our parameters are known with certainty. If we are highly uncertain about our model inputs, we should use Monte Carlo Simulation to model the probability distribution of each input and generate a range of possible NPV's. Then we could make statements such as "I am 80% sure the bakery will have a positive NPV." CH9 Excel Solver: optimization problems [Data Tab] >> [Solver] Objective: ProfitBy changing Variables: $B$5:$B$18Constraints: $B$5:$B$18 = integer //A fractional number of a product is not acceptable. $B$5:$B$18 <= $I$5:$I$18 //Do not make more of a product than customers are willing to buy. $E$19:$G$19 <= $E$3:$G$3 //Cannot use more baker time, flour, or sugar than what we have available.Profit = SUMPRODUCT(H5:H18,B5:B18)CH10 3D Formulas, Excel Table and Circular ReferencesConsolidate 3D Formulas=SUM(Jan:May!J6)Excel Table Select the cell range F3:G17 and select [Control] + [T].Ex. Enter a formula in cell H12 that will compute sales for any product. The formula should work if new products are added to the product list. The pie chart should update automatically if new product data is entered into the worksheet.=RANK([@Units],[Units],0) //0 refers descending; 1 refers ascending.This formula may be read as ranking the Units entry in the current row with respect to the Units column.Circular References [Options] >> [Formulas] > check “Enable Iterative Calculation”CH11 Monte Carlo Simulation@Risk Addin Software: http://www.palisade.com/bookdownloads/booksoftware.asp?isbn=0000000003252 The key to building an accurate Monte Carlo simulation model of a situation is to determine an appropriate random variable to represent each source of uncertainty in the real life situation. There are two types of random variables: discrete and continuous random variables.=RISKDISCRETE(E8:E11,F8:F11) A continuous random variable has a zero probability of assuming any particular value. For example, there is a probability 0 that a randomly selected person is exactly 6 feet tall. To see this note that to be exactly 6 feet tall a person's height would have to be 6.00000000000000000000000000000000000000 (there should really be an infinite number of 0's here!) feet tall, and the probability that a person's height exactly matches this number is to all intents and purposes zero. We model a continuous random variable by defining a PROBABILITY DENSITY FUNCTION (pdf) for the random variable. The total area under a pdf must equal 1. In our development we will use two simple continuous random variables: the Uniform and Triangular random variable. A uniform random variable (written U[a, b]) is simply a random variable that is equally likely to assume any value between a and b. We can model a U[a, b] random variable with the @risk function:= RISKUNIFORM(50,200) // Note the density is uniform or constant everywhere on the interval [50, 200]. The triangular random variable has become popular because it generalizes the common practice of analyzing a business case by creating a best case, worst case, and most likely scenario. The triangular random variable simply allows for the possibility that any value between the best and worst case has a chance of happening.=RISKTRIANG(40000,60000,70000) Eg. Le Napoleon is trying to decide whether or not to introduce a new pastry to compete with similar offerings of other companies. Three key quantities associated with this decision are highly uncertain:• Fixed cost of developing the pastry. We will assume that there is a 50% chance that the fixed cost is $10,000; a 30% chance the fixed cost will be $30,000; and a 20% chance the fixed cost will be $50,000. [Fixed Cost]=RiskDiscrete(H5:H7,I5:I7).• The best case for annual market size of the new pastry is 70,000 units; the worst case is 40,000 units, and the most likely case is 60,000 units, [Market Size] =RiskTriang(40000,60000,70000)• Le Napoleon's market share of the new pastry is equally likely to be anywhere between 20% and 50%. [Market share] =RiskUniform(0.2,0.5) We see that fixed cost can be modeled as a discrete random variable (using the RISKDISCRETE function), market size can be modeled as a triangular random variable (using RISKTRIANG function), while market share can be modeled as a uniform random variable (using RISKUNIFORM function). [Units Sold]=ROUND(D8*D9,0) [Revenue]=Units_Sold*Price [Variable Cost]=Units_Sold*Unit_Cost [Profit]=RevenueVariable_CostFixed_Cost. Move the cursor to the [Profit] cell (D13) and then choose the [Add Output] icon. This makes cell D13 an Output cell and @RISK will keep detailed statistics about this cell. Next, change the number of [iterations] to 1000. This is equivalent to hitting the F9 key 1000 times and having @RISK track the resulting profits for each trial or "iteration. [@RISK Tab] >> [Start Simulation]回应 20120810 09:39

大师 (Appreciate the Moment)
Notes and Formulas from [哈佛商学院Harvard Business School] SM Spreadsheet Modeling by Maestro Frank Source Course at: http://hbsp.harvard.edu/list/onlinecourses CH13 “Status Bar”= Quickcheck Tool CH4 Formulas =SUM(E4:E11) =AVERAGE(E4:E11) =PI()*($D$10/2)^2*$D$11*D15 FORMULA: Profit=RevenueCost =Price*Demand Cost ...20120810 09:39 1人喜欢
Notes and Formulas from [哈佛商学院Harvard Business School] SM Spreadsheet Modeling by Maestro Frank Source Course at: http://hbsp.harvard.edu/list/onlinecoursesCH13“Status Bar”= Quickcheck ToolCH4 Formulas =SUM(E4:E11)=AVERAGE(E4:E11)=PI()*($D$10/2)^2*$D$11*D15FORMULA: Profit=RevenueCost =Price*Demand Cost =Price*Demand (PerCost*Demand +Fixed Cost) =(PricePerCost)*Demand Fixed CostPrice*Demand (PerCost*Demand +Fixed Cost)=(7000200*$D10)*$D10($E$3+(7000200*$D10)*E$9) =(7000200*$D10)*$D10$E$3(7000200*$D10)*E$9(PricePerCost)*Demand Fixed Cost =(7000200*$D10)*($D10*E$9)$E$3CH5 Useful FunctionsIF StatementsIF(logical test,[value if true],[value if false])IF(,,)=IF(E2<C2,B2,(1D2)*B2)=IF((E2C2)>=0,B2*(1D2),B2)IF(,,IF(,,)) Nested IF statement=IF(G2<C2,B2,IF(G2<E2,(1D2)*B2,(1F2)*B2))IF(AND(,),,IF(,,))=IF(AND(E2="x",G2>=C2),(1F2)*B2,IF(G2>=C2,(1D2)*B2,B2))Notice: We need to place x in quotes because it is text.OR(,)NOT()Ex. Currently we charge $30 for a Sacher Tort and our competitor charges $25. If our competitor charges below $20 we will lower our price to $25. Write a formula which returns our price after we enter the competitor's price in the spreadsheet.=IF(B5<20,25,30)Ex. In the game of craps two dice are rolled with a total between 2 and 12 resulting. A 7 or 11 on first roll results in a win, a total of 2, 3 or 12 results in a loss, and otherwise the game continues. Write a formula that returns the game status after the first two dice are rolled.=IF(OR(D11=7,D11=11),"WIN",IF(OR(D11=2,D11=3,D11=12),"LOSS","CONTINUE"))=IF(OR(C8=2,C8=3,C8=12),"loss",IF(OR(C8=7,C8=11),"win","continue")) Notice: 1. Use OR(,), NOT AND(,)! 2. Do not forget type: D11=Text FunctionsEg. Extracts the numbersFIND(text to find, cell to find text, starting character)Find 1st Space in E3=FIND(" ",E3,1) LEFT(cell to find text, num_chars)Extracts the left hand most characters (4  1 = 3) =LEFT(E3,F31)Convert text to valueValue()Eg. Extracts the last name =FIND(" ",D7,1) finds the location of the space=LEN(D7) computes the number of charactersRIGHT (cell to find text, num chars)=RIGHT(D7,F7E7) Extracts the last nameNote: F7E7 computes the number of characters in the cell less the location of the space. Not F7E71Eg. Extracts the middle name =FIND(" ",D7,1) finds the position of the 1st space=FIND(" ",D7,E7+1) finds the 2nd space in her name.Note this works because we start one position (E7+1) after the first space.MID (cell to find text, start num, num chars)=MID(D7,E7+1,F7E71) start one character after the first space[in cell E7] and extract all characters between the first and second space[ in cell D7 to cell G7].Ex. The sheet Movies gives the number of copies of each movie in stock at a video store. Put the name of the movie in one column and the number of copies in the next column.=FIND(" ",B3,1)=RIGHT(B3,LEN(B3)C3)=VALUE(LEFT(B3,C31))Ex. The sheet ID gives a product ID, followed by a product name followed by a product price. Extract the product name to a separate column. The price always is expressed using 4 characters.=FIND(" ",B4,1)=LEN(B4)=MID(B4, G4+1, H45G4)[Data Tab] >> [Text to Columns]Eg. email=G7&"@Napoleon.com"In sheet addresses combine the street address, state, and zip code into a single cell.=B3&" "&C3&" "&D3Date and TimeEx. Extract the year, day of week, day of month, and month.YEAR(date) returns the date's year.MONTH(date) returns the date's month.WEEKDAY(date,2) returns the day of the week for the date, with 1 = Monday, 2 = Tuesday, ...7 = Sunday.DAY(date) returns the date's day of the month.Ex. Extract the hour, minute and second.HOUR(time) returns the hour for the given time.MINUTE(time) returns the number of full minutes after the hour for the given time.SECOND(time) returns the number of seconds for the given time.=DATE(Year, Month, Day)=Time(Hour, Minute, Second)=TODAY() returns the current date=NOW() returns the current date and time.August 15, 2009, is 40,040 days after January 1, 1900. 5:40 PM is 74% of the way between midnight and the start of the next day.Range Names[Formulas Tab] >> [Create from Selection][Formulas Tab] >> [Name Book][F3] =Paste Name[F5] =Go To[Formulas Tab] >> [Define Name]>[ Apply Names]LookupVLOOKUP(lookup value, table range, column index) //Note: V stands for ‘vertical’ =VLOOKUP(D11,lookcost,2) //true means ‘Approximate Match’=VLOOKUP(A20,lookprice,2,false) // false means ‘Exact Match’=HLOOKUP(B6,dateprice,2)Ex. Rows 312 give the age and salary of some of your friends. In rows 1518 write formulas to enter each person's age and salary.=VLOOKUP(E15,$E$2:$G$12,2,FALSE)=VLOOKUP(E15,$E$2:$G$12,3,FALSE)Ex. When a customer orders < 100 Pear Charlottes, that customer pays $25 per cake. When the order is between 100 and 199 Pear Charlottes, they pay $23.00 per cake. If they order at least 200 Pear Charlottes the customer pays $20.00 per cake. Write a formula that will compute the cost of ordering any number of Pear Charlottes.Order No. Price0 $ 25.00 100 $ 23.00 200 $ 20.00 =VLOOKUP(J8,$J$3:$K$5,2)Index and Match=INDEX($A$2: $E$15,13,2)=MATCH("Pear Charlotte",$F$2: $F$15,0) //0 means ‘Exact Match’, 1 means ‘Less Than’, 1 ‘Greater Than’ Notice: Pear Charlotte is in row 10 of the worksheet but row 9 of the lookup range so Excel returns a 9. Note: Pear Charlotte is in " " because it is text.Ex. The worksheet Indexmatchhw.xlsx contains our employees names and salaries. Write a formula that would return the name of the employee with the largest salary. Hint: The MAX function returns the largest number in a column.=MAX(B5:B12)Ex. Suppose we can enter an employee's name in cell D1. Write a formula in cell E12 that returns the employee's salary.=INDEX(B5:B12,MATCH(D1,C5:C12,0),1)Error TrappingIFERROR(excel, formula, desired message)=IFERROR(E9*F9,"_")Ex. File Errortrap.xlsx contains in columns A and B prices at which homes were bought and sold. If a home has not yet been sold, the sell price column says not sold yet. In column C compute the profit earned on each sold house. If a house has not been sold, your formula should say not sold yet.=IFERROR(B4A4,"not sold yet")Rounding• ROUND(number, n) rounds the number to n significant digits. If there is a tie on which number to round to, Excel rounds to the highest number. For example ROUND(3.5,0) yields 4. Also ROUND(3.4,0)+ returns 3 and ROUND(3.6,0) returns 4.• INT(number) rounds the number down to the nearest integer. For example INT(3.4) returns 3 and INT(4.7) returns 5.• ROUNDUP(number, n) rounds the number up (away from 0) using n significant digits. Using n = 0 results in the number being rounded up to the nearest integer. For example, ROUNDUP(3.2,0) returns 4 and ROUNDUP(PI(), 2) returns 3.15.• ROUNDDOWN(number, n) rounds the number down (toward 0) using n significant digits. Using n = 0 results in the number being rounded down to the nearest integer. For example, ROUNDDOWN(3.2,0) returns 3 while ROUNDOWN(PI(), 2) returns 3.14.• For positive numbers there is no difference between the ROUNDDOWN and INT function. For example INT(4.8) = ROUNDDOWN(4.8,0) = 4. For negative numbers, however, the ROUNDDOWN and INT functions may yield different results. For example INT(4.3) = 5 but ROUNDDOWN(4.3,0) = 4.=INT(D17/30)=VLOOKUP(E17,$D$11:$E$14,2)Ex. This file gives the dates when several houses were bought and sold. Use rounding functions to compute the number of full years each house was owned before it was sold. Assume 365 days = 1 year. For example, a house kept for 700 days was kept for one year. Hint: Subtracting dates gives the number of days between the dates. =ROUNDDOWN((E4D4)/365,0)Ex. Compute the number of years, rounded off to the nearest integer, for which each house was owned.=ROUND ((E4D4)/365,0)Sumproduct=SUMPRODUCT(D4:D11,E4:E11) computes 49*10 + 36*13Ex. Determine (with a single formula) the total number of cakes produced.=SUMPRODUCT(D4:D11,E4:E11)Ex. Determine (with a single formula) the total revenue generated by the produced cakes.=SUMPRODUCT(D4:D11,E4:E11,F4:F11)Ex. Determine (with a single formula) the total cost of buying sugar, butter, and flour from our 6 suppliers.=SUMPRODUCT(D17:F22,D26:F31)Random=RAND() generates a value that is equally likely to be anywhere between 0 and 1.Eg. There is a 30% chance that demand for Sacher Tortes today will be for 3 tortes, a 50% chance demand for Sacher Tortes will be for 4 tortes, and there is a 20% chance that today's demand will be for 5 Sacher Tortes.Answer: Entering in cell D5 the formula =RAND() generates a number between 0 and 1. = IF(D5<0.3,3,IF(D5<0.8,4,5))Notice: 0.8=30%+50%Ex. There is a 60% chance the demand will equal 4 and a 40% chance the demand will equal 8.=RAND()=IF(D10<0.6,4,8) =RAND()=VLOOKUP(D5,B4:C5,2)=RANDBETWEEN(100,200) each integer between 100 and 200 has an equal chance (1/101) of appearing.Financial Functions [Formulas Tab] >> [Insert Function] >> select Category [Financial]=XNPV(rate,B3:E3,$B$2:$E$2)Net Present Value (NPV) converts all cash flows to today's dollars by: cash * (1/(1+discount rate)^n )Internal Rate of Return (IRR) the discount rate that makes a project's NPV =0. For example if we discount a project's cash flows at 12% and get an NPV of 0, we can say this project, roughly speaking, is earning a 12% annual return on our investments. The relationship between IRR and NPV is as follows: NPV>0 if and only if Project IRR>discount rate.Payment PMT(rate, nper,pv,fv,type)• Rate is the annual rate on the loan. If periods are years, this equals the annual rate. If periods are months, this is the annual rate divided by 12.• Nper is the number of equal payments needed to pay back loan.• PV is the amount borrowed. PV should be entered with a positive sign since we will receive a check for the amount of the loan.• FV is the amount of the ending payment needed to pay off the loan. If there is no ending payment, then FV=0. FV should be entered with a negative sign since we are paying money.• Use type =1 or True if the loan payments are made at the beginning of the month. Use type = 0 or false if loan payments are made at the end of the month. PV function: computes the value of an annuity. FV function: computes the future accumulation when a constant sum of money is deposited each period. RATE function: computes the effective rate given number of payments, size of each payment, and amount borrowed. NPER function: computes the number of periods required to pay back a loan given the rate, size of payments, and amount borrowed. IPMT, PPMT, CUMPRINC and CUMIPMT: these functions help us calculate an amortization schedule which determines the breakdown each period between principal and interest.Ex. An NBA player will be a rookie in November 2010. Each year for six years he is paid $1 million a month during the months of November—April till December 2011. Assuming a discount rate of 10% per year, what is the NPV of his salaries as of January 15, 2008?1/15/2008 11/15/2010 12/15/2010 1/15/2011 2/15/2011 3/15/2011 4/15/2011 11/15/2011 12/15/20110 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 =XNPV(0.1, B5:J5, B4:J4)Ex. An oil company is going to drill a well costing $50 million on 5/15/2009. For each of the next 20 years they expect to receive $5 million in revenues. Assuming each year's revenue is concentrated midyear, what is the IRR for this oil well?=XIRR($B$19:$V$19,$B$18:$V$18,E$20)=XIRR(B9:V9,B8:V8)What is the monthly payment 20year loan with monthly payments and a 6% annual rate under the following three conditions:• EndofMonth payments =PMT(C4/12,C3,C5,0,0)• Beginningofmonth payments =PMT(C4/12,C3,C5,0,1)• Endofmonth payments with an ending $30,000 payment =PMT(C4/12, C3, C5, 30000,0)Ex. What is the monthly payment (endofmonth payments) on a 30year and a 8% annual rate of $300,000 loan?=PMT(0.08/12,30*12,300000,0,0)Ex. What is the annual depreciation we can charge on a $4000 machine with a salvage value of $500 and a 5year useful life?=SLN(4000,500,5)Counting COUNT(cell range) counts how many cells in cell range containing numbers. COUNTA(cell range) counts how many cells in cell range are not blank (contain text or numbers). COUNTBLANK(cell range) counts how many cells in cell range are blank.Statistical Functions[Formulas Tab] >> [Insert Function] >> select Category [Statistical] =MAX(cell_range) returns the largest number in a cell range. =MIN(cell_range) computes the smallest number in a cell range. =LARGE(cell_range, k) returns the kth largest number in a cell range. =SMALL(cell_range, k) returns the kth smallest number in a cell range. =AVERAGE(cell_range) computes the average of all numbers in a cell range. =VAR(cell_range) returns the variance [329.41 dollars2] of the numbers in a cell range. =STDEV(cell_range) returns the standard deviation [18.15 dollars] of the numbers in a cell range. =RANK(E5, cell range,0) returns the rank of a number k in a cell range 0 refers to descending: the largest number obtaining a rank of 1; 1 to ascending: the smallest number is rank of 1.Conditional Counting=COUNTIF(Location, "SOUTH") sales in South=COUNTIF(Units, 84) sales for 84 units=COUNTIF(Revenue,">=200") sales for >=$200Note that >=200 is enclosed in " " because >= is text.=COUNTIFS(Location, "SOUTH", Salesperson, "CHARLENE") sales in south by Charlene=COUNTIFS(Month, "APRIL", Revenue, ">700") sales in April for >$700Ex. How many transactions involved at least 50 units and were completed by Charlene in November for Napoleons?=COUNTIFS(Units, ">=50", Salesperson, "CHARLENE", Month, "NOVEMBER", Product, "Napoleon (piece)")Conditional Sums=SUMIF(Month, H4, Revenue)=SUMIFS(Units, Salesperson, $H18, Location,I$17)Ex. Compute the total units and revenue for each location.=SUMIF(Location, H32, Units)=SUMIF(Location, H32, Revenue)Ex. Summarize by month the total revenue generated by each salesperson.=SUMIFS(Revenue, Salesperson, $H39, Month, I$38)Remove Duplicates[Data Tab] >> [Remove Duplicates] >> select () Continue with the Current SelectionSorting[Data Tab] >> [Sort] Filtering Data[Data Tab] >> [Filter]Note: If we select [Data Tab] >> [Filter] again, then the dropdown arrows are cleared and our original data returns. Subtotal[Data Tab] >> [Subtotal]Ex. For each month, obtain a breakdown of the units and revenue for each product.Note: month is the main sort, product is the second sort.Pivot Tables and Pivot Chart[Insert Tab] >> [Pivot Table]Format Values: Right click inside the pivot table  choose ()[Show Field List]. Right click on [Sum of Revenues] >> [Value Field Setting] >> [Number format]Average Values: Right click on [Sum of Revenues] >> [Value Field Setting] >> [Average]Show as Percentage: Right click on [Sum of Revenues] >> [Value Field Setting] >> [Show Values As] Tab >> [Percentage of Column]Note: Filter can be used in Pivot Tables[Insert Tab] >> [Pivot Table]> [Pivot Chart][Home Tab] >> [Conditional Formatting]> [New Rule…] > [Use a formula to determine which cells to format]=F8>F7=E12=MAX(E$12:E$15) test whether E12 is the largest number in its column. =E12=MAX($E12:$H12) highlights in red for each region the product with the largest sales. CH6 Charts[Insert Tab]CH7 Importing Data into Excel[Open] data.txt[Data Tab] >> [From Web]CH8 Spreadsheet ModelingRules to Guide Intelligent ModelingLayout: Spreadsheet inputs and decision variables should never be hardcoded in formulas; inputs and decision variables should always be entered in their own cells. A common layout structure is to place model inputs and decision variables on the top of a worksheet, followed by worksheet calculations. The model outputs may then be placed below the calculations or to the right of the model inputs and decision variables. Colors: Yellow for Inputs Blue for Decision Variables Orange for Calculations Gray for OutputsUse Influence Charts: to help understand Model RelationshipsUse Range Names:For formula =A50*B100, this formula would look something like price*demand, which is much easier to understand. Notice: Be wary of using range names in a multiworksheet workbook. Successful use of range names in multiworksheet workbooks often requires knowledge of advanced concepts such as sheet level worksheet names.Documentation: Comments or Annotations in cells Text BoxesUse [Copy Cross] to efficiently create formulas: Notice: When copying formulas, make sure your use of absolute and relative addressing (the $ signs discussed in an earlier chapter) is correct.Test: A model should also be tested for sensibility. For example, if the unit cost of producing a pastry increases, we would expect Le Napoleon's NPV from a new bakery to decrease and the profit maximizing price to increase. [Conditional formatting] can be used to clarify a sensitivity analysis. For example, we can use conditional formatting to highlight the profitmaximizing price for different unit costs of producing a pastry. This is useful because if we change other inputs or decision variables, then the red formatting will still indicate the NPV maximizing price. Using Excel's Goal Seek feature, we can easily determine the value of each input that would make the project NPV =0. This helps us determine how sensitive our final decision is to changes in each input. Error Trapping: use IFERROR (or often an IF statement) to inform the model user of his or her input error. [Formulas Tab] >> [Error Checking] [Formulas Tab] >> [Evaluate Formulas]Understand your model's limitationsInfluence Charts [Insert Tab] >> [Shapes] Year t Profit = Year t Revenue  Year t Total Cost Year t Revenue = (Year t units sold)*(Year t Price) Year t Units Sold = Minimum (Capacity, Year t Demand) Year t + 1 Demand = (Year t Demand)*(Year t + 1 Demand Growth rate) Year t + 1 Unit Cost = (Year t Unit cost)*(Year t + 1 Unit Cost Growth Rate)Set up the Model Year t Profit = Year t Revenue  Year t Total Cost In cell D18 we compute the cost of building the bakery by entering the formula =Plant_fixed_cost + Cost_per_unit_of_capacity * Annual_Capacity. In row 25 copying from cell E25 to the range F25:N25 the formula =E23 * E21 computes the total cost incurred during each year of sales. In cell D26 we compute our Year 2012 profit as simply the negative of the bakery building cost. Copying from cell E26 to the cell range F26:N26 the formula =E24  E25 computes profit during each year of bakery operation as each year's revenues less variable costs. Year t Revenue = (Year t units sold)*(Year t Price) In cell E22 we copy the Year 1 price from cell D5. Then by copying from cell F22 to the range G22:N22 the formula =E22*(1 + Annual_price_rate_of_increase) we ensure that our price grows at the assumed rate of 5% per year. In row 24 copying from cell E24 to the range F24:N24 the formula =E22 * E21 computes revenue during each year of operation as the product of sales price and units sold. Year t Units Sold = Minimum (Capacity, Year t Demand) Copying from E21 to the cell range F21:N21 the formula =MIN(E20, Annual_Capacity) ensures that during each year we sell the minimum of bakery capacity and demand. Year t + 1 Demand = (Year t Demand)*(Year t + 1 Demand Growth rate) Next we enter the 2013 demand as 420000  60000 * price by entering in cell E20 the formula =Year_1_demand_intercept  Year_1_demand_slope * D5. In cell F19 we compute the 2014 demand growth with the formula =IF(F16 <= Years_till_demand_steady_state + 1, Beginning_growth_rate, Steady_state_growth_rate). As long as we are in the fast growth phase, demand grows by 15%. After the fast growth phase is over, demand grows by 5%. Copying this formula to the range G19:N19 generates the growth rate in demand through 2022. Entering in cell F20 the formula =E20 * (1 + F19) computes Year 2014 demand as Year 2014 growth rate * Year 2013 demand. Copying this formula to the cell range G20:N20 computes the demand for all later years. Year t + 1 Unit Cost = (Year t Unit cost)*(Year t + 1 Unit Cost Growth Rate) In cell E23 we copy the Year 1 Unit cost of producing a pastry from cell D13. Then copying from F23 to G23:N23 the formula = E23 * (1 + Annual_unit_cost_rate_of_increase) ensures that during each year unit costs grow at the assumed rate of 6% per year. Finally in cell B29 we use the function wizard to enter the formula =XNPV(Discount_rate, D26:N26, D17:N17). We find that (as of the first date on the list 1/1/2012) our assumptions imply the bakery will create $1,016,044 of value for Le Napoleon. This NPV is computed as of the first cash flow's date, 1/1/2012.WhatIF Data Tables [Data Tab] >> [WhatIF Analysis] >> [Data Table…] [Formulas Tab] >> [Calculation Options] Automatic  This setting is the usual setting. All formulas and data tables recalculate when data or formulas are entered into the spreadsheet. Automatic Except for Tables  If you choose this setting, then your formulas will recalculate whenever you enter data or formulas into your spreadsheet. Your data tables will not recalculate unless you hit the F9 key. This setting is useful in large spreadsheets because in large spreadsheets recalculation of data tables can be time consuming. Manual  When Calculation Mode is on Manual, no formulas or data table will recalculate unless you hit the F9 key. Manual calculation is useful in a large spreadsheet, because in large spreadsheets recalculation of formulas can be time consuming. Remember that in Excel 2007 a spreadsheet can have up to 1,048,576 rows and up to 16.394 columns.WhatIF Goal Seek [Data Tab] >> [WhatIF Analysis] >> [Goal Seek …]Auditing Trace Dependents [Formulas Tab] >> [Trace Dependents] [Formulas Tab] >> [Trace Precedents] Flexibility=IF(F17<=Years_of_Operation, MIN(F21,Annual_Capacity), "No Operation")Comment and Text Box [Review Tab] >> [New Comment] [Insert Tab] >> [Shapes]> [Text Box]Error Trapping 1. If price is high enough, we might see the unrealistic occurrence of a negative demand. To remedy this problem, we will modify our year 1 demand formula to ensure that negative year 1 demand cannot occur. = MAX(Year_1_demand_interceptYear_1_demand_slope*D5,0) //return the usual demand; unless negative demand will return 0. 2. Clearly the years of high growth must not exceed the number of years of operation. We will use an IF statement to ensure that if this condition is violated, the user is informed of that fact. = IF(Years_till_steady_state+1>Years_of_Operation, "Years till steady state cannot exceed years of operation",""). //If we have entered a value for Years of High Growth which exceeds Years of operation cell, B8 will return the error message "Years till steady state cannot exceed years of operation." Otherwise cell B8 will remain blank. 3. Number of years of operation should not be allowed to exceed 20. We will use DATA VALIDATION to ensure that this is the case. Using Data Validation, we can prevent the entry of a number >20 in cell D16. [Data Tab] >> [Data Validation…] Using [F9] and [Evaluate Formulas] Select part of a formula and press [F9] [Formulas Tab] >> [Evaluate Formulas]Scenarios 1. [Data Tab] >> [Data Validation…] > Settings Tab: Change “Whole Number” to “List”2. Draw a lookup table3. = VLOOKUP(D1,lookup,2,FALSE)Spinner [Options] >> [Customize Ribbon] > check “Developer Tab” [Developer] >> [Insert]> [Spin] Right click [Spinner] > [Format Control]Limitations Here are some limitations of our model: We have assumed a linear demand curve. Often the relation between price and demand is not linear. The twostage growth model may not be accurate. To test the validity of the twostage growth model we could check whether the demand growth for past bakery openings has followed the twostage growth model. Our model for building cost as a function of capacity may not be accurate. Modelers often assume that building a plant with capacity x incurs a cost of a+ bxk., where 0<k<1. This type of model ensures that the cost of adding additional units of capacity decreases as more units are added. Finally, none of our parameters are known with certainty. If we are highly uncertain about our model inputs, we should use Monte Carlo Simulation to model the probability distribution of each input and generate a range of possible NPV's. Then we could make statements such as "I am 80% sure the bakery will have a positive NPV." CH9 Excel Solver: optimization problems [Data Tab] >> [Solver] Objective: ProfitBy changing Variables: $B$5:$B$18Constraints: $B$5:$B$18 = integer //A fractional number of a product is not acceptable. $B$5:$B$18 <= $I$5:$I$18 //Do not make more of a product than customers are willing to buy. $E$19:$G$19 <= $E$3:$G$3 //Cannot use more baker time, flour, or sugar than what we have available.Profit = SUMPRODUCT(H5:H18,B5:B18)CH10 3D Formulas, Excel Table and Circular ReferencesConsolidate 3D Formulas=SUM(Jan:May!J6)Excel Table Select the cell range F3:G17 and select [Control] + [T].Ex. Enter a formula in cell H12 that will compute sales for any product. The formula should work if new products are added to the product list. The pie chart should update automatically if new product data is entered into the worksheet.=RANK([@Units],[Units],0) //0 refers descending; 1 refers ascending.This formula may be read as ranking the Units entry in the current row with respect to the Units column.Circular References [Options] >> [Formulas] > check “Enable Iterative Calculation”CH11 Monte Carlo Simulation@Risk Addin Software: http://www.palisade.com/bookdownloads/booksoftware.asp?isbn=0000000003252 The key to building an accurate Monte Carlo simulation model of a situation is to determine an appropriate random variable to represent each source of uncertainty in the real life situation. There are two types of random variables: discrete and continuous random variables.=RISKDISCRETE(E8:E11,F8:F11) A continuous random variable has a zero probability of assuming any particular value. For example, there is a probability 0 that a randomly selected person is exactly 6 feet tall. To see this note that to be exactly 6 feet tall a person's height would have to be 6.00000000000000000000000000000000000000 (there should really be an infinite number of 0's here!) feet tall, and the probability that a person's height exactly matches this number is to all intents and purposes zero. We model a continuous random variable by defining a PROBABILITY DENSITY FUNCTION (pdf) for the random variable. The total area under a pdf must equal 1. In our development we will use two simple continuous random variables: the Uniform and Triangular random variable. A uniform random variable (written U[a, b]) is simply a random variable that is equally likely to assume any value between a and b. We can model a U[a, b] random variable with the @risk function:= RISKUNIFORM(50,200) // Note the density is uniform or constant everywhere on the interval [50, 200]. The triangular random variable has become popular because it generalizes the common practice of analyzing a business case by creating a best case, worst case, and most likely scenario. The triangular random variable simply allows for the possibility that any value between the best and worst case has a chance of happening.=RISKTRIANG(40000,60000,70000) Eg. Le Napoleon is trying to decide whether or not to introduce a new pastry to compete with similar offerings of other companies. Three key quantities associated with this decision are highly uncertain:• Fixed cost of developing the pastry. We will assume that there is a 50% chance that the fixed cost is $10,000; a 30% chance the fixed cost will be $30,000; and a 20% chance the fixed cost will be $50,000. [Fixed Cost]=RiskDiscrete(H5:H7,I5:I7).• The best case for annual market size of the new pastry is 70,000 units; the worst case is 40,000 units, and the most likely case is 60,000 units, [Market Size] =RiskTriang(40000,60000,70000)• Le Napoleon's market share of the new pastry is equally likely to be anywhere between 20% and 50%. [Market share] =RiskUniform(0.2,0.5) We see that fixed cost can be modeled as a discrete random variable (using the RISKDISCRETE function), market size can be modeled as a triangular random variable (using RISKTRIANG function), while market share can be modeled as a uniform random variable (using RISKUNIFORM function). [Units Sold]=ROUND(D8*D9,0) [Revenue]=Units_Sold*Price [Variable Cost]=Units_Sold*Unit_Cost [Profit]=RevenueVariable_CostFixed_Cost. Move the cursor to the [Profit] cell (D13) and then choose the [Add Output] icon. This makes cell D13 an Output cell and @RISK will keep detailed statistics about this cell. Next, change the number of [iterations] to 1000. This is equivalent to hitting the F9 key 1000 times and having @RISK track the resulting profits for each trial or "iteration. [@RISK Tab] >> [Start Simulation]回应 20120810 09:39
谁读这本书?
二手市场
 > 点这儿转让 有2人想读,手里有一本闲着?
订阅关于Practical Management Science (with CDROM Update) : Spreadsheet Modeling and Applications的评论:
feed: rss 2.0
0 有用 大师 20120810
HBS Spreadsheet Modeling
0 有用 大师 20120810
HBS Spreadsheet Modeling