This page only contains a part of excel assignments that I did in college. For more details or excel works, please contact me.
Click the title to download the Excel file.

Basic Graphs and Simulation

  • Calculated aggregate statistics
  • Created Frequency Table, Scatter Plot with Linear Trend Line, Bar charts
  • Created stacked column chart, 100% Stacked column, histogram
  • Created a model that generate 10000 trials to the differences between the ‘control group’ and the ‘experiment group’ are statistically significant

Advanced Simulation

  • Used ‘Risk Solver’ in Google Sheet to simulate the probability of going broke of a new company.
    Conditions of the Company:
    1. $500,000 to start
    2. Cash in on average $1,000,000 cash each month.
    3. Cash out (expenses, employee salaries, rent, etc.) is on average $800,000 per month.
    4. Cash In and Cash out are normally distributed with a Coefficient of Variation of 25%
  • Used ‘Risk Solver’ to create a model that has uncertainty assumptions and simulate the probability of having different costs.

Regression

Part1

  • Created Scatter Plot including Trend Line and Prediction Formula
  • Calculated standard error of the slope
  • Calculated Association statistics (Correlation coefficient, coefficient of determination)
  • Forecasted using prediction function
  • Measured Forecast Error (MAD, MSE, MAPE) and visualize using histogram

Part2

  • Used ‘XLMiner Analysis Toolpak’ in Google Sheet to create a linear regression model using the training set
  • Forecasted on the test set using the linear regression model
  • Calculated RMSE to identify the model with the lowest forecast error.
  • Visualized test set errors (Absolute error and Percent error)

Optimization

Part1

  • Used “Solver” in Google Sheet to build a Product Mix Model with quantity and profit constraints to determine how many of each product to make in a period to maximize profit.

Part2

  • Used “Solver” in Google Sheet to build a Transportation Model to determine the most cost-effective allocation of food to the camps.

Part3

  • Used the time series FANG index (Facebook, Amazon, Netflix, Google) to estimate the annual return.
  • Created a variance-covariance matrix (based on daily percentage change).
  • Used “Solver” to build a Markowitz Portfolio Model to make the most efficient selection of optimal portfolios offering the maximum expected return of the given assets.