The primary weakness of portfolio optimization is the reliance on historical data to determine portfolio weights. To simplify this calculation, we use data analysis toolpak to geenrate a covariance matrix, and then use two of Excel's array functions, MMULT and TRANSPOSE to arrive at a solution. The most difficult task with the multi-security model is calculating the portfolio varinace and standard deviation. While it is possible to derive weights using purely mathematical means, we take advantage of Excel's optimization engine Solver to determine optimal captial allocation. The turtorial then moves into the more complex situation where multiple securites are present, thus improving diversification. Multi-security Example Portfolio Optimization The tutorial discusses how the optimal wieghts are determined graphically and mathematically. This gives us the basic idea of diversification in investing. The first example covers the classic textbook example of the two security case. The video tutorial below demonstrates two methods of portfolio optimization in Excel. These facilities not only need to ensure they are managing their staffing according to their own company policies, but they also have to maintain adequate staffing to meet regulatory requirements.Excel Spreadsheet Model for Portfolio Optimizationįor versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007 Classic Two Security Example Portfolio Optimization Patient care facilities rely on our applications to show them how they are doing in meeting the documented and required hourly care needs of their patients. No more pouring over different spreadsheets for hours trying to figure things out. Step 5: Generating Reports for Management and Regulatory Purposesįinally, with all the data in one place, Excel can create all the reports you need at the click of a button. With the data from Steps 2 and 3, nice visualizations can be created to let you quickly determine where you are over or under staffed. Here is where Excel does a really great job. Step 4: Comparing Allocated vs Needed Staff
That limit will be part of the logic that you will need to provide us, if we automate this process. At some point there is a limit to how much a single person can work before you will have to assign another resource to fill the need. Unless, of course, you like to work your people at, say, 120% FTE. Either way, a single person can only work up to one FTE per day. Alternatively, you can provide us with logic so we can have fun developing an algorithm for use in assigning people automatically. For each project, location or business unit, you get to pick who you want on the job and when.
Step 3: Allocating Human Resources to various Projects, Locations and Business Units If your firm is small, an interface can be provided to enter this information directly into Excel. If your firm is large and this information is stored in a separate database, it can be pulled into Excel with a data connection or an export/import procedure. With PTO, holidays, conferences, training and other demands that pull people off the job, it’s important to have the availability of your resources at your disposal. Step 2: Forecasting Human Resource Availability Aggregate data is then fed into the subsequent steps.
Then, these separate models can be aggregated using native Excel functionality or VBA code, depending on the complexity.
Models can be developed for individual projects, locations or business units. Our team can help get the customized Excel solutions for forecasting, scheduling, and analyzing your staffing needs.Įxcel is great for developing forecasts, whether they are based on simple counts and ratios or more complex models. Generating Reports for Management and Regulatory purposes.Allocating Human Resources to various Projects, Locations and Business Unit.Forecasting Human Resource Availability.Five Key Features of an Excel Staffing Application At eSoftware Associates, our expert VBA developers have created customized Excel staffing applications for firms in construction management, manufacturing, and healthcare. As the organizations grow and the staffing and reporting requirements become more complex, these simple spreadsheets can then be transformed into Excel applications with an amazing amount of functionality. It’s common for organizations to start out with a simple Excel spreadsheet to manage their staffing needs.