Please read attachments

YogaCo –original by EY Foundation Edited at

GGU

Background:

Final Exam Instructions

YogaCo is a privately owned business that began operations in March 2015. Its sole business is the

manufacture and sale of upper-end, high-quality yoga gear. It only sells to large distribution outlets. Its

primary product is a line of lightweight exercise clothes that contain a new, long-range RFID chip that

captures the following information about the user based on personal data (age, weight, etc.) entered by

the user:

► Heart rate

► Perspiration rate

► Calories burned

► Exercise efficiency (percent of capacity) The chip is able to continuously send this information to a host device as far away as 15 miles. The

clothes are also GPS enabled and able to track routes, distances, and elevations. Management prides

itself on being on the cutting edge. The company expects to conduct an IPO within a year or two.

YogaCo recently retained your firm as its consultant, largely because of your commitment to highly

efficient and technology-enabled analysis.

Data

You have been hired by YogaCo to help analyze their cash receipts to be able to complete a Cash Flow

analysis later. You are first responsible for performing an assessment of YogaCo related to its order-to-

cash function as it is a key piece in their Cash Flow model. Your first task is to acquire the data for these

transactions. You work with YogaCo’s IT group to gain access to its sales and cash receipts data for its

start-up period of operations, March through December 2015. You have been provided with an Excel file

with this data (2015 Data Tab) so you can begin your analysis. The data file includes the following fields:

► Type: this is the type of transaction, which is either a sale (Sales) or a cash receipt (CashReceipt).

► TransactionNumber: this is the transaction number (beginning with 1001 and will not exceed 9999)

automatic invoices are sent on this day to customers.

► AppliedToTransaction Number: this is the sales transaction number to which a cash receipt is

applied.

► CustNum: this is a unique customer number used to identify each customer.

► CustName: this is the customer’s name.

► TransactionDate: this is the date of the sale or cash receipt.

► Amount: this is the amount of the sale or cash receipt. Cash receipts will show a negative amount. Part I:

Become familiar with your data file. Make certain that your data is complete and accurate before

performing any analysis. Complete the following in the Final Exam – Data spreadsheet:

YogaCo –original by EY Foundation Edited at

GGU

1. You’ve been told that the accounts receivable balance on the general ledger on December 31, 2015,

is $684,491.19. They can always get this number from accounting to act as check figure. You also

know that as a start-up company, the beginning accounts receivable balance is zero. You are also

told that there are no returns or write-offs in 2015. Create a validation check for this balance on the

Validation Tab.

2. You’ve also been told that YogaCo only conducts business with the following 15 approved customers. Create a check figure on the Validation Tab to make sure that there are no other

customer names and that no customer names are misspelled. Hint: If there are X total amount of

transactions and you can count the total of each CustName to match the below list and both totals

match… etc. etc.

 Bigmart

 Cool Threads

 Corner Runner

 Cross Country Mart

 Family Fit

 Fit N Fun

 Goodway

 Neighborhood Athletic Supply

 Northern Lites

 Runner's Market

 Southeast Regional

 Southern Runners

 Super Runners Mark

 Urban Runner

 ValueChoice IMPORTANT: If there is an error (totals don’t add up), you will need to create a check figure on the 2015

data tab for CustName to find transactions that are incorrect or don’t belong. If you find an error, write the

Type, TransactionNumber, CustName, and Amount in the Notes tab, this information will be important.

Then correct the data (if CustName is misspelt, then correct to right spelling above). HINT: (Filtering,

Sorting, and one of the “If” formulas will be helpful.

3. The sales transaction log shows that 230 sales were transacted this year, beginning with transaction

1001. Create a check in the validation tab to ensure data for all invoices has been captured and that

there are no additional invoices or duplicates included in the file. If there are duplicates or additional

invoices, note it in the Notes tab.

YogaCo –original by EY Foundation Edited at

GGU

Part II:

Now that you have your data, you need to perform appropriate transformation techniques to inform your

analysis for the order-to-cash transactions for YogaCo. Remember we are trying to figure out how the

company has collected from cash from sale transaction in the past year.

1) Create a new tab and name it “2015 Consolidated”. Have the following headers and fill them in with the appropriate data:

 TransactionNumber – from 2015 data tab

 CustNum – from 2015 data tab

 CustName – from 2015 data tab

 TransactionDate – from 2015 data tab

 Amount – from 2015 data tab

 Cash Receipt Amount Applied – Create Formula to pull from 2015 data tab

 Cash Receipt Date – Create Formula to pull from 2015 data tab

 Amount Yet to Be Received – Formula data from this tab

 Days Since Transaction – Formula data from this tab

Assume that this report is as of December 31, 2015. For example, if the transaction date was December

15th 2015, days since the transaction would be 16. If cash for the sale has been received, it should say

“Received” in the Days Since Transaction column. (Hint, Transaction numbers are unique).

2) Insert a pivot table into a new tab with the 2015 Consolidated table created above to analysis the

sales and cash receipts and answer the following questions if asked:

a) Which Customer has the highest risk to not pay their accounts based on number of outstanding invoices and most days outstanding?

b) Which Customers are paid in full? When was their last purchase transaction date?

3) Develop an aging analysis of the amounts that are outstanding using 30-day increments (0–30 days,

31–60 days, 61–90 days and > 90 days). Use the Days since Transaction for the aging analysis.

Provide a visualization of the percentage of amounts yet to be received in each aging category at the

company level using a column chart. “Received” cash transaction should not be used in analysis or

appear in chart.

a) YogaCo is looking for the following on the chart:

i) Percentages on the Y Axis – neat and clean looking

ii) Aged day ranges clearly defined on X Axis – in ascending order

iii) Actual percentages above bars - showing as percentages with no decimals

iv) Proper Title that explains the chart

v) Neat and clean and easy to read

b) What would the Bad debt % Rate be for 2015 if all amounts that are >90 Days from transaction

date were uncollectible. Find the Bad Debt % as the amount of uncollectible sales divided by

YogaCo –original by EY Foundation Edited at

GGU

total sales AND as the amount of uncollectible sales divided by total Cash Receipts made in 2015

(by transaction date). Round to nearest 4 decimal places (e.g., XX.XX%)

YogaCo –original by EY Foundation Edited at

GGU

Part III:

After compiling all of the data for sales and cash receipts, YogaCo now asks you to help complete a cash

flow analysis and budget using the information from 2015 Data. They want to see what net operating

cash flow (Cash Method not Accrual) would look like given the below assumptions. They also wish to

have a model that is dynamic so they can change any of their assumptions and see its effect on the cash

flow model. They ask that all assumptions could be on a dashboard tab where they can change and

update them easily. The 2015 Data is all of their revenue from 2015. Actual Expenses can be found on

the “Expenses” tab.

After interviewing YogaCo, you have the following notes about their assumptions for 2016:

 Revenue Cash Receipts will raise monthly over the next year. Monthly growth of rate of 0.25%

o Note:

 Taxes and Fees will be the same and in the same months as 2015

 G&A will be 20% of Revenue

 COGS will be 50% of Revenue

 Professional Costs will be the same until August 2016 when it will go up to 75,000 a month for the rest of the year (Including August)

 Bad Debt Expense will be 1% of Cash Receipts (ignore calculated number in Part II)

On the Dashboard, YogaCo would like to see all assumption listed out and be able to change any

assumption and see the changed results. They would also like to see Total Gross Revenue, Total

Expenses, and Net Operating Cash Flow per year (2015 and 2016).