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