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 !!!