Complete a financial planning model

Assignment Help Financial Accounting

Reference no: EM133901273

PROJECT: FINANCIAL PLANNING AND MODELING CASE

PROJECT OBJECTIVES
The project requires you 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). Get online assignment help from subject experts!

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.

REQUIRED:

Financial Planning Model
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)
Sales and merchandise purchase plans with supporting schedules.
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.
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)
Projected contribution format income statements by month and in total.
Projected balance sheet for the quarter as of September30.

Complete Sheet 4 (Statements of Cash Flow)
Projected statements of cash by month and in total - internal budget format.

Graph
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.

Reference no: EM133901273

Questions Cloud

In the event dismissal is not granted : Dismissal of the assault charge on the ground that the owner did not have the ability to harm the server with the pistol that was only loaded with blanks.

Identify the asset id which has the error : Prepare a fixed asset schedule for the Northeast by asset category. Identify the asset ID which has the error and explain the error involved.

Draw conclusions about your hypothesis : Draw conclusions about your hypothesis and Load the data into excel or your statistical package of choice

Treated differently than other stakeholders : Regarding Eula Washington, because of her personal situation, should she be treated differently than other stakeholders?

Complete a financial planning model : Complete a financial planning model that is derived from a set of assumptions and link together statements of projected cash, income and balance sheets

Explain how case relates to civil liberties and civil rights : Describe the key facts of the case and the constitutional issues involved. Explain how the case relates to civil liberties and civil rights.

What was the companys revenue for the year : Seok Kyung Company began the year with owner's equity of 15.000. During the year. What was the company's revenue for the year?

Successor in interest to prosecution in criminal case : Was does not meet the definition of unavailable declarant because his location is known. Hunter is not successor in interest to prosecution in criminal case.

What must be included in the result and discussion : What must be included in the result and discussion section of the research paper - Select a research result and state whether it includes the necessary elements

User Account

All Pages