Charlie's Financial Calculator Given (One must be blank) Calculated 0 12 5 5.00 60.00
Description When are payments due Periods per year Number of Years (to maturity) Number of Periods Annuity (Payment, Dividend) Present Value, investment par Future Value, redemption, value Nominal Interest Rate Year Effective Interest Rate Year Interest Per Period Total Payments Total Payments + FV + PV A given loan payment Interest portion of period PER Principal Portion of period PER
Excel Code Type NPERY YEARS NPER PMT PV FV Nominal_Rate Effect_rate RATE
$ 0.00 $ 0.00 $ 22,000.00 $ 22,000.00 $ (29,307.86) 5.75% 5.75% 5.90% 0.48% $ $ 0.00 (7,307.86) (105.42) 105.42
PER
1 $ $
Tue, 12 Aug, 2008 6:59 PM
Notes <=== Type (0 Annuity, 1 Annuity Due) usually 0 <=== Usually 1 for Bonds
<=== Negative for payments, zero if appropriate <=== Negative for payments, zero if appropriate <=== Negative for payments, zero if appropriate <=== Yearly interest rate <=== Not usually Used
<=== Usually the cost of doing business
Flow Process Chart
Job Analyst Page Working Conditions:
O p e r a t i o n
Details
I n M s o p S v e t e c D o m t e r e i l a n o a g t n y e
T i m e
Minimum Purchase
Green values are given Storage Capacity Unit Cost of Blue values are Manipulated 1.37 storage Unit Profit Yellow values are calculated 13.7 Possible Unit Quanity Purchas Cost of Gross Sales on Hand Actual Sales e Storage Profit Net Profit 274 800 274 0 ####### $ 3,753.80 ####### 315 526 315 0 720.62 4,315.50 3,594.88 164 211 164 0 289.07 2,246.80 1,957.73 548 47 47 800 64.39 643.90 579.51 479 800 479 0 1,096.00 6,562.30 5,466.30 254 321 254 0 439.77 3,479.80 3,040.03 256 67 67 800 91.79 917.90 826.11 274 800 274 0 1,096.00 3,753.80 2,657.80 342 526 342 0 720.62 4,685.40 3,964.78 411 184 184 0 252.08 2,520.80 2,268.72 479 0 0 800 456 800 456 0 1,096.00 6,247.20 5,151.20 205 344 205 0 471.28 2,808.50 2,337.22 109 139 109 0 190.43 1,493.30 1,302.87 216 30 30 800 41.10 411.00 369.90 290 800 290 0 1,096.00 3,973.00 2,877.00 315 510 315 0 698.70 4,315.50 3,616.80 342 195 195 0 267.15 2,671.50 2,404.35 369 0 0 800 411 800 411 0 1,096.00 5,630.70 4,534.70 246 389 246 0 532.93 3,370.20 2,837.27 479 143 143 0 195.91 1,959.10 1,763.19 548 0 0 800 685 800 685 0 1,096.00 9,384.50 8,288.50 5,485.00 ###### ######## $75,144.50 ########
Sales Modeling Example Purchase 137 800 Quantity Reorder 685 100 Level
Key
37a8841c-8e5b-4db4-ae3b-15a4d347b129.xls
Guess Finance Rate Re-Investment Rate (IRR) Internal Rate of Return (IRR) NPV Payback Period Sum of Cash Flows PV of Cash Flows Cost PI MIRR Life Equivelent Annual Annuities (EAA) Infinite Horizon NPV
Cash Flow 10.00% 10.00% 8.94% 96.39% Difference (1 A B 2) 96.39% 2000.00% (1903.61%) $ 238,605.80 $ 243,782.68 $ (5,176.89) 475,000.00 400,000.00 75,000.00 243,605.80 245,782.68 (2,176.89) (5,000.00) (2,000.00) (3,000.00) 48.72 122.89 -74.17 52.91% 77.97% (25.06%) 10.00 10.00 0.00 38,831.99 39,674.51 (842.51) 388,319.95 396,745.09 (8,425.14) Difference -3,000 -45,000 -45,000 -45,000 30,000 30,000 30,000 30,000 30,000 30,000 30,000 0 0 0 0 0 0 0 0 0 0 0 0
Year Expected Net Cash Flows 0$ (5,000) $ (2,000) 1 (5,000) 40,000 2 (5,000) 40,000 3 (5,000) 40,000 4 70,000 40,000 5 70,000 40,000 6 70,000 40,000 7 70,000 40,000 8 70,000 40,000 9 70,000 40,000 10 70,000 40,000 11 12 13 14 15 16 17 18 19 20 21 22
Page 5
37a8841c-8e5b-4db4-ae3b-15a4d347b129.xls
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Page 6
37a8841c-8e5b-4db4-ae3b-15a4d347b129.xls
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Page 7
37a8841c-8e5b-4db4-ae3b-15a4d347b129.xls
<== Profiles Cross at cost of capital
Page 8
XY Comp
X-Y Data
X 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
10.6 10.3 11.4 14.2
Graph Info
Linear Proj.
51.15 50.36 53.24 60.56
A given X ==>
18
Y
47.5 51.6 56.2 60.0
Exp. Proj.
Linear Slope = 2.614 Intercept = 23.441 Linear Eqn. Y = 23.441 + 2.614 * X 50.32 Exponential M = 1.049 B = 30.740 53.04 Exp Eqn. Y = 30.740 * 1.049 ^ X
51.05 60.65
70.0 60.0 50.0 40.0 30.0 20.0 10.0 0.0 10.6 10.3
X-Y Data
Y = 70.49 Y = 72.74
11.4
Linear Proj.
14.2
Exp. Proj.
Page 9
XY Comp
Descriptive Stats
X Mean Median Mode Geometric Mean Harmonic Mean Minimum Maximum Range Mean Absolute Deviation Standard Deviation Sample Variance Kurtosis Skewness Sum Count Confidence Level(95.000%)
11.6250 11.0000 #N/A 11.5301 11.4427 10.3000 14.2000 3.9000 1.2875 1.7783 3.1625 2.5490 1.6192 46.5000 4.0000 0.0558
Comparative Stats
Y
53.8250 53.9000 #N/A 53.6176 53.4097 47.5000 60.0000 12.5000 4.2750 5.4384 29.5758 -1.6460 -0.0630 215.3000 4.0000 0.1705
Pearson Product Moment Correlation FTest TTest
0.8547 0.0989 0.0001
Page 10
Costing
Costing
AQ AP SQ SP Material/Labor Actual Unit Quantity Actual Unit Price $ Standard (Budget) Quantity Standard (Budget) Price $ Price Variance per Unit Usage Varaince Actual Quantity X Actual Price Actual Quantity X Standard Price Standard Quantity X Standard Price LRV (Price/Rate Variance) LEV (Usage/Labor Variance) Total Variance
Given Calculated
750,000 0.0069 873,000 0.0060
750,000 0.0069 873,000 0.0060 $ 0.0009 ########### $ 5,175.0000 $ 4,500.0000 $ 5,238.0000 $ 675.0000 $ (738.0000) $ (63.0000)
U F
U U F F
Page 11
Basic EOQ Model
Annual Demand Ordering cost per order Annual carrying cost per unit Working Days per Year Economic Order Quantity Number of orders per year Length of order cycle Average Inventory Annual carrying cost Annual ordering cost Total Annual Cost
D= S= H= Days= Q0 = D/Q0 = Q0/D = Iave = Iave * H = (D/Q0) * S = TC =
Location Reliability of component Reliability of first backup Reliability of second backup Reliability at location Total system reliability
Independent Events Component Information 1 2 3
4
Useful Life with Exponential Mean MTBF Time Probability of Failure Before Time Probability of Failure After Time Proability following Normal Distribution Mean Failure Time Standard Deviation of failure Target Time Probability of Failure Before Time Probability of Failure After Time Availabilty MTBF MTR Availabilty
Focasting Erro Period Actual Forcast r 1 217 215 2 2 213 216 -3 3 216 215 1 4 210 214 -4 5 213 211 2 6 219 214 5 7 216 217 -1 8 212 216 -4 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Sum
|error| 2 3 1 4 2 5 1 4
Error2 4 9 1 16 4 25 1 16
MAD MSE
2.750 10.857
22
76
CostVolume
Cost Volume Analysis Fixed Cost FC = Revenue per unit R= Variable cost per unit VC = Breakeven point BEP = Volume Total revenue Total variable cost Total cost Profit Profit Volume $0.00 $0.00 $0.00 $0.00
Page 15
Linear Programming Given Values Constraint 1 2 3 Availability 1,200 900 1,440 Unit Profit $ A Resources Consumed B 12 5 3 2.40 $ 0 Resources Used B C 0 0 0 0 0 0 0 0 0 10 4 8 2.50 $ 0 C 8 4 16 3.00 0
Changeable Values Number of Units Produced Calculated Values A 1 2 3
Totals 0 0 0 0 0.00 0.00
Total Resources Used ===> Profit $ A Resources Exceeded? ===> 0.00 $ 0.00 $ 0.00 $ Your Results B C Profit
Scenarios A Only B Only C Only Equal Production Use All Resources Best Profit Best Profit - Macro
0
0
0 $
0
Run
Stop
Multi-Ratios
Ratios Data Assets (Current) Assets (Fixed) Assets (Total) Equity Interest Charges Inventory Liabilities (Current) Liabilities (Long Term) Liabilities (Total) Earning before taxes Net Profit After Taxes Receivables Sales Sales (Daily)
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
Ratios Liquidity Current #DIV/0! Quick #DIV/0! Profitability Return on Assets #DIV/0! Profit Margin on Sales #DIV/0! Activity Inventory Turnover #DIV/0! Average Collection Period #DIV/0! Fixed Assets Turnover #DIV/0! Total Assets Turnover #DIV/0! Leverage Debt to Total Assets #DIV/0! Debt to Equity #DIV/0! Times interest Earned #DIV/0! Common Stock Ratios Earnings per Share Book Value per Share Dividends per Share Market to Book
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Page 17
Staff Scheduling
For employees working five consecutive days with two days off, find the schedule that meets demand from attendance levels while minimizing payroll costs. Sch. A B C D E F G Days off Sunday, Monday Monday, Tuesday Tuesday, Wed. Wed., Thursday Thursday, Friday Friday, Saturday Saturday, Sunday Schedule Totals: Total Demand: Employees Sun Mon Tue Wed Thu Fri Sat Color Coding Target cell Changing cells Constraints
4 4 4 6 6 4 4 32
0 1 1 1 1 1 0 24 22
0 0 1 1 1 1 1 24 17
1 0 0 1 1 1 1 24 13
1 1 0 0 1 1 1 22 14
1 1 1 0 0 1 1 20 15
1 1 1 1 0 0 1 22 18
1 1 1 1 1 1 0 28 24
Pay/Employee/Day: $40 Payroll/Week: $1,280
The goal for this model is to schedule employees so that you have sufficient staff at the lowest cost. In this example, all employees are paid at the same rate, so by minimizing the number of employees working each day, you also minimize costs. Each employee works five consecutive days, followed by two days off. Problem Specifications Target cell Changing cells Constraints D20 D7:D13 D7:D13>=0 D7:D13=Integer F15:L15>=F17:L17 Possible schedules Rows 7-13 Goal is to minimize payroll cost. Employees on each schedule. Number of employees must be greater than or equal to 0. Number of employees must be an integer. Employees working each day must be greater than or equal to the demand. 1 means employee on that schedule works that day.
In this example, you use an integer constraint so that your solutions do not result in fractional numbers of employees on each schedule. Selecting the Assume linear model check box in the Solver Options dialog box before you click Solve will greatly speed up the solution process.
Source: solvsamp.xls included with Excel
Page 18