Assignment Prompt & Instructions
Need this project completed
– ## F2025Assignt2KeyCheckfigures.xlsx
– ## F2025Excelprojectinstructions6.pdf
– F2025EXCELPROJ1.xls
**## F2025Assignt2KeyCheckfigures.xlsx**
## Sheet1
| | | | | |
| — | — | — | — | — |
| Key Check Figures |
| Schedules |
| Schedule B | July | August | September | Sched E15 |
| Total collections | $2,274,993 | $2,572,479 | $2,858,704 | $929,200 |
| Schedule D |
| COGS | $1,692,384 | $1,909,842 | $2,156,968 |
| Schedule E |
| Total Payments | $2,076,010 | $1,985,264 | $2,177,350 |
| Projected Income statements |
| Net Income before Taxes | $125,877 | $161,323 | $203,086 |
| Projected Balance Seet | September |
| Accounts Receivable | $3,079,662 |
| Inventory | $1,587,432 |
| Fixed Assets, net of depreciation | $1,522,000 |
| Total Assets | $6,230,594 |
| Accounts Payable | $896,796 |
| Projected SCF | July | August | September |
| Cash excess (deficit) | -$527,715 | -$166,635 | -$206,252 |
**## F2025Excelprojectinstructions6.pdf**
1
EXCEL PROJECT: FINANCIAL PLANNING AND MODELING CASE
(100 points)
GENERAL INSTRUCTIONS: READ THIS ENTIRE ASSIGNMENT CAREFULLY
BEFORE STARTING YOUR WORK. Failure to follow the instructions will result in point
deductions. Late Projects will be subject to a 10 point for each day (or a fraction of a day) and
will not be accepted after the third day following the deadline.
Download all files from the Canvas course page, including the accompanying excel template,
and save
them. DO NOT COPY AND PASTE THE EXCEL TEMPLATE INTO A NEW
WORKBOOK AND DO NOT WORK THROUGH THE WEB BROWSER. Use formulas
in all spreadsheet cells and reference given data cells as necessary. You will NOT earn credit
for ENTERING HARDCODED VALUES. BE SURE TO REGULARLY SAVE YOUR
WORK. Excuses about lost work will not be accepted. Maintain a second (backup) copy of
your file as well.
PROJECT OBJECTIVES
The project requires to complete an Excel-based financial model template. Such models are
extensively used in business planning, decision-making, and for obtaining business financing.,
making but also emphasizes critical thinking and general business skills. Specifically, the case
has the following objectives: (1) to obtain experience using Excel within a planning and decision
context; (2) to integrate a range of managerial and financial accounting concepts; and (3) to
reinforce financial statement relationships through linking projected financial statements.
You will complete a financial planning model that is derived from a set of assumptions and link
together statements of projected cash, income and balance sheets that articulate. An Excel
template, which is available for downloading, is structured with a separate worksheet (or tab) for
each of the following sections: Sheet (1) Assumptions; Sheet (2) Supporting Schedules; Sheet (3)
Projected Income Statement and Balance Sheet; and Sheet (4) Projected Cash Flow.
Sheet (2) provides supporting schedules for detailed computations, which in turn links to the
projected income statements and balance sheet contained within Sheet (3), along with projected
cash flow in Sheet (4).
2
To work correctly, all cells of your model should be linked together via formulas, or cell
references, across all worksheets. (A 5th sheet labeled, Graph contains data to produce a cost-
volume-profit graph from current period, baseline information. You should use the ChartWizard
in Excel to create the graph. The Excel template already contains the key assumptions as well as
some formulas.
Within a real-life practice situation, you would need to independently develop and obtain support
for the necessary assumptions that have been provided to you. In Part I of the Excel case, you
should complete the model formulas and linkages to Sheets (2), (3) and (4
To accomplish this, the financial model must articulate (i.e., interconnect, or link) across all
schedules and statements. Thus, a change in assumption should flow through and automatically
update all related schedules in Sheet (2) and projected statements in Sheets (3) and (4). This
requires entering formulas within the cells to calculate the required values, rather than
hardcoding values into the cells.
OUT-WEST PRODUCTS, INC. CASE
You’re Hired!
Cynthia Valley formed Out-West Products, Inc. (OWP) in 2020 when she obtained an exclusive
franchise to nationally distribute a pen-based input device that provides effortless communication
with standard personal computers. Recent high sales growth of the base model pen-based input
device (PID-B), along with expected sales growth for a new premium model (PID-P), requires
adding new management team members. The Company hires you as a management trainee to
assume direct responsibility for financial planning activities. Your first assignment is to prepare
a financial plan for the next three months, starting July 1.
CASH COOLECTION PATTERN is as follows:
30% of a month’s sales are collected by month-end. An additional 40% is collected in the
month following, and the remaining 30% is collected in the second month following sale.
Thus far bad debts have been negligible.
Since OWP’s policy is to never stock out of its pen-based input devices (PIDs), and potentially
forfeit market share to competitors, the Company maintains fairly high inventory levels.
Therefore, desired ending inventories are equal to 70% of the next month’s sales in units.
3
Prior to June, OWP sold only the basic model PID-B at a price of $230 per unit. The PID-B
costs OutWest Products $155 each from the manufacturer and it pays for purchases as follows:
CASH Disbursements PATTERN is as follows:
60% in the month of purchase and the remaining 40% the following month.
The company’s monthly operating expenses (organized by cost behavior) are also provided in the
Assumptions Sheet of the excel template. All operating expenses are paid during the month, in
cash, with the exception of depreciation and insurance expenses.
New fixed assets, including personal computers and office furniture, will be purchased
during September for $90,000 cash. Ignore depreciation for these newly acquired since they
are insignificant. The Company declares dividends of $20,000 each quarter, payable in the
first month of the following quarter.
OWP’s actual balance sheet at June 30, 2020 is provided at the end of the template’s
Assumptions Sheet.
However, the Company also desires a minimum ending cash balance each month of $35,000
to meet regular operating expenses. Assume borrowings occur at the beginning of the
period. To further simplify, assume no tax consequences.
REQUIRED:
Financial Planning Model (100 Points)
Download the Excel template from your Blackboard course page – DO NOT MODIFY THE
FORMAT OF THE TEMPLATE. Save the file and do not attempt to complete the template
directly from the course page and do not copy and paste sheets into a new workbook. Case
assumptions have already been provided in the template’s assumptions sheet. If necessary, it
may be helpful to review Chapter 3 (CVP Analysis) and Chapter 6 (Budgeting) in your textbook
prior to beginning the project. Be sure to regularly save your work as you complete the
model. Remember that you must use formulas so that any changes in input data
automatically update your model. Otherwise, your model will not work adequately, and
you will lose points. First, complete the sheets of the Excel template, as indicated below.
Complete Sheet 2 (Schedules)
1) Sales and merchandise purchase plans with supporting schedules. (40 points)
4
a) A sales plan by month and in total – including a schedule of projected cash collections
from sales
and accounts receivable, by month and in total.
b) A purchases plan in units and in dollars, including a schedule of projected cash payments
for
purchases, by month and in total.
[Note: The cost of inventory on hand is released to cost of goods sold before costs for the
purchase of additional units (i.e., use a FIFO cost flow assumption for all months).]
Complete Sheet 3 (Inc. St. and Bal. Sheet)
2) Projected contribution format income statements by month and in total. (20 points)
3) Projected balance sheet for the quarter as of September30. (20 points)
Complete Sheet 4 (Statements of Cash Flow)
4) Projected statements of cash by month and in total – internal budget format. (15 points)
Graph (5 points)
Use the data in the Graph worksheet to create a cost-volume-profit graph.
Highlight the cells A13:H16. Click insert and Select a line chart type for the CVP graph (your
choice of style).
Be sure that your graph closely resembles a standard CVP graph or points will not be awarded.
**F2025EXCELPROJ1.xls**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/f2025excelproj1-xls-10456445)
**F2025EXCELPROJ1.xls**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/f2025excelproj1-xls-10456445)
**## F2025Assignt2KeyCheckfigures.xlsx**
## Sheet1
| | | | | |
| — | — | — | — | — |
| Key Check Figures |
| Schedules |
| Schedule B | July | August | September | Sched E15 |
| Total collections | $2,274,993 | $2,572,479 | $2,858,704 | $929,200 |
| Schedule D |
| COGS | $1,692,384 | $1,909,842 | $2,156,968 |
| Schedule E |
| Total Payments | $2,076,010 | $1,985,264 | $2,177,350 |
| Projected Income statements |
| Net Income before Taxes | $125,877 | $161,323 | $203,086 |
| Projected Balance Seet | September |
| Accounts Receivable | $3,079,662 |
| Inventory | $1,587,432 |
| Fixed Assets, net of depreciation | $1,522,000 |
| Total Assets | $6,230,594 |
| Accounts Payable | $896,796 |
| Projected SCF | July | August | September |
| Cash excess (deficit) | -$527,715 | -$166,635 | -$206,252 |
**## F2025Excelprojectinstructions6.pdf**
1
EXCEL PROJECT: FINANCIAL PLANNING AND MODELING CASE
(100 points)
GENERAL INSTRUCTIONS: READ THIS ENTIRE ASSIGNMENT CAREFULLY
BEFORE STARTING YOUR WORK. Failure to follow the instructions will result in point
deductions. Late Projects will be subject to a 10 point for each day (or a fraction of a day) and
will not be accepted after the third day following the deadline.
Download all files from the Canvas course page, including the accompanying excel template,
and save
them. DO NOT COPY AND PASTE THE EXCEL TEMPLATE INTO A NEW
WORKBOOK AND DO NOT WORK THROUGH THE WEB BROWSER. Use formulas
in all spreadsheet cells and reference given data cells as necessary. You will NOT earn credit
for ENTERING HARDCODED VALUES. BE SURE TO REGULARLY SAVE YOUR
WORK. Excuses about lost work will not be accepted. Maintain a second (backup) copy of
your file as well.
PROJECT OBJECTIVES
The project requires to complete an Excel-based financial model template. Such models are
extensively used in business planning, decision-making, and for obtaining business financing.,
making but also emphasizes critical thinking and general business skills. Specifically, the case
has the following objectives: (1) to obtain experience using Excel within a planning and decision
context; (2) to integrate a range of managerial and financial accounting concepts; and (3) to
reinforce financial statement relationships through linking projected financial statements.
You will complete a financial planning model that is derived from a set of assumptions and link
together statements of projected cash, income and balance sheets that articulate. An Excel
template, which is available for downloading, is structured with a separate worksheet (or tab) for
each of the following sections: Sheet (1) Assumptions; Sheet (2) Supporting Schedules; Sheet (3)
Projected Income Statement and Balance Sheet; and Sheet (4) Projected Cash Flow.
Sheet (2) provides supporting schedules for detailed computations, which in turn links to the
projected income statements and balance sheet contained within Sheet (3), along with projected
cash flow in Sheet (4).
2
To work correctly, all cells of your model should be linked together via formulas, or cell
references, across all worksheets. (A 5th sheet labeled, Graph contains data to produce a cost-
volume-profit graph from current period, baseline information. You should use the ChartWizard
in Excel to create the graph. The Excel template already contains the key assumptions as well as
some formulas.
Within a real-life practice situation, you would need to independently develop and obtain support
for the necessary assumptions that have been provided to you. In Part I of the Excel case, you
should complete the model formulas and linkages to Sheets (2), (3) and (4
To accomplish this, the financial model must articulate (i.e., interconnect, or link) across all
schedules and statements. Thus, a change in assumption should flow through and automatically
update all related schedules in Sheet (2) and projected statements in Sheets (3) and (4). This
requires entering formulas within the cells to calculate the required values, rather than
hardcoding values into the cells.
OUT-WEST PRODUCTS, INC. CASE
You’re Hired!
Cynthia Valley formed Out-West Products, Inc. (OWP) in 2020 when she obtained an exclusive
franchise to nationally distribute a pen-based input device that provides effortless communication
with standard personal computers. Recent high sales growth of the base model pen-based input
device (PID-B), along with expected sales growth for a new premium model (PID-P), requires
adding new management team members. The Company hires you as a management trainee to
assume direct responsibility for financial planning activities. Your first assignment is to prepare
a financial plan for the next three months, starting July 1.
CASH COOLECTION PATTERN is as follows:
30% of a month’s sales are collected by month-end. An additional 40% is collected in the
month following, and the remaining 30% is collected in the second month following sale.
Thus far bad debts have been negligible.
Since OWP’s policy is to never stock out of its pen-based input devices (PIDs), and potentially
forfeit market share to competitors, the Company maintains fairly high inventory levels.
Therefore, desired ending inventories are equal to 70% of the next month’s sales in units.
3
Prior to June, OWP sold only the basic model PID-B at a price of $230 per unit. The PID-B
costs OutWest Products $155 each from the manufacturer and it pays for purchases as follows:
CASH Disbursements PATTERN is as follows:
60% in the month of purchase and the remaining 40% the following month.
The company’s monthly operating expenses (organized by cost behavior) are also provided in the
Assumptions Sheet of the excel template. All operating expenses are paid during the month, in
cash, with the exception of depreciation and insurance expenses.
New fixed assets, including personal computers and office furniture, will be purchased
during September for $90,000 cash. Ignore depreciation for these newly acquired since they
are insignificant. The Company declares dividends of $20,000 each quarter, payable in the
first month of the following quarter.
OWP’s actual balance sheet at June 30, 2020 is provided at the end of the template’s
Assumptions Sheet.
However, the Company also desires a minimum ending cash balance each month of $35,000
to meet regular operating expenses. Assume borrowings occur at the beginning of the
period. To further simplify, assume no tax consequences.
REQUIRED:
Financial Planning Model (100 Points)
Download the Excel template from your Blackboard course page – DO NOT MODIFY THE
FORMAT OF THE TEMPLATE. Save the file and do not attempt to complete the template
directly from the course page and do not copy and paste sheets into a new workbook. Case
assumptions have already been provided in the template’s assumptions sheet. If necessary, it
may be helpful to review Chapter 3 (CVP Analysis) and Chapter 6 (Budgeting) in your textbook
prior to beginning the project. Be sure to regularly save your work as you complete the
model. Remember that you must use formulas so that any changes in input data
automatically update your model. Otherwise, your model will not work adequately, and
you will lose points. First, complete the sheets of the Excel template, as indicated below.
Complete Sheet 2 (Schedules)
1) Sales and merchandise purchase plans with supporting schedules. (40 points)
4
a) A sales plan by month and in total – including a schedule of projected cash collections
from sales
and accounts receivable, by month and in total.
b) A purchases plan in units and in dollars, including a schedule of projected cash payments
for
purchases, by month and in total.
[Note: The cost of inventory on hand is released to cost of goods sold before costs for the
purchase of additional units (i.e., use a FIFO cost flow assumption for all months).]
Complete Sheet 3 (Inc. St. and Bal. Sheet)
2) Projected contribution format income statements by month and in total. (20 points)
3) Projected balance sheet for the quarter as of September30. (20 points)
Complete Sheet 4 (Statements of Cash Flow)
4) Projected statements of cash by month and in total – internal budget format. (15 points)
Graph (5 points)
Use the data in the Graph worksheet to create a cost-volume-profit graph.
Highlight the cells A13:H16. Click insert and Select a line chart type for the CVP graph (your
choice of style).
Be sure that your graph closely resembles a standard CVP graph or points will not be awarded.
**F2025EXCELPROJ1.xls**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/f2025excelproj1-xls-10456445)
**## F2025Assignt2KeyCheckfigures.xlsx**
## Sheet1
| | | | | |
| — | — | — | — | — |
| Key Check Figures |
| Schedules |
| Schedule B | July | August | September | Sched E15 |
| Total collections | $2,274,993 | $2,572,479 | $2,858,704 | $929,200 |
| Schedule D |
| COGS | $1,692,384 | $1,909,842 | $2,156,968 |
| Schedule E |
| Total Payments | $2,076,010 | $1,985,264 | $2,177,350 |
| Projected Income statements |
| Net Income before Taxes | $125,877 | $161,323 | $203,086 |
| Projected Balance Seet | September |
| Accounts Receivable | $3,079,662 |
| Inventory | $1,587,432 |
| Fixed Assets, net of depreciation | $1,522,000 |
| Total Assets | $6,230,594 |
| Accounts Payable | $896,796 |
| Projected SCF | July | August | September |
| Cash excess (deficit) | -$527,715 | -$166,635 | -$206,252 |
**## F2025Excelprojectinstructions6.pdf**
1
EXCEL PROJECT: FINANCIAL PLANNING AND MODELING CASE
(100 points)
GENERAL INSTRUCTIONS: READ THIS ENTIRE ASSIGNMENT CAREFULLY
BEFORE STARTING YOUR WORK. Failure to follow the instructions will result in point
deductions. Late Projects will be subject to a 10 point for each day (or a fraction of a day) and
will not be accepted after the third day following the deadline.
Download all files from the Canvas course page, including the accompanying excel template,
and save
them. DO NOT COPY AND PASTE THE EXCEL TEMPLATE INTO A NEW
WORKBOOK AND DO NOT WORK THROUGH THE WEB BROWSER. Use formulas
in all spreadsheet cells and reference given data cells as necessary. You will NOT earn credit
for ENTERING HARDCODED VALUES. BE SURE TO REGULARLY SAVE YOUR
WORK. Excuses about lost work will not be accepted. Maintain a second (backup) copy of
your file as well.
PROJECT OBJECTIVES
The project requires to complete an Excel-based financial model template. Such models are
extensively used in business planning, decision-making, and for obtaining business financing.,
making but also emphasizes critical thinking and general business skills. Specifically, the case
has the following objectives: (1) to obtain experience using Excel within a planning and decision
context; (2) to integrate a range of managerial and financial accounting concepts; and (3) to
reinforce financial statement relationships through linking projected financial statements.
You will complete a financial planning model that is derived from a set of assumptions and link
together statements of projected cash, income and balance sheets that articulate. An Excel
template, which is available for downloading, is structured with a separate worksheet (or tab) for
each of the following sections: Sheet (1) Assumptions; Sheet (2) Supporting Schedules; Sheet (3)
Projected Income Statement and Balance Sheet; and Sheet (4) Projected Cash Flow.
Sheet (2) provides supporting schedules for detailed computations, which in turn links to the
projected income statements and balance sheet contained within Sheet (3), along with projected
cash flow in Sheet (4).
2
To work correctly, all cells of your model should be linked together via formulas, or cell
references, across all worksheets. (A 5th sheet labeled, Graph contains data to produce a cost-
volume-profit graph from current period, baseline information. You should use the ChartWizard
in Excel to create the graph. The Excel template already contains the key assumptions as well as
some formulas.
Within a real-life practice situation, you would need to independently develop and obtain support
for the necessary assumptions that have been provided to you. In Part I of the Excel case, you
should complete the model formulas and linkages to Sheets (2), (3) and (4
To accomplish this, the financial model must articulate (i.e., interconnect, or link) across all
schedules and statements. Thus, a change in assumption should flow through and automatically
update all related schedules in Sheet (2) and projected statements in Sheets (3) and (4). This
requires entering formulas within the cells to calculate the required values, rather than
hardcoding values into the cells.
OUT-WEST PRODUCTS, INC. CASE
You’re Hired!
Cynthia Valley formed Out-West Products, Inc. (OWP) in 2020 when she obtained an exclusive
franchise to nationally distribute a pen-based input device that provides effortless communication
with standard personal computers. Recent high sales growth of the base model pen-based input
device (PID-B), along with expected sales growth for a new premium model (PID-P), requires
adding new management team members. The Company hires you as a management trainee to
assume direct responsibility for financial planning activities. Your first assignment is to prepare
a financial plan for the next three months, starting July 1.
CASH COOLECTION PATTERN is as follows:
30% of a month’s sales are collected by month-end. An additional 40% is collected in the
month following, and the remaining 30% is collected in the second month following sale.
Thus far bad debts have been negligible.
Since OWP’s policy is to never stock out of its pen-based input devices (PIDs), and potentially
forfeit market share to competitors, the Company maintains fairly high inventory levels.
Therefore, desired ending inventories are equal to 70% of the next month’s sales in units.
3
Prior to June, OWP sold only the basic model PID-B at a price of $230 per unit. The PID-B
costs OutWest Products $155 each from the manufacturer and it pays for purchases as follows:
CASH Disbursements PATTERN is as follows:
60% in the month of purchase and the remaining 40% the following month.
The company’s monthly operating expenses (organized by cost behavior) are also provided in the
Assumptions Sheet of the excel template. All operating expenses are paid during the month, in
cash, with the exception of depreciation and insurance expenses.
New fixed assets, including personal computers and office furniture, will be purchased
during September for $90,000 cash. Ignore depreciation for these newly acquired since they
are insignificant. The Company declares dividends of $20,000 each quarter, payable in the
first month of the following quarter.
OWP’s actual balance sheet at June 30, 2020 is provided at the end of the template’s
Assumptions Sheet.
However, the Company also desires a minimum ending cash balance each month of $35,000
to meet regular operating expenses. Assume borrowings occur at the beginning of the
period. To further simplify, assume no tax consequences.
REQUIRED:
Financial Planning Model (100 Points)
Download the Excel template from your Blackboard course page – DO NOT MODIFY THE
FORMAT OF THE TEMPLATE. Save the file and do not attempt to complete the template
directly from the course page and do not copy and paste sheets into a new workbook. Case
assumptions have already been provided in the template’s assumptions sheet. If necessary, it
may be helpful to review Chapter 3 (CVP Analysis) and Chapter 6 (Budgeting) in your textbook
prior to beginning the project. Be sure to regularly save your work as you complete the
model. Remember that you must use formulas so that any changes in input data
automatically update your model. Otherwise, your model will not work adequately, and
you will lose points. First, complete the sheets of the Excel template, as indicated below.
Complete Sheet 2 (Schedules)
1) Sales and merchandise purchase plans with supporting schedules. (40 points)
4
a) A sales plan by month and in total – including a schedule of projected cash collections
from sales
and accounts receivable, by month and in total.
b) A purchases plan in units and in dollars, including a schedule of projected cash payments
for
purchases, by month and in total.
[Note: The cost of inventory on hand is released to cost of goods sold before costs for the
purchase of additional units (i.e., use a FIFO cost flow assumption for all months).]
Complete Sheet 3 (Inc. St. and Bal. Sheet)
2) Projected contribution format income statements by month and in total. (20 points)
3) Projected balance sheet for the quarter as of September30. (20 points)
Complete Sheet 4 (Statements of Cash Flow)
4) Projected statements of cash by month and in total – internal budget format. (15 points)
Graph (5 points)
Use the data in the Graph worksheet to create a cost-volume-profit graph.
Highlight the cells A13:H16. Click insert and Select a line chart type for the CVP graph (your
choice of style).
Be sure that your graph closely resembles a standard CVP graph or points will not be awarded.
**F2025EXCELPROJ1.xls**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/f2025excelproj1-xls-10456445)
– 1
– 2
– 3