FIN 350 UNCG Overview of The Montecarlo Worksheet Please find the Excel Templet of the SCORE Project spreadsheet and the guide of doing the project Last re

FIN 350 UNCG Overview of The Montecarlo Worksheet Please find the Excel Templet of the SCORE Project spreadsheet and the guide of doing the project Last revised 5/2016. See revision notes on last tab.
Step 1 – Enter info about your company in yellow shaded boxes below.
Preparer Name
Company Name
Starting Month
Starting Year
Step 2 – Read the following instructions
This Microsoft Excel Workbook is designed to provide those starting a business or
already running a business with information that will allow them to make a “go
/no-go” decision. It will help a potential entrepreneur project operating profit,
develop a projected income statement, balance sheet and cash flow forecast.
Color-Coding:
Enter Data Here
Adjust as Needed
It is designed for a wide variety of users, from those who have little or no
accounting or Excel experience to those who may be well versed in finance,
accounting and the use of Microsoft Excel.
The workbook contains a number of worksheets, each documented two ways.
Extensive directions and guidance for a particular page or on a specific accounting
topic are found in blue boxes (like this one) on pages that are not selfexplanatory.
The second way this workbook is documented is using Excel comments in a given
cell. Comments are normally hidden from sight. If you see a red triangle in the
upper right corner of a cell, you can hover your mouse over the triangle to see
the note. As your mouse moves away from the triangle, the comment will
disappear.
Comments will have a beige background. Each comment may have a specific
direction for that cell, may be a reminder of something the author believes
-10000
important, or may have some additional information about the accounting topic.
Month 1
Month 2
Month 3
Month 4
Want a guide? Visit our
website:
score.org/startyourbusiness
Need to make the
numbers bigger? Increase the
magnification. This will either be located in
the lower right-hand corner, or in the
Functions bar at the top of the page.
Month 5
Month 6
Month 7
Month 8
Month 9
Month 10 Month 11
Month 12
Start-Up Expenses Year 1
(Starting Balance Sheet)
Start-up Expenses Year 1 (Starting Balance Sheet)
Prepared By:
Owner
Company Name:
Company 1
Fixed Assets
Real Estate-Land
Real Estate-Buildings
Leasehold Improvements
Equipment
Furniture and Fixtures
Vehicles
Other
Total Fixed Assets
Operating Capital
Pre-Opening Salaries and Wages
Prepaid Insurance Premiums
Inventory
Legal and Accounting Fees
Rent Deposits
Utility Deposits
Supplies
Advertising and Promotions
Licenses
Other Initial Start-Up Costs
Working Capital (Cash On Hand)
Total Operating Capital
Total Required Funds
Amount
Depreciation (years)
Notes
Not Depreciated
20
7
7
5
5
5
$
Amount
$
$
Sources of Funding
Percentage
Owner’s Equity
Outside Investors
Additional Loans or Debt
Commercial Loan
Commercial Mortgage
Credit Card Debt
Vehicle Loans
Other Bank Debt
Total Sources of Funding
Total Funding Needed
Existing Businesses ONLY — Calculating Cash on Hand
Cash
+ Accounts Receivable
+ Prepaid Expenses
– Accounts Payable
– Accrued Expenses
Total Cash on Hand
$
20200422030252score_template
Notes
Totals
Loan Rate
Term in Months
Monthly Payments
Notes
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00% $
$

9.00%
84
9.00%
240
7.00%
60
6.00%
48
5.00%
36
Cell D 42 must equal cell C31
$
You are fully funded (Balanced)

See Loan Amortization &
Depreciation Schedule

1-StartingPoint
4/22/2020 5:05 AM
Payroll Year 1
Payroll Year 1
Employee Types
Prepared By:
Owner
Company Name:
Company 1
Number of Owners
/Employees
Average Hourly
Pay (to 2 decimal Estimated
places, ex.
Hrs./Week (per Estimated
$15.23)
person)
Pay/Month (Total)
Owner(s)
Full-Time Employees
Part-Time Employees
Independent Contractors
Total Salaries and Wages
Payroll Taxes and Benefits
Social Security
Medicare
Federal Unemployment Tax (FUTA)
State Unemployment Tax (SUTA)
Employee Pension Programs
Worker’s Compensation
Employee Health Insurance
Other Employee Benefit Programs
Total Payroll Taxes and Benefits
0
Wage Base Limit
$
$
$
$

Percentage of
Salary/Wage
117,000
-7,000
7,000
—–
Total Salaries and Related Expenses
20200422030252score_template
6.20%
1.45%
0.60%
3.45%
0.00%
0.00%
0.00%
0.00%
11.70%
0
$
$
$
$
$
Month 1

Estimated Taxes &
Benefits/Month
(Total)
$
$
$
$
$
$
$
$
$

$

$
Month 2

Month 1
$
Month 3

Month 2
$

$

$
Month 4

Month 3
$

$

$
Month 5

Month 4
$

$

Month 5
$

$

2a-PayrollYear1
$
Month 6

$
Month 7

Month 6
$

$

$
Month 8

Month 7
$

$

$
Month 9

Month 8
$

$

$
Month 10

Month 9
$

$

$
Month 11

Month 10
$

$

Month 12

$
Month 11
$

$

$
Annual Totals

Month 12
$

$

$
$
$
$
$

Annual Totals
$

$
$
$
$
$
$
$
$
$

$

$

4/22/2020 5:05 AM
Payroll Years 1-3
Payroll Years 1-3
Prepared By:
Owner
Company Name:
Company 1
Employee Types
Year 1 Totals Growth Rate 1 to 2 Second Year Growth Rate 2 to 3 Third Year
Owner(s)
Full-Time Employees
Part-Time Employees
Independent Contractors
Total Salaries and Wages
20.0%
20.0%
10.0%
3.0%
$

Social Security
Medicare
Federal Unemployment Tax (FUTA)
State Unemployment Tax (SUTA)
Employee Pension Programs
Worker’s Compensation
Employee Health Insurance
Other Employee Benefit Programs
Total Payroll Taxes and Benefits
20.0%
20.0%
20.0%
20.0%
0.0%
3.0%
3.0%
10.0%
$

Total Salaries and Related Expenses
$

30.0%
30.0%
30.0%
3.0%
$

30.0%
30.0%
30.0%
30.0%
0.0%
3.0%
3.0%
10.0%
$

$

$

$

$

Payroll Taxes and Benefits
20200422030252score_template
2b-PayrollYrs1-3
4/22/2020 5:05 AM
Sales Forecst Year 1
Sales Forecast Year 1
Prepared By:
Company Name:
Owner
Company 1
Complete This Chart First:
Product Lines
Units
COGS Per
Unit
Sales Price Per
Unit
Margin Per Unit
$
$
$
$
$
$
Product Lines
Month 1
Month 2
Month 3

Month 4
Month 5
Month 6
Month 7
Month 8
Month 9
Month 10
Month 11
Month 12
Category
Breakdown
Annual Totals
Category / Total
Product 1
Units Sold
Total Sales
Total COGS
Total Margin












$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 2
Units Sold
Total Sales
Total COGS
Margin












$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 3
Units Sold
Total Sales
Total COGS
Margin












$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 4
Units Sold
Total Sales
Total COGS
Margin












$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 5
Units Sold
Total Sales
Total COGS
Margin












$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0

0

0

0

0

0

0

0

0

0

0

0

$
$
$
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 6
Units Sold
Total Sales
Total COGS
Margin
Total Units Sold
Total Sales $
Total Cost of Goods Sold $
Total Margin $
20200422030252score_template
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
3a-SalesForecastYear1
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
0
0

4/22/2020 5:05 AM
Sales Forecast Years 1-3
Sales Forecast Year 1-3
Prepared by:
Company Name:
Company 1
Owner
Growth Rate Year 1 to Year 2: 10.00%
Growth Rate Year 2 to Year 3: 10.00%
Product Lines
Year 1 Totals
Month 1
Month 2
Month 3
Month 4
Month 5
Month 6
Month 7
Month 8
Month 9
Month 10
Month 11
Month 12
Category
Breakdown
Year 2 Totals
Category /
Total
Product 1
Sold
Total Sales $
Total COGS $
Total Margin $
0

0

0

0

0

0

0

0

0

0

0

0

0

$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 2
Sold
Total Sales $
Total COGS $
Margin $
0

0

0

0

0

0

0

0

0

0

0

0

0

$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 3
Sold
Total Sales $
Total COGS $
Margin $
0

0

0

0

0

0

0

0

0

0

0

0

0

$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 4
Sold
Total Sales
Total COGS $
Margin $
0
0

0

0

0

0

0

0

0

0

0

0

0

0

$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 5
Sold
Total Sales $
Total COGS $
Margin $
0

0

0

0

0

0

0

0

0

0

0

0

0

$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0
0
– $
– $
– $
0
0

0
0

0
0

0
0

0
0

0
0

0
0

0
0

0
0

0
0

0
0

0
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 6
Sold
Total Sales
Total COGS
Margin
Total Units Sold
Total Sales
Total Cost of Goods Sold
Total Margin
$
$
$
$
$
$
20200422030252score_template
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
3b-SalesForecastYrs1-3
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
0
0

4/22/2020 5:05 AM
Sales Forecast Years 1-3
Product Lines
Month 1
Month 2
Month 3
Month 4
Month 5
Month 6
Month 7
Month 8
Month 9
Month 10
Month 11
Category
Year 3 Totals Breakdown
Month 12
Category /
Total
Product 1
Sold
Total Sales
Total COGS
Total Margin
0

0

0

0

0

0

0

0

0

0

0


$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 2
Sold
Total Sales
Total COGS
Margin
0

0

0

0

0

0

0

0

0

0

0

0

$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 3
Sold
Total Sales
Total COGS
Margin
0

0

0

0

0

0

0

0

0

0

0

0

$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 4
Sold
Total Sales
Total COGS
Margin
0

0

0

0

0

0

0

0

0

0

0

0

$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 5
Sold
Total Sales
Total COGS
Margin
0

0

0

0

0

0

0

0

0

0

0

0

$
$
$
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0
0

0
0

0
0

0
0

0
0

0
0

0
0

0
0

0
0

0
0

0
0

0
0

0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
Product 6
Sold
Total Sales
Total COGS
Margin
Total Units Sold
Total Sales $
Total Cost of Goods Sold $
Total Margin $
$
$
$
20200422030252score_template
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
3b-SalesForecastYrs1-3
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
0
0

4/22/2020 5:05 AM
Additional Inputs
Additional Inputs
Prepared By:
Owner
Company Name:
Company 1
Accounts Receivable (A/R) Days Sales Outstanding
Percent of Collections
Year 1
Paid within 30 days
Paid between 30 and 60 days
Paid in more than 60 days
Allowance for bad debt
This should equal 100% —->
100%
0%
0%
0%
100%
Year 2
100%
0%
0%
0%
100%
Year 3
100%
0%
0%
0%
100%
Accounts Payable (A/P)
Percent of Disbursements
Paid within 30 days
Paid between 30 and 60 days
Paid in more than 60 days
This should equal 100% —->
100%
0%
0%
100%
Year 2
100%
0%
0%
100%
Year 3
100%
0%
0%
100%
Year 1
Line of Credit Assumptions
Desired Minimum Cash Balance
Line of Credit Interest Rate
0
8.00%
Additional Fixed Assets Purchases
Fixed Assets
Real Estate
Leasehold Improvements
Equipment
Furniture and Fixtures
Vehicles
Other Fixed Assets
Total Additional Fixed Assets
Income Tax Assumptions
Effective Income Tax Rate – Year 1
Effective Income Tax Rate – Year 2
Effective Income Tax Rate – Year 3
Amortization of Start-Up Costs
Amortization Period in Years
Month 1
Depreciation (years)
20
7
7
5
5
5
$
$
$
$
$
$
$
Month 2

$
Month 3

$
Month 4

$
Month 5

$
Month 6

$
Month 7

$
Month 8

$
Month 9

$
Month 10

$

Month 11
$
Month 12

$
Year 2
Total
Year 1 Totals

$
$
$
$
$
$
$

$
$
Year 3
Total

$
$

0.0%
0.0%
0.0%
3
20200422030252score_template
4-AdditionalInputs
4/22/2020 5:05 AM
Operating Expenses Year 1
Operating Expenses Year 1
Prepared By:
Owner
Company Name:
Company 1
Month 1
Expenses
Advertising
Car and Truck Expenses
Commissions and Fees
Contract Labor (Not included in payroll)
Insurance (other than health)
Legal and Professional Services
Licenses
Office Expense
Rent or Lease — Vehicles, Machinery, Equipment
Rent or Lease — Other Business Property
Repairs and Maintenance
Supplies
Travel, Meals and Entertainment
Utilities
Miscellaneous
Total Expenses
Other Expenses
Depreciation
Interest
Commercial Loan
Commercial Mortgage
Credit Card Debt
Vehicle Loans
Other Bank Debt
Line of Credit
Bad Debt Expense
Total Other Expenses
Total Fixed Operating Expenses
20200422030252score_template
$
$
$
Month 2

$
Month 3

$
Month 4

$
Month 5

$
Month 6

$
Month 7

$
Month 8

$
Month 9

$
Month 10

$
Month 11

$
Month 12

$
Annual Totals

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$













$













$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
5a-OpExYear1
$
$
$
$
$
$
$
$
$
$
$
$
4/22/2020 5:05 AM
Operating Expenses Years 1-3
Operating Expenses Years 1-3
Prepared By:
Company Name:
Owner
Company 1
Line Item
First Year
Advertising
Car and Truck Expenses
Commissions and Fees
Contract Labor (Not included in payroll)
Insurance (other than health)
Legal and Professional Services
Licenses
Office Expense
Rent or Lease — Vehicles, Machinery, Equipment
Rent or Lease — Other Business Property
Repairs and Maintenance
Supplies
Travel, Meals and Entertainment
Utilities
Miscellaneous
Total Expenses
Other Expenses
Depreciation
Interest
Commercial Loan
Commercial Mortgage
Credit Card Debt
Vehicle Loans
Other Bank Debt
Line of Credit
Bad Debt Expense
Total Other Expenses
Total Operating Expenses
20200422030252score_template
$
$
$
Growth Rate 1 to 2

Second Year
3.0%
3.0%
5.0%
3.0%
3.0%
3.0%
5.0%
3.0%
3.0%
3.0%
5.0%
3.0%
3.0%
3.0%
3.0%
$
Growth Rate 2 to 3

Third Year
3.0%
3.0%
5.0%
3.0%
3.0%
3.0%
5.0%
3.0%
3.0%
3.0%
5.0%
3.0%
3.0%
3.0%
3.0%
$







5b-OpExYrs1-3
$
$

$
$

4/22/2020 5:05 AM
Cash Flow Forecast Year 1
Cash Flow Forecast Year 1
Prepared By:
Owner
Company Name:
Company 1
Month 1
Beginning Balance
Cash Inflows
Cash Sales
Accounts Receivable
Total Cash Inflows
Cash Outflows
Investing Activities
New Fixed Asset Purchases
Additional Inventory
Cost of Goods Sold
Operating Activities
Operating Expenses
Payroll
Taxes
Financing Activities
Loan Payments
Owners Distribution
Line of Credit Interest
Line of Credit Repayments
Dividends Paid
Total Cash Outflows
Net Cash Flows
Operating Cash Balance
Line of Credit Drawdown
Ending Cash Balance
Line of Credit Balance
20200422030252score_template
Month 2
$

$

$
$
$
$
$
$
Month 3
$

$

Month 4
$

$

Month 5
$

$

Month 6
$

$

Month 7
$

$

Month 8
$

$

Month 9
$

$

Month 10
$

$

Month 11
$

$

Month 12
$

$

Totals
$

$

$
$
$


























$
$
$












$
$
$
























$
$
$
$
$
$
$

$


$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$
6a-CashFlowYear1

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

4/22/2020 5:05 AM
Cash Flow Forecast Years 1-3
Cash Flow Forecast Years 1-3
Prepared By:
Company Name:
Owner
Company 1
Year 1 Totals
Month 1
Beginning Balance
Cash Inflows
Cash Sales
Accounts Receivable
Total Cash Inflows
Cash Outflows
Investing Activities
New Fixed Asset Purchases
Additional Inventory
Cost of Goods Sold
Operating Activities
Operating Expenses
Payroll
Taxes
Financing Activities
Loan Payments
Owners Distribution
Line of Credit Interest
Line of Credit Repayments
Dividends Paid
Total Cash Outflows
Net Cash Flows
Operating Cash Balance
Line of Credit Drawdown
Ending Cash Balance
Line of Credit Balance
Month 2
$

$

Month 3
$

$

Month 4
$

$

Month 5
$

$

Month 6
$

$

Month 7
$

$

Month 8
$

$

Month 9
$

$

Month 10
$

$

Month 11
$

$

Month 12
$

$

$

$

$
$
$

$
$
$

























$
$
$













$
$
$
$
$
$
$

























$

20200422030252score_template
$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$
6b-CashFlowYrs1-3

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

4/22/2020 5:05 AM
Cash Flow Forecast Years 1-3
Year 2 Totals
Beginning Balance
Cash Inflows
Cash Sales
Accounts Receivable
Total Cash Inflows
$
$
$
Cash Outflows
Investing Activities
New Fixed Asset Purchases
$
Additional Inventory
$
Cost of Goods Sold
$
Operating Activities
Operating Expenses
$
Payroll
$
Taxes
Financing Activities
Loan Payments
$
Owners Distribution
$
Line of Credit Interest
$
Line of Credit Repayments $
Dividends Paid
$
Total Cash Outflows
$
Net Cash Flows
$
Operating Cash Balance
Line of Credit Drawdown
$
Ending Cash Balance
Line of Credit Balance
20200422030252score_template



6b-CashFlowYrs1-3
4/22/2020 5:05 AM
Cash Flow Forecast Years 1-3
Month 1
Beginning Balance
Cash Inflows
Cash Sales
Accounts Receivable
Total Cash Inflows
$
Cash Outflows
Investing Activities
New Fixed Asset Purchases
Additional Inventory
Cost of Goods Sold
Operating Activities
Operating Expenses
Payroll
Taxes
Financing Activities
Loan Payments
Owners Distribution
Line of Credit Interest
Line of Credit Repayments
Dividends Paid
Total Cash Outflows
$
Net Cash Flows
$
Operating Cash Balance
$
Line of Credit Drawdown
$
Ending Cash Balance
$
Line of Credit Balance
$
20200422030252score_template
Month 2

$
Month 3

$
Month 4

$
Month 5

$
Month 6

$
Month 7

$
Month 8

$
Month 9

$
Month 10

$
Month 11

$
Month 12

$
Year 3 Totals













$
$
$

























$
$
$













$
$
$

























$
$
$
$
$
$
$

$


$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

6b-CashFlowYrs1-3
$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

$
$
$
$
$
$

4/22/2020 5:05 AM
Income Statement Year 1
Income Statement Year 1
Prepared By:
Owner
Company Name:
Company 1
Month 1
Revenue
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Total Revenue
Cost of Goods Sold
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Total Cost of Goods Sold
Gross Margin
Payroll
Operating Expenses
Advertising
Car and Truck Expenses
Commissions and Fees
Contract Labor (Not included in payroll)
Insurance (other than health)
Legal and Professional Services
Licenses
Office Expense
Rent or Lease — Vehicles, Machinery, Equipment
Rent or Lease — Other Business Property
Repairs and Maintenance
Supplies
Travel, Meals and Entertainment
Utilities
Miscellaneous
Other Expense 1
Other Expense 2
Total Operating Expenses
Income (Before Other Expenses)
Other Expenses
Amortized Start-up Expenses
Depreciation
Interest
Commercial Loan
Commercial Mortgage
Credit Card Debt
Vehicle Loans
Other Bank Debt
Line of Credit
Bad Debt Expense
Total Other Expenses
Net Income Before Income Tax
Income Tax
Net Profit/Loss
20200422030252score_template
Month 2
$

$
$
$

$

$
$
$


$
$
$
$
$

Month 3
$

$
$
$


$
$

Month 4
$

$
$
$


$
$

Month 5
$

$
$
$


$
$

Month 6
$

$
$
$


$
$

Month 7
$

$
$
$


$
$

Month 8
$

$
$
$


$
$

Month 9
$

$
$
$


$
$

Month 10
$

$
$
$


$
$

Month 11
$

$
$
$


$
$

Month 12
$

$
$
$
$
$
$
$

$
$
$

$
$
$
$
$
$
$
$
$


$
$
$
$
$
$
$
$
$
$
$
$
$
$
$


$
$


$
$

Annual Totals
$
$












$
$













$
$
$
$
$
$
$
$
$
$
$

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
7a-IncomeStatementYear1

Purchase answer to see full
attachment

Submit a Comment