# Merchant of Venice Cash Flow Forecast This is an Excel assignment for my accounting class, It seems pretty simple to complete. I would love if someone can

Merchant of Venice Cash Flow Forecast This is an Excel assignment for my accounting class, It seems pretty simple to complete. I would love if someone can complete this in one day. EXCEL Assignment 1

Introduction to Excel

(25 points – due on 1/31 at the beginning of class)

Purpose

The purpose of this tutorial is to demonstrate your ability to create well-structured workbooks that

are accurate, easily understood and audited. Please make sure you fully understand each step that

you perform. Just following the instructions and making key strokes to complete this assignment

may cause problems when you work on other assignments.

Knowledge Objectives

This tutorial provides an exposure to the following:

Basic Spreadsheet Skills

Enter and format spreadsheet information

Formulas and Insert Function wizards

Working with multiple spreadsheets or workbooks

Spreadsheet viewing options

Structured Workbooks

Use separate Input, Calculation, and Report worksheets to increase the accuracy of

workbooks

Vocabulary

Spreadsheet

Workbook

A table of cells arranged in rows and columns. Cells can have predefined

relationships with other cells, where changing the contents of one cell

automatically changes related cells.

Powerful spreadsheet programs allow the creation of multiple spreadsheets

within the same file. Files that contain several spreadsheets are called

workbooks. However, while these definitions distinguish between

Spreadsheets and Workbooks, most people use the terms interchangeably!

Structured Spreadsheets

As discussed in the readings cited in the References section, large spreadsheets are

notoriously fraught with errors. To avoid such errors, we need to use three categories of

spreadsheets within a workbook. First, Input spreadsheets serve as a single point of data input for a

workbook. The Input spreadsheet contains the numbers users manually enter or frequently change.

Second, Calculation spreadsheets are used to manipulate the numbers from the Input spreadsheet.

Separate Calculation spreadsheets allow space to break long formulas into smaller, component

formulas that are much easier to audit. Third, Report spreadsheets display information from the

Input and Calculation spreadsheets and present the information in a meaningful manner.

The Merchant of Venice Cash Flow Forecast Case

The Merchant of Venice wants to start a new business importing silk from China. She asks

you to develop forecasts for her new business. This tutorial walks through the steps required to

develop, audit, and print a well-structured workbook that forecasts future cash flows for the

Merchant of Venice. In the following pages of this tutorial, you will:

Create a new workbook with Input, Calculation, and Report spreadsheets.

Create an Input spreadsheet that contains labels and values for the forecasts developed by the

Merchant. The Input spreadsheet will initially look similar to Figure 2. The input sheet will then

be formatted to appear similar to Figure 3.

Create a Calculation spreadsheet that manipulates information from the Input Spreadsheet. The

finished Calculation spreadsheet will look similar to Figure 4.

Create a Report spreadsheet that is formatted as a traditional financial report. Figure 5 depicts

the completed Report spreadsheet.

Print the workbook and create Headers and Footers for each of the spreadsheets.

Perform a “What If” scenario. You will change one of the input values and see how the

spreadsheet is automatically updated.

Create a New Workbook

The first step in creating a well-structured workbook consists of creating a new workbook,

naming spreadsheets, and saving the new workbook.

1) Use your mouse to select the “Blank workbook” icon to open a new Excel workbook.

2) Next, the spreadsheets should be named, as the default Sheet1, Sheet2, and Sheet3 are not very

descriptive.

a) Double select on the “Sheet1” tab in the bottom left corner. The tab will highlight the text

with gray.

b) Once the tab has been selected, or the text is highlighted with gray, simply type the new

name for the tab, which is “Input.”

c) Press “Enter.”

2

d) Add Sheet2 and Sheet3, changing the names to “Calculation” and “Report,” respectively.

Use the “+” in a circle next to the first tab to add each additional sheet. Figure 2 depicts a

workbook with the changed spreadsheet names.

3) Save your spreadsheet by pressing the “Save’ button in the upper left corner. When prompted,

save the spreadsheet with the name “MOV Forecast by YourFirstName YourLastName.” For

example; MOV Forecast by John Doe.

Create an Input Spreadsheet

In her forecasting process, the Merchant identified several key items that affect her business’s

cash flow. Since each of the following items contains new information provided by the Merchant,

they are all included in the Input Spreadsheet.

Outsider Investment – The Merchant plans to receive money from outside investors.

Amount Spent on Ships – The Merchant plans to purchase a boat that will be used to transport

silk from China.

Forecasted Sales, in Bolts – Based on market research, the Merchant forecasted the number of

bolts of silk she plans to sell each year.

Sales Price per Bolt – The market research has also allowed the Merchant to project the price

she will be able to charge for silk in future years.

Cost per Bolt – The Merchant has projected the price she will have to pay for silk.

Deckhand Cost, per Trip – The Merchant has been monitoring the labor market and developed

forecasts for the cost of deckhands.

Cost of Additional Manager – With the anticipated growth, the Merchant plans to hire an

additional manager once sales reach a certain level. Her labor market research has enabled the

Merchant to forecast the cost of an additional manager.

Enter and Format Spreadsheet Titles and Labels

Now that we know what information the Merchant plans to use in forecasting cash flow, we can

begin the Input spreadsheet. Use Figure 2 and Figure 3 as guides for completing this section.

1) Move to your Input spreadsheet and enter the titles that appear in Figure 2. Simply enter the text

as it appears in Figure 2. We will begin to format the text in the next step.

a) To move between spreadsheets, you can either select the spreadsheet tabs with your mouse

or use the and key combinations.

2) Once you have entered the titles and labels, you will quickly notice that the text needs to be

formatted to provide a pleasant presentation of the information.

a) The titles in rows 1 and 2 should be centered over all of the spreadsheet data and their text

formatted.

i) Highlight cells A1 to D1

3

ii) Select the “Merge and Center” button (located in the Alignment box) to merge cells A1

to D1 into a single large cell and center the text within the new cell.

iii) While the new cell is still selected, format the text to be bold and 18-size font.

iv) Use the same process to format the title in row 2, only make the text italic and 14-size

font.

b) The year title and the year numbers should be bold and underlined. To format everything in

row 4 simultaneously:

i) Select the box on the left of the screen that identifies row 4. The entire row will be

highlighted.

ii) Select the “Bold” and “Underline” buttons so everything in row 4 becomes bold and

underlined or key combinations and , respectively.

c) Column A needs to be widened, as the labels exceed the width of the column and run into

columns B and C. In Excel, column widths can be set manually or Excel can automatically

adjust column widths.

i) First, manually resize column A.

(1) Move your mouse to the top of the sheet and point at the line between column A and

B. The normal plus sign cursor will change into a two-headed arrow.

(2) Hold down the left button and drag the column width to the right until the column is

just slightly larger than the text that appears in column A.

ii) You can also let Excel determine the size of the column. This feature is particularly

helpful when the spreadsheet has many long rows that cannot be displayed on the

computer screen at the same time.

(1) Again, move your mouse to the top of the sheet and point at the line between column

A and B. The normal plus sign cursor will change into a two-headed arrow.

(2) Double click when the two-headed arrow is present. The column is automatically

resized.

Enter and Format Spreadsheet Values

With the titles and labels entered and formatted, we are ready to enter the Merchant’s

forecasted values.

1) Input the values depicted in Figure 3.

a) Never manually enter dollar signs ($) into a spreadsheet. If dollar signs are entered into a

cell, Excel treats the contents as text and cannot use the contents of the cell in formulas. The

dollar signs will be added with special formatting buttons in subsequent steps.

4

2) Once the values have been entered, they need to be formatted using the following buttons

(located in the Number box).

a) Format cells that contain dollar values by highlighting the cells and selecting the

“Accounting Number Format” button. It looks like a dollar sign ($).

b) Format the cells containing quantities using the “Comma Style” button.

i) The “Comma Style” button default is to display two decimal places. Use the “Decrease

Decimal” button and format cells that contain quantities to display no decimal places.

3) Once your Input spreadsheet looks like Figure 3, save your workbook and proceed to the next

section of the tutorial.

Create a Calculation Spreadsheet

With the Merchant’s forecasted values entered into the Input spreadsheet, we are ready to

calculate the values required to forecast future cash flows.

Enter and Format the Titles and Labels

The first step in completing the Calculation sheet is to enter and format the required titles

and labels.

1) Enter the titles and labels shown in Figure 4 into your Calculation spreadsheet.

a) Do not enter any of the values displayed in Figure 4, as formulas will be used to display

most of the values depicted in Figure 4.

2) Format the titles and labels as depicted in Figure 4.

a) The Merchant of Venice title is bold and 18-size font.

b) The Cash Flow Forecast Calculations is italic and 14-size font.

c) The Year titles are bold and underlined.

d) The section titles, e.g. Revenue, Cost of Goods Sold, etc., are Bold.

e) Column A has been manually resized so it is fairly narrow. Making column A narrow

provides a nice indentation of the labels entered in column B.

f) Adjust the width of column B.

Enter Formulas

With the titles and labels entered into the Calculation spreadsheet, the formulas required to

display and calculate the values required to forecast cash flows can be entered. The following

narrative provides instruction on how to create the formulas required for each section of the

Calculation spreadsheet.

Revenue

The Revenue section of the Calculation spreadsheet uses formulas to display the information

required to calculate future revenue along with the calculated future revenue.

1) The first two formulas required for the Revenue section are very basic formulas that simply

display information that was entered into the Input sheet.

a) To create a formula that displays the forecasted bolts of silk sold in 1492 from the Input

spreadsheet in the Calculation spreadsheet:

5

i) Move to cell C7 of the Calculation spreadsheet.

ii) Start the formula by typing an equal sign (=). Excel formulas always start with an equal

sign.

iii) Use the mouse to select cell B10 of the Input spreadsheet.

iv) Press “Enter.”

v) Move back to cell C7 of the Calculation spreadsheet and examine “=Input!B10”

displayed in the Formula Bar. What does this formula mean?

(1) The equal sign signifies the start of a formula.

(2) The Input! tells us the following value comes from the Input spreadsheet.

(3) The B10 tells us the cell being referenced from the Input spreadsheet.

b) Create a formula in cell C8 of the Calculation sheet that displays the forecasted sales price

of silk that was entered on the Input spreadsheet.

2) Now that we have formulas that display the quantity of the silk we plan to sell and the

forecasted sales price of the silk, we can calculate the total revenue for the year by multiplying

the quantity by the sales price.

a) Move to cell C9 of the Calculation spreadsheet.

b) Start the formula by entering an equal sign (=).

c) Use your mouse to select cell C7, which contains the bolts of silk sold in 1492.

d) Press the asterisk key (*), which means multiply to Excel.

e) Use your mouse to select cell C8, which contains the sales price of silk for 1492.

i) Notice how the Formula Bar shows the formula that you have created.

f) Press “Enter” to complete your formula.

g) Cell C9 now displays the forecasted revenue for 1492, $8.00.

i) If you have made an error, you may see “#Value” in the cell.

3) While we could go through the process of entering additional revenue formulas for 1493 and

1492, it is much easier to copy the formulas from 1492 into the 1493 and 1494 cells. To copy

the formulas:

a) Highlight the 1492 formulas in cells C7 to C9.

b) Release the mouse button and point at the lower right-hand corner of the highlighted range.

The cursor will change to a small, black plus sign (+).

c) Press the left mouse button and drag the highlighted box into the 1493 and 1494 columns.

Excel will copy the relative formulas from 1492 to the other 2 years.

d) Move among the cells, and study the formulas. See how the formulas changed for each year.

i) If the cells you wanted to copy the formulas to were not adjacent to the destination cells,

you would have to Copy the initial formulas and Paste them to the new region.

4) Once you have all created revenue formulas for all three years, format the cells.

a) Dollar values should be formatted as currency with two decimal places.

b) Quantities should be formatted as comma style with no decimal places.

Cost of Goods Sold

The cost of goods sold calculations are very similar to the revenue calculations. Use

formulas to:

1) Display the bolts of silk sold and the cost of silk from the Input spreadsheet.

2) Calculate the Total Cost of Goods sold by multiplying the number of bolts of silk sold by the

price paid for the silk.

3) Format the cells.

a) Dollar values should be formatted as currency with two decimal places.

6

b) Quantities should be formatted as comma style with no decimal places.

Required Number of Trips to China

The Merchant needs to determine the number of trips to China required to meet demand.

The number of trips is calculated by dividing forecasted demand by the capacity of the Merchant’s

ship. While this formula seems simple at first glance, the formula is complicated by the fact that the

Merchant cannot make partial trips to China.

For example, forecasted demand in 1494 is 51 bolts and the Merchant’s ship has a maximum

capacity of 10 bolts of silk. Simply dividing the two numbers yields 5.1 trips. Can the Merchant

make 5.1 trips? No! To meet demand, the Merchant is required to make 6 trips. The below narrative

describes how to create a formula that will divide 51 by 10, round the answer up to the next integer,

and return an answer of 6.

1) Create a formula that displays forecasted demand from the Input spreadsheet in cell C17. Copy

this formula into cells D17 and E17.

2) The Merchant’s ship has a fixed capacity of 10 bolts of silk. Manually enter 10 into cells C18,

D18, and E18.

a) Wait a minute, all input values belong on the Input spreadsheet! Why am I already breaking

rules? The Ship Capacity and Sales Level Requiring Additional Management values are

entered on the Calculations spreadsheet to demonstrate two points:

i) First, spreadsheets often use fixed values for calculations that cannot change. Instead of

placing these values on the Input spreadsheet, where users will either try to change the

values or question what the values mean, they are better placed on Calculation

spreadsheet.

ii) Second, many spreadsheet users simply imbed fixed values into formulas. However, this

process makes spreadsheets very difficult to audit. By entering the fixed values into their

own clearly labeled rows, users can clearly see what the fixed values mean and where

they come from.

iii) Unfortunately, strong arguments can be made to support why users should be able to

change the ship’s capacity and the level of sales requiring an additional manager. The

Merchant specified that these values were fixed and that she did plan to change these

values in the future.

3) To create a formula that returns the correct number of trips, we are going to use the Insert

Function wizard to create a Roundup function.

a) Select cell C19

b) Select “Formulas” on the main bar then select “Insert Function.”

c) The Insert Function pop-up window allows you to select the type of function to create.

Select a ROUNDUP function, which is a Math & Trig type of function; or type “roundup” in

the box. Then select the “Go” button and choose “ROUNDUP” from its recommended box.

d) Select the “OK” button.

e) A ROUNDUP pop-up window appears. The pop-up window has one box that allows us to

define a function and a second box that allows us to designate the number of decimal places

to round to.

7

i) Select the “Number” box to begin entering the function we want rounded.

ii) We want to divide forecasted demand by the capacity of the ship so select cell C17,

which is the forecasted demand.

iii) Type a forward slash (/), which is the symbol most software programs use as a divide

operator.

iv) Select the capacity of the ship, which is in cell C18.

v) Select the “Num_digits” box in the pop-up window.

vi) The number of trips needs to be rounded to a whole number, so enter “0” to designate

that the function is to contain no decimal places.

vii) Select “OK.”

f) Select cell C19 and take a few moments to examine the syntax used in the new formula.

g) Use the Insert Function wizard to create similar formulas for cells D19 and E19.

i) While you can easily copy the formula from cell C19 into cell D19 and E19, recreating

the formulas using the Insert Function wizard will further your Insert Function wizard

skills.

h) Examine the results in cells C19, D19, and E19 to make sure they are correct.

4) Format the cells.

a) Dollar values should be formatted as currency with two decimal places.

b) Quantities should be formatted as comma style with no decimal places.

5) Please note that the Excel ROUNDUP function is unrelated to the

function where ranchers gather their cattle together for branding,

inoculation, etc.

Deckhand Costs

The Deckhand Costs section requires only a few simple formulas. The only new idea

presented in this section is the process of using other calculations from the Calculation spreadsheet

as input. Use formulas to:

1) Display the number of trips required to China from the calculations you performed in the

previous section.

a) While these formulas require the number of trips to China to be displayed on the spreadsheet

twice, they do allow us to present all the information required to calculate the total cost for

deckhands in a single section of the Calculations spreadsheet.

2) Display the cost of deckhands per trip from Input spreadsheet.

3) Calculate the Total Deckhand costs by multiplying the number trips by cost per trip.

4) Format the cells.

a) Dollar values should be formatted as currency with two decimal places.

b) Quantities should be formatted as comma style with no decimal places.

Additional Management Costs

The Merchant of Venice plans to hire an additional manager once sales exceed 15 bolts of

silk per year. Through the use of a logical IF function, Excel formulas can be created that return

specified variables based on a specified criterion. To create formulas that will automatically decide

when an additional manger needs to be hired and calculate the cost of the additional manager:

1) Enter the sales levels, in bolts of silk, which require an additional manger. The Merchant has

determined she will need assis…

Purchase answer to see full

attachment