135|EXCEL – Cash Flow Crunch

BYU Student Author: @Jae
Reviewers: @Parker_Sherwood, @Brett_Lowe
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

  • Excel

Need a program? Click here.

Overview
Crayon Sunroof Muffins Inc. (CSM) is a company that specializes in creating unique and colorful muffins that are sure to brighten up your day. Using only the finest ingredients, their muffins come in a variety of flavors, including blueberry, chocolate chip, and raspberry. But what sets them apart is their signature sunroof design, which features a colorful swirl of icing on top of each muffin, making them look like a mini work of art.

Founded by a group of passionate foodies, CSM takes pride in creating muffins that not only taste delicious, but also look visually appealing. In recent years, CSM has engaged in market analysis and stock-market investment. Their investment team is made up of seasoned professionals who analyze stock market trends and love to bake.

In addition to their investments, CSM also has a fleet of vehicles used for their business operations. They have a team of skilled drivers who ensure that their vehicles are always in top condition and available for use whenever needed. Whether it’s delivering their baked goods to stores or attending food festivals and events, their vehicles are an integral part of their business operations.

You are a junior accountant at CSM. As you approach year-end, your manager, Jesse, would like you to create a simple statement of cash flows for the current year to help you prepare for end-of-year financial accounting. An easy bake. Jesse has given you transaction data for you to convert into the financial report. She has also attached a template for the report to aid in your calculations.

Instructions
There are two parts to this challenge:
Part 1 – Complete the Statement of Cash Flows

  • Referencing the ‘Transaction Data’ sheet, complete CSM’s statement of cash flows on the ‘Cash Flow Statement’ sheet
  • Jesse has already prepopulated some cells with values and formulas to help you
  • Write formulas into cells C8:C12 to account for the changes in cash flows for operating activities
  • Write formulas into cells C16:C18 to account for the changes in cash flows for investing activities
  • Write formulas into cells C22:C24 to account for the changes in cash flows for financing activities

Part 2 – Custom formulas

  • Answer the three questions in column F by writing a single-cell formula answer for each question in column G. The SUMIFS function and use of arrays (Ctrl + Shift + Enter) may be helpful
  • Although each formula should be in a single-cell, the sale of shares question should output 5 values in a row

Data Files

Suggestions and Hints
  • Defining each column in the transaction data in the name manager will make your formulas easier to write and more readable
  • Do not worry about depreciation and other standard cash flow statement items, we are focused on the formulas, not the final presentation or actual reporting
  • The SMALL function may help with a question, it has a similar counterpart
  • Part 1 Check figure: EOY cash = $110,495

Solution

For part 1, I was able to create a formula in the first cell and then copy it down the column to create the SCF much faster. Part 2 was a great reminder of how to use the SMALL/LARGE functions and array formulas.

Kyle_Brown_Challenge135_Data.xlsx (18.0 KB)

Copy of Challenge135_Data.xlsx (20.3 KB)

30 minutes to complete
Difficulty: Intermediate