I'm trying to write a spreadsheet for a small business plan. I have seven sheets in the book: Perma (permanent equipment, such as computers), recur (recurring equipment, such as salaries, inventory, etc), loan (detailing the terms of my business loan), costs (total costs), revenue, and profit.
I want my start-up costs to equal the permanent equipment plus three months of recurring equipment, effectively meaning I can hold the business with no revenue for at least three months. This means that, when I add the permanent equipment, and the quarterly recurring costs (monthly recurring times 3), that's how much I borrow.
However, when figuring my recurring costs, I also have to factor in my loan payments. I've gotten off a math & science forum the equation for a loan payment (the kind that banks use on their online calculators), but when I put that in a seperate sheet (titled "loan"), I make the principal equal to the start-up costs (when one changes, so does the other). I enter into the recurring costs a cost called "payment," which is equal to the amount in "payment" cell of the loan sheet. This effectively causes a going-in-circles relationship, so that the cells automatically adjust each other so that the start-up costs are as low as possible, without me having to manually enter new payments from online calculators, and adjust the loan accordingly (the spreadsheet does that for me). Or at least, that's what I was hoping for. Instead, the cells where this circle is present just gives me the message "Err.522."
Now, I think I know why this is happening: Because if how does the spreadsheet know whether I want the start-up costs to be as low, or as high, as possible. Also, spreadsheet cells can go all the way down to negative infinity, but I can't use any numbers other than positive and zero.
So, I've identified the problem; the only question is, how do I fix it?
I want my start-up costs to equal the permanent equipment plus three months of recurring equipment, effectively meaning I can hold the business with no revenue for at least three months. This means that, when I add the permanent equipment, and the quarterly recurring costs (monthly recurring times 3), that's how much I borrow.
However, when figuring my recurring costs, I also have to factor in my loan payments. I've gotten off a math & science forum the equation for a loan payment (the kind that banks use on their online calculators), but when I put that in a seperate sheet (titled "loan"), I make the principal equal to the start-up costs (when one changes, so does the other). I enter into the recurring costs a cost called "payment," which is equal to the amount in "payment" cell of the loan sheet. This effectively causes a going-in-circles relationship, so that the cells automatically adjust each other so that the start-up costs are as low as possible, without me having to manually enter new payments from online calculators, and adjust the loan accordingly (the spreadsheet does that for me). Or at least, that's what I was hoping for. Instead, the cells where this circle is present just gives me the message "Err.522."
Now, I think I know why this is happening: Because if how does the spreadsheet know whether I want the start-up costs to be as low, or as high, as possible. Also, spreadsheet cells can go all the way down to negative infinity, but I can't use any numbers other than positive and zero.
So, I've identified the problem; the only question is, how do I fix it?
