141|EXCEL&VBA – Can You Escape?

BYU Student Author: @Erick_Sizilio
Reviewers: @klayton, @TylerBooth, @Donovon
Estimated Time to Solve: 25 minutes

We provide the solution to this challenge using:

  • Excel
  • VBA

Need a program? Click here.

Overview
In a job interview, the interviewer gives you an excel spreadsheet and says “I have a challenge for you! It’s all contained on this spreadsheet. The tabs named StoreID### contain the financial statements for each individual store location for our company. We currently have 278 stores in operation. I want you to create a consolidated financial statement in the tab labeled ConsolidatedFinancial Statement. After that, try to “escape” using tabs Room1 and Room2. If you can complete this challenge, the job is yours.” The interviewer sets a timer for 25 minutes and leaves the room.

Instructions

  1. Create a consolidated financial statement
  2. Escape Room1 and Room2
  3. Have fun!

Feel free to use VBA, Alteryx, Tableau Prep, Python or any other tools you think can be useful in solving this challenge!

Data Files

Suggestions and Hints
  • You can adjust a sum() formula to sum the same cell from multiple sheets. The last sheet is called “StoreID1”
  • Check figure: Cash and Cash Equivalents = $11,163,530
  • Net Profit Margin = Net Income/Sales
  • Asset Turnover = Sales/Asset
  • Asset-to-equity Ratio = Asset/Equity
  • You can use VBA to loop through all tabs that start with the name “Store”, get the StoreID, and paste it into a list in a blank tab. After this, you will be able to see which stores are missing by comparing the list you generated to a list of all possible Store IDs. StoreID ranges from 1 to 278.

Solution

I used VBA, I think I could improve on being more efficient, but it was good practice. I didn’t finish in the time either, but I think I could’ve without VBA. Here is my VBA code.

Sub Financials()
    Dim StoreSheets As Worksheet
    Dim CFinancials As Worksheet
    Dim CashTotalSum As Double
    Dim ARTotalSum As Double
    Dim InvTotalSum As Double
    Dim PETotalSum As Double
    Dim KitchenTotalSum As Double
    Dim VehiclesTotalSum As Double
    Dim BuildingTotalSum As Double
    Dim LandTotalSum As Double
    Dim APTotalSum As Double
    Dim ITTotalSum As Double
    Dim SPTotalSum As Double
    Dim MLPTotalSum As Double
    Dim MLPLTTotalSum As Double
    Dim LTTotalSum As Double
    Dim EquityTotalSum As Double
    Dim SRTotalSum As Double
    Dim COGSTotalSum As Double
    Dim IntTotalSum As Double
    Dim Sandbox As Worksheet
    Dim RoomRange As Integer
    Dim SalaryTotalSum As Double
    Dim DeprTotalSum As Double
    Dim OOETotalSum As Double

    ' Set CFinancials to the right sheet
    Set CFinancials = ThisWorkbook.Sheets("ConsolidatedFinancial Statement")

    ' Loop through each worksheet and add the cells
    For Each StoreSheets In ThisWorkbook.Worksheets
        If InStr(1, StoreSheets.Name, "StoreID") > 0 Then
           ' Cash and Cash Equivalents
           CashTotalSum = CashTotalSum + StoreSheets.Range("C8").Value
           CFinancials.Range("C10").Value = CashTotalSum
           ' Accounts Receivable
           ARTotalSum = ARTotalSum + StoreSheets.Range("C9").Value
           CFinancials.Range("C11").Value = ARTotalSum
           ' Inventory
           InvTotalSum = InvTotalSum + StoreSheets.Range("C10").Value
           CFinancials.Range("C12").Value = InvTotalSum
           ' Prepaid Expenses
           PETotalSum = PETotalSum + StoreSheets.Range("C11").Value
           CFinancials.Range("C13").Value = PETotalSum
           ' Kitchen Equipment
           KitchenTotalSum = KitchenTotalSum + StoreSheets.Range("C15").Value
           CFinancials.Range("C17").Value = KitchenTotalSum
           ' Vehicles
           VehiclesTotalSum = VehiclesTotalSum + StoreSheets.Range("C16").Value
           CFinancials.Range("C18").Value = VehiclesTotalSum
           ' Building
           BuildingTotalSum = BuildingTotalSum + StoreSheets.Range("C17").Value
           CFinancials.Range("C19").Value = BuildingTotalSum
           ' Land
           LandTotalSum = LandTotalSum + StoreSheets.Range("C18").Value
           CFinancials.Range("C20").Value = LandTotalSum
           ' AP
           APTotalSum = APTotalSum + StoreSheets.Range("F8").Value
           CFinancials.Range("F10").Value = APTotalSum
           ' Income Tax
           ITTotalSum = ITTotalSum + StoreSheets.Range("F9").Value
           CFinancials.Range("F11").Value = ITTotalSum
           ' Salaries Payable
           SPTotalSum = SPTotalSum + StoreSheets.Range("F10").Value
           CFinancials.Range("F12").Value = SPTotalSum
           ' Mortgage and Loans Payable
           MLPTotalSum = MLPTotalSum + StoreSheets.Range("F11").Value
           CFinancials.Range("F13").Value = MLPTotalSum
           ' Mortgage and Loans Payable Long Term
           MLPLTTotalSum = MLPLTTotalSum + StoreSheets.Range("F15").Value
           CFinancials.Range("F17").Value = MLPLTTotalSum
           ' Loans Payable Long Term
           LTTotalSum = LTTotalSum + StoreSheets.Range("F16").Value
           CFinancials.Range("F18").Value = LTTotalSum
           ' Equity
           EquityTotalSum = EquityTotalSum + StoreSheets.Range("F19").Value
           CFinancials.Range("F21").Value = EquityTotalSum
           ' Sales Revenue
           SRTotalSum = SRTotalSum + StoreSheets.Range("I6").Value
           CFinancials.Range("I8").Value = SRTotalSum
           ' COGS
           COGSTotalSum = COGSTotalSum + StoreSheets.Range("I7").Value
           CFinancials.Range("I9").Value = COGSTotalSum
           ' Salaries and Wage Expense
           SalaryTotalSum = SalaryTotalSum + StoreSheets.Range("I11").Value
           CFinancials.Range("I13").Value = SalaryTotalSum
           ' Depreciation Expense
           DeprTotalSum = DeprTotalSum + StoreSheets.Range("I12").Value
           CFinancials.Range("I14").Value = DeprTotalSum
           ' Other Operating Expenses
           OOETotalSum = OOETotalSum + StoreSheets.Range("I13").Value
           CFinancials.Range("I15").Value = OOETotalSum
           ' Interest
           IntTotalSum = IntTotalSum + StoreSheets.Range("I18").Value
           CFinancials.Range("I20").Value = IntTotalSum
        End If
    Next StoreSheets
    
    ' Set Sandbox to the correct worksheet
    Set Sandbox = ThisWorkbook.Sheets("Sandbox")
    
    ' Set up RoomRange count
    RoomRange = 1
    
    ' Loop through storeids and find out which one is missing
    For Each StoreSheets In ThisWorkbook.Worksheets
        If InStr(1, StoreSheets.Name, "StoreID") > 0 Then
            Sandbox.Range("A" & RoomRange).Value = StoreSheets.Name
            RoomRange = RoomRange + 1
        End If
    Next StoreSheets

End Sub