BYU Student Author: @Erick_Sizilio
Reviewers: @klayton, @TylerBooth, @Donovon
Estimated Time to Solve: 25 minutes
We provide the solution to this challenge using:
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
- Create a consolidated financial statement
- Escape Room1 and Room2
- 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