Problem with dynamically calculating numbers

[coryhouston]

I need to tap into the knowledge of the forum. I'm trying set up my model so that I can enter in a year and quarter number and my excel worksheet will automatically calculate the accrued revenue and enter it into the correct cell. Objectively, I would like to have the spreadsheet calculate a number representing the last 8 quarters of sales depending on the year/quarter entry.

I have attached an Excel file to better explain the scenario. If you look at line 5, I have this company will start paying money in Q3/FY18. So in the FY18 box, I want the entry to calculate a payment of past sales equaling a multiplier times the last eight quarters (including the first quarter) which is 25% of FY16, all of FY17, and 75% of FY18. This would the payment for past sales, and to this I would add 25% of the FY18 forecast to represent a payment for Q4/FY18. FY19 and onwards would just be the forecast number.

The challenging part is making the formula dynamic so that I can change either the year or quarter and the formula would recalculate the amount and put it in the appropriate cell.

Is this possible?


Cory

[AliGW]

Attach the sample workbook here. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Glenn Kennedy]

"So in the FY18 box, I want the entry to calculate " where is it?

Show us (on the excel sheet) whhere these numbers are and how they are derived:

"I want the entry to calculate a payment of past sales equaling a multiplier times the last eight quarters (including the first quarter) which is 25% of FY16, all of FY17, and 75% of FY18. This would the payment for past sales, and to this I would add 25% of the FY18 forecast to represent a payment for Q4/FY18. FY19 and onwards would just be the forecast number."


Where will you be doing this:
"I can change either the year or quarter "

[coryhouston]

Ali - thanks for directions on posting the file.

"So in the FY18 box, I want the entry to calculate " where is it?

Show us (on the excel sheet) whhere these numbers are and how they are derived:

"I want the entry to calculate a payment of past sales equaling a multiplier times the last eight quarters (including the first quarter) which is 25% of FY16, all of FY17, and 75% of FY18. This would the payment for past sales, and to this I would add 25% of the FY18 forecast to represent a payment for Q4/FY18. FY19 and onwards would just be the forecast number."


Where will you be doing this:
"I can change either the year or quarter "

Hopefully the attached spreadsheet example provides the details you have requested. Columns D and E are where those two variables are changed.

Cory

[AliGW]

Cory - please don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

For normal conversational replies, try using the QUICK REPLY box below. Thanks!