loan repayment
Paper instructions:
All formulae are to be optimized: if it is possible to copy a formula from one cell to another,
it should be copied.
• You are to use cell references everywhere outside of input areas in all spreadsheets. Add
necessary constants to the input area.
• Use 3-D reference to calculate loan repayment on the second spreadsheet for second strategy.
• When you print your assignment, please show gridlines.
Prepare and analyze spreadsheets reflecting the projected operation of “Best Products” and evaluate
two possible strategies over a year period based on the company’s Net Profit. You are to use only the
information given in this assignment. All formulae are to be optimized: if it is possible to copy a
formula from one cell to another, it should be copied.
First strategy
Running the business in a traditional way, the company is expected to end up first quarter with
following monthly average results:
Income: Product Sales = $22,500.00
Warranty Sales = $3,000.00
Installation = $ 3,200.00
Maintenance = $ 340.00
Repair Service = $ 1,650.00
Expenses: Rental Costs = $9,000.00
Salaries = $11,000.00
Purchases = $4,600.00
Other Costs = $6,200.00
The results for the next 3 quarters are projected based on the results for the first quarter and Change
rates. Each quarter is projected to have the following changes from the quarter preceding it:
Product Sales = 2.20%
Warranty Sales = 1.75%
Installation = 2.25%
Maintenance = 0.9%
Repair Service = 2.75%
Rental Costs = -3.10%
Salaries = 0.70%
Purchases = 1.00%
Other Costs = 1.20%
Gross Profit is the difference between the Total Income and the Total Expenses.
Taxes are 20 % of the first $10,000.00 of Gross Profit plus 30% of any Gross Profit in excess of
$10,000.00. For example, the tax on $8,000.00 would be $1,600.00, while the tax on $15,000.00
would be $2,000.00 + $1,500.00 = $3,500.00. There is no tax if there is no Gross Profit. Assume
taxes are paid at the end of each quarter based on Gross Profit for the quarter. At the end of the year
taxes are calculated based on the Gross Profit for the year and the given formula. The owner either
pays the difference or receives the overpayment back.
Net Profit is equal to the Gross Profit less Taxes.
The layout of the spreadsheet should be such that there is an input area for Income, Expenses, and
Changes in the upper left corner. The actual worksheet area showing the grid should be a few rows
below this input area. The grid is to include a column for each quarter as well as one showing the
yearly total for each item. Besides the rows for each item the grid is to contain rows for Total Income,
Total Expenses, Gross profit, Taxes, and Net profit. Below the grid you are to create a row, which
shows the yearly total Net Profit and the difference or overpayment of taxes for the year.
In the upper right corner of the spreadsheet you are to place a graph (vertical bar or column),
which shows Income, Expenses, and Net Profit for each quarter. The graph is to have a proper
title and axis labels. It must also be legible.
Input area
Graph
Grid
First Q Second Q Third Q Fourth Q Yearly Total
Product Sales
Warranty sales
…..
Total Income
Rental Costs
Salaries
….
Total Expenses
…
Formatting
In the grid, the rows containing Total Income and Total Expenses are to have upper and lower solid
line borders. A dotted line border is to be at the bottom of the Net Profit row. There should be no
other cell borders.
Numbers are to be formatted appropriately: dollars should be shown as such with two decimal places,
percentages should show a percent symbol. Column headings are to be in bold type.
When you have this spreadsheet ready, print a copy of
• the sheet with numbers showing
• and a copy with the formulae showing.
You should print in landscape. The formula sheet can be made to fit on one sheet.
HINT – you will need to enable Wrap text for the cells which contain long formulae and convert
the cells into Text format.
Second Strategy
The owner can make a renovation in the first quarter of the year, which is cost $12,000. At the
beginning of the year the owner takes a loan for a year period with interest rate 5.5%.
On a separate worksheet, create an amortization table for monthly loan payments assuming
constant payments and interest rate. The worksheet is to have the following layout:
On the top left corner, the loan amount, the annual interest rate and the loan term in months are to be
presented in separate rows, one under another as an input area. Below the input area the monthly
payment should be calculated using PMT function and referencing appropriate cells.
Below the monthly payment the amortization table are to be placed. The table must hold the
following columns: Payment Number, Initial balance, Interest, Principal. Each column must have an
appropriate label. All cells must be formatted properly.
When you have this spreadsheet ready, print a copy of
• the sheet with numbers showing
• and a copy with the formulae showing.
If the owner decides to do the renovation, it will raise his expenses (We assume, they have to repay
the loan, and the loan repayment is tax-deductible). However, the renovation would be expected to
keep the same results for the first quarter, and to increase:
• Product Sales Change rate to 8.00%,
• Repair Services Change rate to 7.00%,
• Installation Change rate to 7.25%,
• Maintenance Change rate to 1.20%
and to decrease:
• Rental Costs Change rate to –4.2%,
• Other Costs Change rate to 0.40%
for the remaining three quarters of the year.
Make these changes to the input area and formulae on the first worksheet, reprint the formulae and
number format of this worksheet, and examine the changes. Make a brief typed recommendation to
the owner as to whether he should make a renovation, based on your comparison of the spreadsheets.
SUBMIT:
• Printout of the first original spreadsheet, number format,
• Printout of the first original spreadsheet, formulae format,
• Printout of the first revised spreadsheet, number format,
• Printout of the first revised spreadsheet, formulae format,
• Printout of the worksheet with amortization table, number format
• Printout of the worksheet with amortization table, formulae format
• Printout of recommendation.
GENERAL INSTRUCTIONS
• All formulae are to be optimized: if it is possible to copy a formula from one cell to another,
it should be copied.
• You are to use cell references everywhere outside of input areas in all spreadsheets. Add
necessary constants to the input area.
• Use 3-D reference to calculate loan repayment on the second spreadsheet for second strategy.
• When you print your assignment, please show gridlines.
ORDER THIS ESSAY HERE NOW AND GET A DISCOUNT !!!
Testimonials
