Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
FIGURE 7-6
End-of-year cash on hand section
￿
Net Cash Position (NCP)
The NCP at the end of a year equals the cash at the beginning of the
year plus the year
s net income after taxes.
￿
Borrowing from bank—Assume that the company’s bank will lend enough money at the end of
the year to reach the minimum cash needed to start the next year. If the NCP is less than this
minimum, the company must borrow enough to start the next year with the minimum.
Borrowing increases the cash on hand, of course.
￿
Repayment to bank—If the NCP is more than the minimum cash needed and some debt is owed
at the beginning of the year, you must pay off as much debt as possible without going below the
minimum cash required to start the next year. Repayments reduce cash on hand.
￿
End-of-year cash on hand—This amount is the NCP plus any borrowing and minus any
repayments.
Debt Owed Section
This section shows a calculation of debt owed to the bank at year
s end (see Figure 7-7). Year 2011 values are
NA, except for End-of-year debt owed, which is \$50,000. You must compute values by cell formula; use
hardcoded numbers in formulas only when instructed. Cell formulas should not reference a cell that contains a
value of
NA.
An explanation of each item follows the figure.
FIGURE 7-7
Debt Owed section
￿
Beginning-of-year debt owed
Debt owed at the beginning of a year equals the debt owed at the
end of the prior year.
￿
Borrowing from bank
This amount has been calculated elsewhere and can be echoed to this
section. Borrowing increases the amount of debt owed.
￿
Repayment to bank
This amount has been calculated elsewhere and can be echoed to this
section. Repayments reduce the amount of debt owed.
￿
End-of-year debt owed
In 2012 through 2014, this value is the amount owed at the beginning of
a year, plus borrowing during the year, minus repayments during the year.
Return on Sales Section
This section shows a calculation of return on sales in each year. Return on sales is net income after taxes
for the year divided by revenue for the year, as shown in Figure 7-8. Format values as percentages with one
decimal place.
FIGURE 7-8
Return on Sales section
Recall that the company
s goal is to earn at least 5% return on sales each year.

Search JabSto ::

Custom Search