- Local time
- 4:48 PM
- Messages
- 374
All,
I created a five year pro forma financial model for my business. The spreadsheet makes extensive use of worksheet formulas and macros. I also added a sheet to track actuals (numbers extracted, by hand, from QuickBooks monthly).
Certain formulas in the spreadsheet are based on values that do not change over time. For example, I assume that my widgets will sell for some unit price and monthly revenue forecasts are calculated as the projected sales volume times the unit price.
Here's the problem - let's assume that my sales price changes at some point in time, after sales revenue has been generated. If I simply change the price, which is applied globally, then the historical 'projections' will be wrong.
Just to be clear: When the model was first built, it assumed that widgets sell for $100 apiece. Assume that unit sales are the same as the month number. Thus, my revenue for the first six months would be $100, $200, $300, $400, $500, and $600. Assume that for the first three months, I hit my revenue numbers. In this case, the sheet that tracks actuals would show a $0 difference, for each of the first three months, between projected revenue and actual revenue. Since business is doing so well, I decide to increase the price of the widgets to $150 on a forward-going basis. Suddenly, the spreadsheet now shows that I failed to meet projections for the first three months, when, in fact, I did meet them!
It seems to me that there should be some way to lock cells in the spreadsheet based on the current date. For example, when I open the spreadsheet in June, I should be able to programmatically protect cells from May and earlier. Is this possible? an you please provide a pointer to how this can be accomplished?
Thanks.
I created a five year pro forma financial model for my business. The spreadsheet makes extensive use of worksheet formulas and macros. I also added a sheet to track actuals (numbers extracted, by hand, from QuickBooks monthly).
Certain formulas in the spreadsheet are based on values that do not change over time. For example, I assume that my widgets will sell for some unit price and monthly revenue forecasts are calculated as the projected sales volume times the unit price.
Here's the problem - let's assume that my sales price changes at some point in time, after sales revenue has been generated. If I simply change the price, which is applied globally, then the historical 'projections' will be wrong.
Just to be clear: When the model was first built, it assumed that widgets sell for $100 apiece. Assume that unit sales are the same as the month number. Thus, my revenue for the first six months would be $100, $200, $300, $400, $500, and $600. Assume that for the first three months, I hit my revenue numbers. In this case, the sheet that tracks actuals would show a $0 difference, for each of the first three months, between projected revenue and actual revenue. Since business is doing so well, I decide to increase the price of the widgets to $150 on a forward-going basis. Suddenly, the spreadsheet now shows that I failed to meet projections for the first three months, when, in fact, I did meet them!
It seems to me that there should be some way to lock cells in the spreadsheet based on the current date. For example, when I open the spreadsheet in June, I should be able to programmatically protect cells from May and earlier. Is this possible? an you please provide a pointer to how this can be accomplished?
Thanks.
My Computer
- Computer type
- PC/Desktop
- OS
- Windows 7 x64 Pro
- CPU
- Core i7 860 @ 3.8 GHz
- Motherboard
- MSI P55-GD80
- Memory
- 16 GB F3-12800CL7D (DDR3 1600 7-7-7-24)
- Graphics Card(s)
- Sapphire Vapor-X 100283VXL Radeon HD 5770
- Monitor(s) Displays
- NEC LCD3090WQXi-BK