How to Model Your Own Iris in Excel

These IRIS tutorials are collections of m-files and pdf files (created by publishing the m-files). Each tutorial usually includes its own master file: read_me_first.m and read_me_first.pdf . This is the best place to start.

General Modeling Topics

In this tutorial, we build a simple sticky-price business-cycle model (SPBC), and run a number of basic and more advanced experiments to illustrate typical tasks in practical model development and operation. The experiments include, for instance, steady-state comparative statics, dynamic simulations, estimation, filtering of historical data, and forecasting.

OLG Model [Coming Soon…]

Using some of the IRIS preparser language features, we create an overlapping generation model whose number of cohorts can be flexibly changed by a parameter. We analyze its steady-state properties and dynamic simulations.

In this tutorial, we describe how to create two types of optimal policy model specifications based on a loss function, discretion and commitment, and run various types of experiments with models based on them. The experiments include simulating shocks, simulation disinflation, and drawing policy frontiers to describe policy trade-offs.

Equation-Selective Nonlinear Simulations [Coming Soon…]

We simulate models in an equation-selective nonlinear mode (equivalent to perfect-foresight or stacked-time solution methods) and explain how anticipated and unanticipated shocks need to be treated in such simulations. We run a Kalman filter that uses a nonlinear prediction step based on the same simulate technique.

In this tutorial, we show how to build and handle models with unit-root variables, such as balanced-growth path models. In IRIS, unit-root models do not need to be stationarized, and can be built, simulated and analyzed with the variables in their original levels.

Model File Language and Preparser

Model File Language [Coming Soon…]

We create and preparse various model file examples to illustrate the use of the IRIS preparser commands and pseudofunctions. Note that we use the ‘saveAs=’ option in the model function to save the pre-parsed model files for us to be able to look at what the pre-parser commands exactly do.

Time Series Analysis and Forecasting

VAR, SVAR, BVAR, Panel VAR, VARX, FAVAR, X12/X13, HP

This tutorial is an introduction into VAR modeling in IRIS. We prepare data, estimate a reduced-form VAR, check its properties, and assess the sampling uncertainty by bootstrapping. We then produce conditional and unconditional forecasts, and show how to identify a structural VAR (SVAR).

VARs with Sign Restrictions [Coming Soon…]

We show the identification of structural VARs based on sign restrictions, or other, more general, types of underdetermined identifying assumptions.

Panel VARs [Coming Soon…]

We explain the basics of panel VAR estimation and simulation in IRIS. The tutorial highlights the differences between plain VARs and panel VARs, and methods of comparing the two.

FAVAR Forecasting [Coming Soon…]

We develop a factor-augmented VAR model to forecast a large number of U.S. macroeconomic indicators.

BVAR Forecasting [Coming Soon…]

We develop a VAR model to forecast a number of U.S. macroeconomic indicators, and use prior restrictions (Bayesian VAR) to deal with the overfitting problem.

Reporting

Reporting Equations [Coming Soon…]

Reporting equations are an easy option how to evaluate a collection of equations in a non-simultaneous time-recursive way, i.e. equation by equation, period by period. Reporting equations can be created in three different ways: (i) as an rpteq object directly in an m-file or command prompt, (ii) as an rpteq object created by reading a separate file describing the equations, or (iii) as !reporting_equations in model files. This tutorial shows how to create and run reporting equations in all three ways.

Introduction to Reporting in IRIS [Coming Soon…]

This tutorial is an introduction to the reporting system in IRIS. It explains the report object and its basic elements, such as tables and graphs, and shows how to publish reports to PDF. Furthermore, it illustrates somewhat more sophisticated ways how to format, modify and customize the report elements.

The first step in iris folding is to choose a template. We have generated some pre-made templates for you to use. Simply copy & paste the large, medium, or small images onto a text file and print it out for use. You can also design your own templates for iris folding. You may choose any shape you want, or perhaps you want a template of specific dimensions. We use a square as an example.

Draw the shape you desire in the size that you want. Using a ruler, make a dot “X” distance from the corner. Do this for all corners. “X” can be half and inch; a quarter of an inch; or any distance you want.

If you choose a short distance (1/8 of an inch), then you will need many narrow strips of paper. If you choose a long distance (1 inch), you will need fewer strips, but each strip will be wider.

• go back to Iris Folding
• Home Page
• Free Origami Instructions
• Site Map
• Like Us on Facebook

What’s happening under the hood of lm? An R tutorial unpacking estimation, prediction, and validation for linear regression

Alex daSilva

Jul 17, 2019 · 4 min read

Learning about statistical/machine learning can be intimidating, especially if you’re like me and coming from another field (eg., social sciences, life sciences, business, etc). There are hundreds of complex models to choose from and numerous schemes to validate your data. Here, we’re going to take a closer look at a simple model for prediction you probably came across if you completed a statistics course, regression. Why so simple? Regression may not be as sexy as some other methods, but gaining a deeper understanding of the basics is undoubtedly important for moving on to more complex models.

When learning about regression, the emphasis probably wasn’t on prediction (or where predicted values come from) but rather on jumping to the output, checking coefficients, and praying that p-values associated with said coefficients fell below .05. Regression need not be just a tool for inferential statistics. As I touched on, if you’ve ran a regression analysis you’ve already completed a computation that involved calculating predicted values.

Below, we’ll unpack how prediction unfolds in two different contexts. In the first context, we’ll be working with data where the y value is present — a common situation where an analyst is making inferences about the relationship between the outcome and predictor variables. In the second context, we’ll be predicting new y values on left out data. We’ll do this all without the help of functions or libraries to illustrate how linear regression can be used as a basic predictive tool with the classic iris data set.

The first thing to do is to load the iris data set:

Before we get into our own calculations, let’s begin by looking at how we can fit a linear model and use that to predict some new data in base R with the “lm” function. We’ll attempt to predict Sepal Length from the other 3 numeric variables in the iris data set. To begin, we’ll grab some training data (70/30 split) and fit our model.

Now, we’ll create some test data and combine that with our model from the training data to create some predictions.

The predictions look great! If someone accidentally deleted a bunch of information regarding iris’ sepal lengths in a flower database, we can safely say we’d do a sound job recovering that information.

Of course, our concern here is to determine what “predict” is doing to come up with those values. We’ll take a look at that in a second; but first, we’ll examine prediction in the context of linear regression models in a more general sense.

We’ll start by examining how new values can be predicted on a set of data where both the y values and predictor variables are present (using the iris training data we created as an example). First, we need to separate our x values from our y values. We’ll pad the x matrix with a column of ones to represent the intercept.

Now, we need to transpose x and multiply it by itself ( X’X). This results in a sum of squares/cross-product matrix, SSCP. It turns out with a few manipulations, this matrix can depict the variance/co-variance, correlation, or cosine association between a set of variables. If you’re interested in hearing a bit more about that, check out my post here.

Our calculations match the output from the actual cross-product function.

Next, we are going to calculate a projection matrix or hat matrix (on it’s diagonal are observation leverages).

This will allow us to map y into predicted y values.

So, if we have access to the y values, we can combine them with a projection matrix to obtain predictions. That’s all well and good, but how can we predict new values of y in our test data using our training data? To do that, we need to calculate beta coefficients for our training data that contain information about the relationship between our y and x values.

We can ensure that out calculated beta values are indeed correct by comparing them to the values produce by “lm”.

We can add the model intercept through to our test data and multiply that by the beta coefficients (intercept excluded) to find our predicted values.

What we’ve learned so far can easily be extended to a kfold cross validation scheme. The function below will create folds and return a list containing the original data set with the folds as a new column. In addition, a list containing the fold indices themselves will also be returned.

Let’s create the folds and make sure they make sense.

And fit our model using 5 fold cross-validation:

We can extract and examine the predictive summary statistics.

Finally, we can pass our fold indices to the popular predictive modeling package, caret, and confirm our calculations.

Averaging across folds, our statistics match caret’s output.

In sum, we took a closer look at how prediction functions in the context of regression. We were ultimately able to apply the computations we covered to make predictions on left out data. Hopefully this tutorial provided a bit of clarity into what happens when you’re pressing ctrl+enter on a line of code containing the “lm” function.

Excel gives you the ability to protect your work, whether it’s to prevent someone from opening a workbook without a password, granting Read-Only access to a workbook, or even just protecting a worksheet so you don’t inadvertently delete any formulas. In this topic we’ll discuss the various ways you can utilize the primary options to protect and distribute your Excel files.

If you forget or lose your password, Microsoft can’t retrieve it for you.

You should not assume that just because you protect a workbook or worksheet with a password that it is secure – you should always think twice before distributing Excel workbooks that could contain sensitive personal information like credit card numbers, Social Security Number, employee identification, to name a few.

Worksheet level protection is not intended as a security feature. It simply prevents users from modifying locked cells within the worksheet.

Following are the different options available for protecting your Excel data:

File-level: This refers to the ability to lock down your Excel file by specifying a password so that users can’t open or modify it. You have two choices here:

File encryption: When you choose this option, you specify a password and lock the Excel file. This prevents other users from opening the file. For more information, see Protect an Excel file.

Setting a password to open or modify a file: You specify a password to open or modify a file. Use this option when you need to give Read-only or edit access to different users. For more information, see Protect an Excel file.

Mark as Final: Use this option if you want to mark your Excel file as the final version and want to prevent any further changes by other users. For more information, see Add or remove protection in your document, workbook, or presentation.

Restrict Access: If your organization has permissions set up using Information Rights Management (IRM), you can apply any of the available IRM permissions to your document. For more information, see Add or remove protection in your document, workbook, or presentation.

Digital signature: You can add digital signatures to your Excel file. For more information, see Add or remove a digital signature in Office files.

Note: To add a digital signature, you need a valid certificate from a certificate authority (CA).

Workbook-level: You can lock the structure of your workbook by specifying a password. Locking the workbook structure prevents other users from adding, moving, deleting, hiding, and renaming worksheets. For more information on protecting workbooks, see Protect a workbook.

Worksheet-level: With sheet protection, you can control how a user can work within worksheets. You can specify what exactly a user can do within a sheet, thereby making sure that none of the important data in your worksheet are affected. For example, you might want a user to only add rows and columns, or only sort and use AutoFilter. Once sheet protection is enabled, you can protect other elements such as cells, ranges, formulas, and ActiveX or Form controls. For more information on protecting worksheets, see Protect a worksheet.

Which level of protection should I use?

To control the level of access users should have to an Excel file, use file-level protection. Let’s say you have a weekly status report of your team members in an Excel file. You don’t want anyone outside your team to be even able to open the file. There are two options available:

If you don’t want others to open your file: You can encrypt the Excel file, which is the most common technique used. This basically means you lock it with a password and nobody except you can open it.

If you want to enable Read-only or editing access to different users: Maybe, you want the managers in your team to be able to edit the weekly status report, but team members should only have Read-only access. You can protect the Excel file by specifying two passwords: one to open, and the other to modify. You can later share the appropriate passwords with the team depending on the access they should be given.

To control how users should work with worksheets inside a your workbook’s structure, use workbook-level protection. Let’s say your status report workbook has multiple worksheets, and each worksheet is named after a team member. You want to make sure each team member can add data to their own worksheet, but not be able to modify any of the worksheets in the workbook, whether it be adding a new worksheet, or moving worksheets around within the workbook.

To control how users should work within an individual worksheet, use worksheet-level protection. Let’s say each worksheet in your status report workbook contains data that is common to all worksheets, like header rows or a specific report layout, and you really don’t want anyone to change it. By protecting your worksheet, you can specify that users can only perform specific functions in a sheet. For example, you can give users the ability to enter data, but keep them from deleting rows or columns, or only insert hyperlinks or sort data.

You can use one or more levels of protection for your Excel data depending on your/your organization’s needs. You can choose to use all of the available options or a combination of options—it’s completely up to the level of security you want for your Excel data. For example, you may choose to encrypt a shared Excel file, as well as enable workbook and worksheet protection, while only using worksheet protection on a personal workbook just so you don’t accidentally delete any formulas.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Now that you’ve figured out how to fund your business, you need to make future assumptions in your financial model and project how the business will perform. You need to build a separate Assumptions worksheet with your business projections that will drive the rest of the financial model.

The Assumptions worksheet already has places for your key business drivers — it’s up to you to input accurate and reasonable assumptions for the business.

In modeling, all input variables should be formatted consistently (usually with blue font or using Input Style, which can be found on the Home tab in the Styles section of the Ribbon). Then anyone using this model knows that she can make changes to any of the cells formatted in that way.

Revenue assumptions

Based on your study of other cafes in the area (you’ve been drinking a lot of coffee the past few weeks, haven’t you?), you expect the following assumptions for your business’s revenue:

• You’ll sell an average of 120 cups of coffee per day throughout the year.
• Forty percent of coffees sold will be in large cups; 60 percent will be in small cups.
• You’ll charge $4 for a large cup of coffee and $3.50 for a small cup of coffee.

These are your expectations for the business’s sales; they represent your base case revenue assumptions. You aren’t really sure whether you have the daily number of sales right — you’re just estimating — so you’ll adjust this number when you run the scenarios. You’ll address the best- and worst-case assumptions later on, after the base case is complete, so you can leave the cells in rows 3 and 4 blank for now.

Go to the Assumptions page, and enter the business sales.

The model has already been formatted for you, but when you make your own models, be careful to format correctly, expressing percentage values as percentages and using dollar signs to show dollar values. One of the most common errors in financial modeling is confusing units, such as treating a number of units as a price. Proper formatting makes any report or model clearer and easier to read for the user. Pay particular attention to formatting when using percentages. If a cell is formatted as a percentage, any figure entered will automatically be converted to a fraction.

For example, if you enter the number 5 in any normal cell, the value is 5. But if the cell is formatted as a percentage, the number 5 will automatically be converted to a value of 0.05 (or 5 percent). This could potentially lead to incorrect calculations.

Expense assumptions

In your analysis, you’ve also researched the operating costs of running a cafe, which are the following:

• You think the rent expense will most likely be $1,200 per month. This is just an estimate, though — you’ll enter some potential fluctuations into the scenario analysis later on.
• Consumables — including coffee beans, cups, filters, and so on — will cost you $0.45 per cup. This amount has been averaged over both large and small cups, so you won’t need to distinguish between size for the purpose of this model.
• The barista’s salary is $50,000 per year, plus 25% in other staff costs and benefits.
• Monthly utilities, such as electricity, heat, and water, will cost $100 per month.
• The company income tax rate is 30 percent.

These are your expectations for the business’s costs; they represent your base case expense assumptions.

Scroll down to the Expense Assumptions section on the Assumptions worksheet, and enter the assumptions.

Other assumptions

Finally, you also have some other assumptions regarding the number of business days you’ll be open per month and how busy your cafe will be throughout the year, so you need to apply some seasonality because this will affect your cash flow. Follow these steps:

1. Scroll down to the Other Assumptions section on the Assumptions worksheet, and based on next year’s calendar, complete row 32 for the number of business days per month.
2. Based on historical seasonal and weather patterns, complete row 33 for the seasonality variance.

Models must be well documented and assumptions must be clearly stated. A properly documented model will not only help you and others follow it, but also help users if you’re no longer around when a change needs to be made to the model. Including sources is handy for when you or someone else wants to go back and check the validity of your assumptions. Document assumptions and sources as you build the model. Documenting as you go is much easier than having to go back and do it at the end. After all, a model is only as good as its assumptions!

A Monte Carlo simulation can be developed using Microsoft Excel and a game of dice. The Monte Carlo simulation is a mathematical numerical method that uses random draws to perform calculations and complex problems. Today, it is widely used and plays a key part in various fields such as finance, physics, chemistry, and economics.

Key Takeaways

• The Monte Carlo method seeks to solve complex problems using random and probabilistic methods.
• A Monte Carlo simulation can be developed using Microsoft Excel and a game of dice.
• A data table can be used to generate the results—a total of5,000 results are needed to prepare the Monte Carlo simulation.

Monte Carlo Simulation

The Monte Carlo method was invented by John von Neumann and Stanislaw Ulam in the 1940s and seeks to solve complex problems using random and probabilistic methods. The term Monte Carlo refers the administrative area of Monaco popularly known as a place where European elites gamble.  

The Monte Carlo simulation method computes the probabilities for integrals and solves partial differential equations, thereby introducing a statistical approach to risk in a probabilistic decision. Although many advanced statistical tools exist to create Monte Carlo simulations, it is easier to simulate the normal law and the uniform law using Microsoft Excel and bypass the mathematical underpinnings.

When to Use the Monte Carlo Simulation

We use the Monte Carlo method when a problem is too complex and difficult to do by direct calculation. Using the simulation can help provide solutions for situations that prove uncertain. A large number of iterations allows a simulation of the normal distribution. It can also be used to understand how risk works, and to comprehend the uncertainty in forecasting models.

As noted above, the simulation is often used in many different disciplines including finance, science, engineering, and supply chain management—especially in cases where there are far too many random variables in play. For example, analysts may use Monte Carlo simulations in order to evaluate derivatives including options or to determine risks including the likelihood that a company may default on its debts.

Game of Dice

For the Monte Carlo simulation, we isolate a number of key variables that control and describe the outcome of the experiment, then assign a probability distribution after a large number of random samples is performed. In order to demonstrate, let’s take a game of dice as a model. Here’s how the dice game rolls:

• The player throws three dice that have six sides three times.

• If the total of the three throws is seven or 11, the player wins.

• If the total of the three throws is: three, four, five, 16, 17, or 18, the player loses.

• If the total is any other outcome, the player plays again and re-rolls the dice.

• When the player throws the dice again, the game continues in the same way, except that the player wins when the total is equal to the sum determined in the first round.

It is also recommended to use a data table to generate the results. Moreover, 5,000 results are needed to prepare the Monte Carlo simulation.

To prepare the Monte Carlo simulation, you need 5,000 results.

Step 1: Dice Rolling Events

First, we develop a range of data with the results of each of the three dice for 50 rolls. To do this, it is proposed to use the “RANDBETWEEN(1,6)” function. Thus, each time we click F9, we generate a new set of roll results. The “Outcome” cell is the sum total of the results from the three rolls.

Step 2: Range of Outcomes

Then, we need to develop a range of data to identify the possible outcomes for the first round and subsequent rounds. There is a three-column data range. In the first column, we have the numbers one to 18. These figures represent the possible outcomes following rolling the dice three times: The maximum being 3 x 6 = 18. You will note that for cells one and two, the findings are N/A since it is impossible to get a one or a two using three dice. The minimum is three.

In the second column, the possible conclusions after the first round are included. As stated in the initial statement, either the player wins (Win) or loses (Lose), or they replay (Re-roll), depending on the result (the total of three dice rolls).

In the third column, the possible conclusions to subsequent rounds are registered. We can achieve these results using the “IF” function. This ensures that if the result obtained is equivalent to the result obtained in the first round, we win, otherwise we follow the initial rules of the original play to determine whether we re-roll the dice.

Step 3: Conclusions

In this step, we identify the outcome of the 50 dice rolls. The first conclusion can be obtained with an index function. This function searches the possible results of the first round, the conclusion corresponding to the result obtained. For example, when we roll a six, we play again.

One can get the findings of other dice rolls, using an “OR” function and an index function nested in an “IF” function. This function tells Excel, “If the previous result is Win or Lose,” stop rolling the dice because once we have won or lost we are done. Otherwise, we go to the column of the following possible conclusions and we identify the conclusion of the result.

Step 4: Number of Dice Rolls

Now, we determine the number of dice rolls required before losing or winning. To do this, we can use a “COUNTIF” function, which requires Excel to count the results of “Re-roll” and add the number one to it. It adds one because we have one extra round, and we get a final result (win or lose).

Step 5: Simulation

We develop a range to track the results of different simulations. To do this, we will create three columns. In the first column, one of the figures included is 5,000. In the second column, we will look for the result after 50 dice rolls. In the third column, the title of the column, we will look for the number of dice rolls before obtaining the final status (win or lose).

Then, we will create a sensitivity analysis table by using the feature data or Table Data table (this sensitivity will be inserted in the second table and third columns). In this sensitivity analysis, the numbers of events of one to 5,000 must be inserted into cell A1 of the file. In fact, one could choose any empty cell. The idea is simply to force a recalculation each time and thus get new dice rolls (results of new simulations) without damaging the formulas in place.

Step 6: Probability

We can finally calculate the probabilities of winning and losing. We do this using the “COUNTIF” function. The formula counts the number of “win” and “lose” then divides by the total number of events, 5,000, to obtain the respective proportion of one and the other. We finally see that the probability of getting a Win outcome is 73.2% and getting a Lose outcome is therefore 26.8%.

Odds are pretty good that you create the same types of Excel spreadsheets over and over again. Creating a custom template can help make that process a lot smoother.

When faced with creating similar spreadsheets, many people pop open an existing sheet, delete the data they don’t want there, and then save it as a different file. Worse still, some just create the file from scratch every time. A custom template can make this go a lot faster. Template files are more or less the same as standard Excel files, but with formatting and boilerplate content already set. You just use the template to create a new Excel file, and fill in the data. You can create a custom template from scratch, or you can save an existing spreadsheet as a template, and then clean it up a bit. The only difference is whether you start with a new, blank sheet or one you’ve already created.

Create a Template

For our example, we’re going to create template from an existing spreadsheet. Here’s a standard sheet we use at How-To Geek for expense reports.

The file is just a regular Excel file saved with the .xlsx extension. Ours is already nicely formatted, so we just need to delete any actual data we don’t need to be there. If you’re adapting an existing sheet (or creating a new one), go ahead and clean it up. Set up the base sheet, add your headings, include formulas, format cells, create borders, whatever. In short, make it so that when you create a new file based on the template, you can just start typing in data.

When you’ve got things looking the way you want, you need to save the file as a template. Open the “File” menu, and then click the “Save As” option.

In the file type dropdown (just underneath the file name text box), select the “Excel template (.xltx)” option.

By default, Excel likes to save templates in DocumentsCustom Office Templates, but you can save them wherever it makes sense to you.

If you want an even more organized approach, you can change the default location where Excel saves templates. On the “File” menu, click the “Options” command. In the “Excel Options” window, slick the “Save” category in the left column.

On the right, you’ll see a “Default personal templates location” box where you can type a path to a custom save location for templates. There’s “Browse” button for some reason, so type the full path to the folder you want to use or copy and paste the location from a File Explorer address bar.

Use a Template to Create a New Document

Now that you’ve got your template saved, you can use it to create new documents. And you can do this in one of two ways.

If you save your templates in the official custom templates folder Office uses (whether that’s the default DocumentsCustom Office Templates location or you configured a new save location in Settings), those templates become available on the Office splash screen. That screen shows featured templates by default, but you can see templates you’ve saved by clicking the “Personal” link.

Just click the template you want to use, and Excel creates a new document for you based on that template.

You can also create a new file based on a template by just double-clicking the template in File Explorer. The default action on template files is to create a new file rather than open the template file, so double-clicking a template immediately opens a new Excel file for you.

If you want to open the actual template file to modify it, you can right-click the file, and then choose “Open” from the context menu.

In the end, template files are functionally much like regular excel files. The big difference lies in how Excel handles those files, making it easy for you to create new documents based on them.

Understand the basic dynamics and concepts while coding a simple computational epidemic model

Sadegh Maghsoudi

Mar 19 · 7 min read

If you live in this world, there is a very small chance you may have not yet heard of this novel Cor o navirus Covid-19 and the pandemic it caused literally all over the world. Actually, it is a good chance that you are reading this while self quarantined at your home, social distancing from public and like many, have very much of extra time to deal with. So, if you are familiar with programming, why not try this: build your own epidemic model at home. Which helps you boost your programming abilities, and, helps you understand the concepts and basic dynamics of epidemics.

But first of all, please note that it is simply an article to show how to use Python to prepare a basic model for Covid-19 epidemic. Which on the other hand means: It Is Not a model you could use to predict what’s to come. It is Not your DIY model which brings you the ability to simply ignore what official authorities recommend you to do. Neither it is a visualization tool to prove others on social media how doomed we are! If you’r OK with these disclaimers now let’s start our coding.

The main concepts we assume here, is to model a finite number of individuals of a sample society interacting with each other on a daily basis and the chance of contamination which each interaction brings. For the sake of simplicity, I generated the group randomly, with proximities represents the amount of interaction each person has with it’s nearest neighbors. Note that a person’s neighbor may be his/her family, colleagues, classmates or anybody else who he/she is in contact with every day. I assumed that this interaction remains the same for most persons in the sample, but to make things more interesting, let’s assume a fraction of group,I took 10%, which we call them Transporters, move randomly each day in the model; which means their neighbors change each day. I’ve added these group to represent those shopkeepers, taxi drivers, police officers, etc. who interact with new individuals each day. I’ve marked transporters with a circle and their number to keep track of each.
And finally, to complete our Day Zero initialization, what we need more is a Patient Zero! which I colored red.

After initialization, the simulations goes like this on each day:

• Transporters move randomly.
• People interact with their neighbors.
• Infection spreads randomly with a predefined chance of contamination from a patient to a healthy guy during interaction.
• A certain fraction of patients, Covid-19 (+), randomly hospitalize, assuming their symptoms got worst.
• A certain fraction of hospitalized patients, randomly selected, die.
• Patients who has endured the period of disease, let’s say 14 days, with mild symptoms will be cured and immune against the disease.
• And tomorrow . . . we repeat all above again. And that goes on until no new infection could be possible.

Coding the above mentioned algorithm would be an easy task for like all programming languages, but for visualization purposes, Python has a lot to offer using matplotlib .

Let’s see what happens if we run this simulation with a random dataset of 1000 individuals and infection chance of 25% in each interaction:

Let’s see what the statistics looked like during that simulation:

To change the colors of the current workbook

1. On the Page Layout tab, in the Themes group, click Theme Colors:

2. Click Customize Colors. :

3. In the Create New Theme Colors dialog box, under Theme colors, click the button of the theme color element that you want to change:

4. Under Theme colors, select the colors that you want to use:

In the Create New Theme Colors dialog box, under Sample, you can see the effect of the changes that you make.

5. Repeat steps 3 and 4 for all of the theme color elements that you want to change.

6. In the Name box, type an appropriate name for the new theme colors.

7. Click Save.

Note: To revert all theme color elements to their original theme colors, you can click Reset before you click Save.

To make changes apply to all new workbooks

If you would like to make these color changes apply to all new workbooks that you create, you need to create a default workbook template.

1. On the Page Layout tab, in the Themes group, click Themes.

2. Click Save Current Theme. :

3. In the File Name box, type an appropriate name for the theme.

Note: A customs document theme is saved in the Document Themes folder and is automatically added to the list of custom themes.

4. On the File tab, choose Save As.

5. In the Save Current Theme dialog box, was selected Office Theme (*.thmx) and chose the Document Themes folder as the location. You can use your own filename instead of the proposed Theme1.thmx.

The location of the Document Themes folder may vary, but it is usually located here:

C:Users AppData Roaming Microsoft Templates Document Themes

6. Click Save.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

Martín Pellarolo

Feb 23, 2018 · 4 min read

While Python’s scikit-learn library provides the easy-to-use and efficient LogisticRegression class, the objective of this post is to create an own implementation using NumPy. Implementing basic models is a great idea to improve your comprehension about how they work.

We will use the well known Iris data set. It contains 3 classes of 50 instances each, where each class refers to a type of iris plant. To simplify things, we take just the first two feature columns. Also, the two non-linearly separable classes are labeled with the same category, ending up with a binary classification problem.

Give n a set of inputs X, we want to assign them to one of two possible categories (0 or 1). Logistic regression models the probability that each input belongs to a particular category.

Hypothesis

A function takes inputs and returns outputs. To generate probabilities, logistic regression uses a function that gives outputs between 0 and 1 for all values of X. There are many functions that meet this description, but the used in this case is the logistic function. From here we will refer to it as sigmoid.

Loss function

Functions have parameters/weights (represented by theta in our notation) and we want to find the best values for them. To start we pick random values and we need a way to measure how well the algorithm performs using those random weights. That measure is computed using the loss function, defined as:

Gradient descent

Our goal is to minimize the loss function and the way we have to achive it is by increasing/decreasing the weights, i.e. fitting them. The question is, how do we know what parameters should be biggers and what parameters should be smallers? The answer is given by the derivative of the loss function with respect to each weight. It tells us how loss would change if we modified the parameters.

Then we update the weights by substracting to them the derivative times the learning rate.

We should repeat this steps several times until we reach the optimal solution.

Predictions

By calling the sigmoid function we get the probability that some input x belongs to class 1. Let’s take all probabilities ≥ 0.5 = class 1 and all probabilities

Picking a learning rate = 0.1 and number of iterations = 300000 the algorithm classified all instances successfully. 13.8 seconds were needed. These are the resulting weights:

LogisticRegression from sklearn:

If we trained our implementation with smaller learning rate and more iterations we would find approximately equal weights. But the more remarkably difference is about training time, sklearn is order of magnitude faster. Anyway, is not the intention to put this code on production, this is just a toy exercice with teaching objectives.

Further steps could be the addition of l2 regularization and multiclass classification.

How much of your salary do you need to save to reach your retirement goal? That is not a simple question to answer, but that is what this spreadsheet attempts to help solve. It makes quite a few assumptions, and doesn’t include nearly as much detail as you would find in professional financial planning software. But, for a quick estimation it may be useful.

Retirement Calculator

Download

License: Personal Use (not for distribution or resale)

“No installation, no macros – just a simple spreadsheet” – by Jon Wittwer

If you would like to use this Retirement Calculator in your business to assist your clients, you may purchase the commercial-use version.

The header includes a place for the client’s name and your company info.

The Vertex42 branding is outside the print area so it doesn’t show up when you make a printout or PDF for a client.

Return Policy: 60 Days

Note (3/13/15): I put this spreadsheet together after receiving numerous requests for a spreadsheet that combined my 401(k) savings calculator with my retirement withdrawal calculator. I still consider this spreadsheet a work in progress, but I think it’s finally at a point where people can try it out.

Update (5/5/16): Fixed a bug where the Retirement Income in the table (and chart) referenced inflation rather than the Annual Increase. Update (7/20/16): Added the ability to limit Other Retirement Income to a specific number of years (thanks to Ben Martinek).

Update (7/23/18): Fixed a bug in the formula for Current Annual Contributions. The old formula assumed the employer match was always maxed out. However, the corrected formula calculates the employer match as the Match % multiplied by the Employee contribution or the Max contribution (whichever is less).

Update (11/15/2018): Improved labels and instructions for the Current Savings Contributions section and added a note above the Shortfall section to warn when the shortfall is negative.

Update (3/22/2019): Fixed an error in the “Value of Other Income at Retirement” so that it more accurately predicts the amount of additional salary to save to reach zero at the end of the payout period.

Update (4/13/2020): This calculator was not designed for the case where Current Age >= Age at Retirement (meaning no accumulation phase), but a few changes were made in version 1.1.0 to handle a scenario where Current Age = Age at Retirement. The spreadsheet is still not ideal for that scenario (but it’s better than it was).

Using the Retirement Calculator Spreadsheet

The most important thing about this spreadsheet to understand is that future rates of return, inflation, salary, disasters, how long you’ll live, and other life events can’t be known for sure. So, if you are going to use this, realize that it is a mathematical exercise and the inputs that you choose and how you interpret the results are your own responsibility.

Below are some additional explanations that may help you use this calculator.

Rates of Return and Inflation

These are values that just have to be estimated. You can’t know future rates with certainty. However, one of the unique things about this spreadsheet is that you can enter different rates of return for the accumulation period and the retirement period. This may be useful because during retirement period, your portfolio will likely be weighted towards lower risk, lower return investments.

Defining Your Retirement Goal

1: Your Salary. Enter a salary you want to live on during retirement in today’s dollars. This should represent a lifestyle rather than an actual income or withdrawal amount. The payouts during retirement will be adjusted for inflation (the calculator uses the estimate of the inflation rate to calculate the “inflation-adjusted salary”). If you expect to receive income during retirement, the amount withdrawn from your nest egg is reduced to account for that.

2: Years to Pay Out. The number of years you want your retirement nest egg to last is the second aspect of your retirement goal. Are you going to live until you are 100, 90? Are your kids going to support you when your nest egg runs out? Is the government going to support you?

Current Savings Contributions

The amount that you will save is based on a percentage of your salary. That way, as your salary increases, the amount that you will save will also increase.

Your annual savings contributions in this spreadsheet are not limited to just 401k contributions. However, if you specify an Employer Match, the spreadsheet assumes you are first contributing to a 401k, and then any savings beyond the 401k limits are placed in other accounts.

Updated 11/15/2018: The labels and comments associated with the Employer Match should be more clear now. For this spreadsheet, if you enter 50% as the Employer Match and 6% as the Max Employee % of Contribution, this should be interpreted as “50% match up to an employee contribution of 6%.” This means that the company stops matching the rest of your contribution if you contribute more than 6% of your salary.

Social Security

This spreadsheet doesn’t include social security calculations. Instead, you can use the Other Retirement Income section to includes estimates of other income like social security. The social security administration provides a Retirement Estimator that you can use to get a rough idea of what your social security benefits might be.

Taxes

This calculator does not take into account taxes at all. That doesn’t mean taxes are not important to consider. In fact, taxes can have a HUGE impact on your savings. When using this calculator, assume that you are making all the correct tax-related decisions that apply to your specific situation.