I’m a natural problem solver. Sometimes though I can spend a fair bit of time mulling over the problem before acting on different approaches to take when attempting to solve it. One thing that I have been working on is acting on the problem fairly soon and iterating through various approaches. Ironically, I’m also attempting to be as lean as possible and employ the approach of do it once and do it right.

What I’ve decided to do is create a new series of posts where I engage in a metacognitive approach to problem solving. To alleviate some of the bias into creating these problems, I’ll be using various AI software to create Excel/Google Sheets problems of varying difficulty levels. I’ll be writing a flow of consciousness as I read the problem and detail my thinking and ultimately approach to solve each problem.

Here is the Sheet: https://docs.google.com/spreadsheets/d/1U0vtrNoK2Xngz5IYDI5Io9GGKtGg1fFA4vvYRVovKcA/edit#gid=0

Problem 1 – Sales Report Calculation

Calculate the total sales revenue for each product category based on the provided sales data.

Mock Data:

Product	Category	Units Sold	Unit Price
Product A	Category 1	100	$10
Product B	Category 2	150	$8
Product C	Category 1	80	$12

Approach

My eyes initially spot the ‘Sales Report Calculation’ and my brain immediately drops into broadly specific analysis mode, e.g. sales variance, profit margin ratios that have moving dependencies, and multiple year accountabilities. When I hit ‘total sales revenue’ my eyes drop down to the mock data and see that I have Units Sold and Unit Price to play with; so I know that I have to perform a [Units Sold]*[Unit Price] formula. I re-read the original statement and realise that I need to calculate the total sales revenue for each product category.

Upon reviewing this paragraph immediately after writing and reflecting on my historical approaches to tasks, I can see where I have misinterpreted these previous tasks because my brain is attempting to jump forward to the solution to a problem that isn’t on the page/screen/table. This practice of “thinking out loud” to myself is actually a really helpful activity that reminds me to calm down, stop, and read the problem being asked.

From a Google Sheets perspective, I know that I can do the first portion, which is finding the revenue for each product; [Units Sold] * [Unit Price]; I can create this formula in E2:E4. Now, I need to account for the different Categories, but I want to be able to achieve this in one formula. So, I will place ‘Category 1’ in B6 and ‘Category 2’ in C6. I can enter the following formula into B7 and drag it across to C7:

=SUMIF($B$2:$B$4,B6,$E$2:$E$4)

Problem 2 – Expense Tracker

Create a monthly expense tracker that calculates the total expenses for each category and the overall total.

Mock Data:

Category	Amount
Groceries	$200
Utilities	$150
Rent	$1000
Dining Out	$120

Approach

When I see the words ‘monthly expense tracker’, I immediately think back through my previous project and do a mental check to see if I have already made one that I could use for this project or adapt a project that is close to this one. Since I haven’t created a project that is close to what I want to develop for this one, I’ll start breaking down the tasks that I’d need to do. This problem is asking me to create a monthly expense track to calculate the total expenses for each category. It should also show the overall total.

I will want to maintain that total expense at the top of the spreadsheet as it is most probably one of the metrics that I will want to observe in a flash. I have the option between running the months horizontally across the columns or vertically down the rows. Since there are only 4 categories that I want to track, I will run those categories across the columns and place the months down along the rows. I’ll make sure that I leave room at the top for the title and running (total) expenses.

I can now enter the number data into each of those categories for the month of January (assuming that is where the data starts). I’ll calculate the total of those expenses for each month at the end of the expense table. I have made individual assumptions for the next two months of spending for each of the categories, e.g. an increase in utilise in February of 15%. All of the months have been accounted for (in column F), which means that I can now take the sum of the entire year and place this at the top of the expense tracker (cells C3:D3).

Problem 3 – Gradebook Calculation

Calculate the average grade for each student based on their test scores.

Mock Data:

Student	Test 1	Test 2	Test 3
John	85	90	88
Alice	92	95	87
Bob	78	85	80

Approach

When I read this question I felt extremely comfortable with it as I used to develop my own Excel spreadsheets in 2015 to calculate my grades and what I needed to pass. It was a way to ingrain what I had learned about weighted scores in class to thoroughly understand percentages. When I re-read the question, I noted that the mock data didn’t include the weighted score for the tests so I felt a little less enthusiastic about solving this problem as all I had to do was take the average.

So, I’ll set up the new sheet and enter the data as is (A:D). In column E, I’ll set up the ‘Average Grade’ column and enter the formula AVERAGE(A2:D2). I can then drag this down from John to Bob and have the average grade for each student. Here we can see the respective grades scores of: 87.66, 91.33, and 81. Depending on your schooling system, we have two As and a B or something slightly different.

Problem 4 – Budget Planning

Plan a monthly budget by allocating percentages of income to different expense categories.

Mock Data:

Income: $5000

Category	Percentage
Rent	30%
Groceries	20%
Transportation	10%
Savings	20%
Entertainment	10%
Other	10%

Approach

I immediately thought back to Problem 2, the monthly expense tracker. This is a similar question, but asking me to extract information rather than build it up to a total. So, I’ll start by laying out this spreadsheet in a similar way to Problem 2; months down the rows, and the categories across the top. As I finished writing that last sentence, my mind went to what the spreadsheet would look like month. I had a vision of an array of 12 (months) x 6 (categories) of the same numbers down each column for 6 columns. To me, that makes for an uninteresting and meaningless use of 60 numbers (12*5). I had a vision of using budgeted vs actual monthly, but wasn’t sure if I wanted to put them right next to one another or group them at the end. I believe that this would come down to the dealer’s choice, but in this instance, I will place them next to one another.

I’ll start by duplicating Problem 2’s spreadsheet and adjusting it for this context. Now that I have the months going down column A and the categories running across the columns (B:G), I can then place the percentages below those categories. I’ll place the Monthly Income above the categories so that it can remain fixed in place. Now that I have all of those categories and income in place, I can set up the calculation for each category and month:

=$C$3*B$6

I’ll copy this code down the rows in the first column and then across the other columns. Now that this is completed for the budgeted distributions, I’ll place a column in between each category, which will provide me space for budgeted and actual columns. I can now label, beneath the category Budgeted and Actual. This is where the user can now enter their actual expenses for each month. I can introduce an average, underneath each Actual title, and divide that by the monthly income:

=AVERAGE(C7:C18)/$C$3

Problem 5 – Inventory Management

Keep track of product inventory and calculate the total value of the inventory.

Mock Data:

Product	Quantity	Unit Price
Item 1	50	$5
Item 2	100	$10
Item 3	75	$8

Approach

It had been a while since I had considered product inventory problems and I wanted to review what are the main metrics that should be tracked and maintained. I did a quick Google search to see what are the things that should be considered. Unfortunately, with the dataset provided, it is extremely limited as I feel that I can only produce one additional column of total value. However, if I place some assumptions in, we can start to develop a more robust spreadsheet, albeit simple.

I’ll begin by putting the data into a new spreadsheet. I’ll add a title and ‘Total value’ cell above it too. Next to Unit Price, I’ll calculate the Inventory Value for each entry. The ‘Total Value’ cell (above) will calculate the total of the inventory value. Then I can add the following columns to monitor how the inventory is tracking: ‘Reorder Level’, ‘Days per Reorder’, and ‘Item Reorder Quantity’

Problem 6 – Loan Amortisation Schedule

Create a loan amortisation schedule for a given loan amount, interest rate, and duration.

Mock Data:

• Loan Amount: $10,000
• Interest Rate: 5%
• Loan Duration: 5 years

Approach

The first thing I think of when I see Amortisation is, ‘is this American or Australian’? From a US perspective, amortisation is concerned with the book value of depreciation over time with respect to an intangible good (or goodwill) that a business accounts for in its financial reports. From an Australian perspective, this is the amount of money that has been paid by a loan perspective. Since this question is asking about a Loan Amortisation Schedule, I think it’s safe to say that the latter is more appropriate for this problem.

I consider the question’s request to create a loan schedule for the three factors; amount, interest rate, and duration. I know that I’ll need to create a table that includes at least 4 columns; the loan amount, the interest rate, loan duration, and amount that has been paid over time. I know that I can consider some of Google Sheets’ financial functions, in particular the PMT (Periodic Payment) function that calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate. Since this is a loan, and this usually means monthly payments, I’ll also account for 60 rows (5*12) so that I can observe these payments.

So, I’ll create the spreadsheet with the mock data placed into it:

Next I’ll using the PMT function to calculate the monthly payments:

=PMT(B4/12, B5*12, –B3)

Now I’ll create the schedule table with the following headings:

• Month
• Payment
• Interest
• Principal
• Balance

I’ll make the months dynamic (for potential future automation) by using SEQUENCE(12*B5+1,1,0); 61 months (allowing for the initial (0) month), with 1 column, and starting at 0. The first balance entry will be the loan amount, $10,000:

I know that the monthly payment is going to be the figure that I put into B7, so I can enter this (with absolute referencing) into E5:

=PMT($B$4/12, $B$5*12, –$B$3)

The Interest is the current previous month’s balance times by the monthly interest rate:

=H4*($B$4/12)

The Principal is equal to the Monthy Payment minus the Interest:

=E5–F5

The the current balance is the previous month’s Balance minus the current Principal:

=H4–G5

Now that the formulas have been created, I can select those for cells (E5:G5) and double-click the autofill bubble to complete the schedule:

Problem 7 – Time Tracking

Calculate the total hours worked for each employee in a week and their total pay based on hourly rates.

Mock Data:

Employee	Hours Worked	Hourly Rate
John	40	$15
Alice	35	$20
Bob	45	$18

Approach

I felt extremely comfortable making this solution as I have done it numerous times for one of my jobs this year. I even used the available data that I had to forecast potential hours that workers could submit as the budget was running out.

First, I will add the title and data into a new sheet. I then added a new column that totals each worker’s hours and rate. When I re-read the question, I felt like this was missing a fair bit of information, so I decided to add more entries and randomise their hours worked (maintaining the same hourly rate).

For the Employees, I made a temporary table at the bottom of the workspace and randomised the workers:

1 John

2 Alice

3 Bob

I then performed a VLOOKUP on this range using the RANDBETWEEN(1,3) function as the search key. I hard copy-pasted the results into the Employee column. Next I randomised the hours by entering the function RANDBETWEEN(30,55) in the Hours column then hard copy-pasted the results. I then added an additional column to that temporary table (mentioned above) and included each employee’s Hourly Rate:

1 John $15

2 Alice $20

3 Bob $18

I then performed a VLOOKUP on this updated range using the employee name (from the Employee column) as the search key and returned each employee’s hourly rate. After that, I copied the ‘Total Income’ formula down for the remaining entries. I wanted to see a summary of each employee’s income (to date) at the top of the sheet, so I added their names to the top and entered the following formula in the next row down, under each of their names (allowing for future entries):

=SUMIF($A$7:$A,B3,$D$7:$D)

To be sure that I had done the calculation correctly, I entered the following formula next to the last name’s total income at the top. If cell returns TRUE it means that the total wages equals the total buckets (created at the top) for each employee, which it does:

=SUM(B4:D4)=SUM(D7:D17)

Problem 8 – Stock Portfolio Performance

Track the performance of a stock portfolio by calculating the total investment value, current value, and percentage change.

Mock Data:

Stock	Quantity	Purchase Price	Current Price
AAPL	100	$150	$160
GOOGL	50	$2000	$2050
MSFT	75	$180	$190

Approach

When reading ‘Stock Portfolio Performance’, I immediately think of the one that I have previously made (insert link here). However, I start to re-read the problem statement and consider how I can make a new portfolio performance tracker, making improvements on the one that I have already created. Considering the 3 aspects that it the AI engine is asking me to base the analysis around (total investment value, current value, and percentage change), I am currently crafting an interface that considers investment date and total change across that period (to date).

Firstly, I will input the data into a Google Sheet. When entering this data though, I fall back into old habits of putting it at the top of the sheet. I am still attempting to correct this practice by moving this static data down the sheet, making sure that I have the most up-to-date information at the top of the sheet.

Secondly, I will commence the tracking of the portfolio value by entering ‘Total Investment Value’ and split them by ‘Intial’ and ‘Current’:

I will also split the stock values out so that we can see micro changes, but I can place this in with the static data table:

I can use the data in the table B9:G12 to develop the Total Investment Value:

Finally, I can integrate the percentage change of the overall portfolio:

To make things a bit clearer, I will add conditional formatting to this percentage change cell (C6) as well as the current cell (C5). If the percentage is:

• Greater than 100%, then the cell will be coloured green,
• Equal to 100%, orange,
• Less than 100%, red.

If the current value cell is:

• Greater than the intial value, then the cell will be coloured green,
• Equal to the intial value, orange,
• Less than the intial value, red.

The final dashboard design being:

Problem 9 – GPA Calculator

Calculate the GPA for each student based on their course grades and credit hours.

Mock Data:

Course	Grade	Credit Hours
Math	A	3
English	B	4
Science	A	3

Approach

My immediate response to this problem is that it is extremely similar to Problem 3 – Gradebook Calculator. However, when I reconsider the problem, I note that there are credit hours attached to the grading. During my time as a transcript assessor in one of my jobs, one of my roles is to balance the number of credit hours that someone completes their university subject under and accordingly assign that subject a “weighted value”.

I’m going to assume that this is an American GPA system, meaning that grades are awarded with the following schedule:

• A = 4
• B = 3
• C = 2
• D = 1

What I need to consider is the impact that credit hours have on the overall GPA. I can think of the credit hours, in collaboration with the grades, as weights for the overall GPA. So, I can use a SUMPRODUCT approach to finding the overall credit hours score and divide the total by the total number of hours.

First, I will input the data into the spreadsheet, label the sheet, then make space for the overall GPA:

Next, I’ll assign the Grades a numeric value (as above):

Now I can find the Total Credit Hours by multiplying the credit hours by the numeric grade:

I can get the sum of both Credit Hours and Total Credit Hours:

Then, finally, calculate the GPA by dividing Total Credit Hours by Credit Hours:

Problem 10 – Project Timeline

Create a project timeline with start and end dates for each task and calculate the duration of the project.

Mock Data:

Task	Start Date	End Date
Task 1	2024-09-01	2024-09-10
Task 2	2024-09-11	2024-09-20
Task 3	2024-09-21	2024-09-30

Approach

I have already created a Gantt Chart in my portfolio, and actually made a post about updating a part of it last year. However, I enjoy making Gantt Charts because I find them a little intimidating. That said, when everything is done and automated, it’s a great feeling. I’ll begin by placing that data table into a new sheet, and leave a space for two additional columns. I’ll then run the dates (for September) along row 4.

For the first column that I left free, I’ll calculate the number of days that each task requires to be completed: [End Date] – [Start Date]. The second column that I left free will be an aesthetic spacer.

Now I’m thinking about how I want the chart to look. I’m interested in a colour for the task days, but a dark version of the same colour for it’s last day. I think an approach for this is to use some nested IF functions. What I want to do is check if the current (today’s) date is in between the task’s completion range, e.g. is “today” 03/09/2024 in between 01/09/2024 and 10/09/2024? If it is, output a 1. If “today” is on 10/09/2024, I want it to output a 2 (for that darker colour). If it’s outside of those days, output a 0. So, I’ll use a mix of 2 IF functions and an ISBETWEEN function.

One last thing I need to consider is the cell referencing. For today’s date, I want to make sure that I can across columns, but maintain the date row (column$row). I want to lock in the task dates, but only for the columns, not rows ($columnrow; $columnrow).

=IF(ISBETWEEN(F$4,$B5,$C5,1,0),1,IF(F$4=$C5,2,0))

For the conditional formatting, I’ve assigned the following colours (arbitrarily) for each task:

Task 1

Task 2

Task 3