GENERAL INFORMATION AND GUIDELINES
This project consists of two parts: Part I requires completing an Excel-based financial model template; and Part II involves decision-making and analysis extensions, including cost-volume-profit and sensitivity analysis, operating income versus cash flow, benchmarking and planning issues. This project requires application of Excel in managerial planning and decision-making, but also emphasizes critical thinking and general business skills. Specifically, the case has the following objectives: (1) to use Excel within a planning and decision context; (2) to integrate a range of managerial and financial accounting concepts; (3) to reinforce financial statement relationships through linking projected financial statements; and (4) to stimulate student appreciation for accounting information.
A significant way in business professionals can add more value within organizations is by partnering with managers in modeling business problems to support managerial decisions. Indeed, the skills that Institute of Management Accountants (IMA) members believe are most important in business include strategic planning, financial and economic analysis and computer skills. Relevant costs and decision-making, short-range planning, cost behavior, cost-volume-profit analysis, and building financial models for decision-making are highly ranked skills. Prior studies suggest that important knowledge, skills and abilities include the following: budgeting; computerized worksheets; relationship between financial statements; and financial and economic analysis.
BUSINESS PLANNING MODELS
A model is a simplified representation of reality. A financial model can be defined as an accurate, reliable simulation of relations among relevant costs, benefits, value, and risk that is useful for supporting business decisions. Financial models use a set of assumptions, or parameters, which often culminate in budgeted, or projected financial statements. Such models are extensively used in business planning, decision-making and for obtaining financing.[footnoteRef:1] This case is designed to further develop Excel-based planning and decision-analysis skills. [1: For a more detailed discussion of accounting and auditing issues related to prospective financial statements see the AICPA Audit and Accounting Guide: Guide for Prospective Financial Information.]
Each team will complete a financial planning model that is derived from a set of assumptions and culminates in statements of projected cash, income and balance sheets.[footnoteRef:2] Your Excel financial model is structured to show the following sections: (1) Assumptions; (2) Schedules; and (3) Projected Statements. (1) Assumptions contains baseline assumptions. Within Assumptions, you will modify certain input data to perform sensitivity analyses in Part II of the case. (2) Schedules will include supporting schedules for detailed computations, which in turn will link to the (3) Projected Statements. To work properly, all cells of your model should be linked together via formulas, or cell references, across all three sections. [2: The statement of projected cash is presented in a cash budget format (see text, Noreen et al., 4th Ed., Chapter 9, page 406, Schedule 8), but it can alternatively be presented as a projected Statement of Cash Flows in a format consistent with SFAS 95.]
In Part I of the Excel case, you will finish the financial model, which has already been partially completed for you. In Part II, you will use your model to perform sensitivity and what-if analysis to support decision making. Sensitivity analysis involves changing sensitive, or critical, assumptions to answer “what-if” questions, such as the impact of changing sales price on profitability. To accomplish this, the financial model must articulate (i.e., interconnect, or link) across all schedules and statements. Thus, a change in an assumption should flow through and automatically update all related schedules and projected statements. This requires the use of formulas within the cells to calculate the required values, rather than hard coding values into the cells. Co
ect construction of formulas is essential to accurately portray the relations of the underlying assumptions and to update expected financial results.
Formulas should only contain cell references from the data input source (1) Assumptions and other related mathematical computations from (2) Schedules and (3) Projected Statements. This is essential to conduct the sensitivity analyses. SIGNIFICANT POINTS WILL BE DEDUCTED FOR HARDCODED VALUES SINCE THE FINANCIAL MODEL WILL NOT WORK PROPERLY. If the model does not work, then partial credit will be awarded for Part I of the project and zero credit will be awarded for all of Part II. If the tool is inco
ect, then the analysis is as well. Therefore, it is critical that the financial model works appropriately before moving forward to Part II of the project. CHECK FIGURES ARE PROVIDED ON D2L – use these to test your model. Do not move forward until your model works. If modeling problems persist, please ask.
The detailed case information follows. After this discussion, the requirements for Part I and Part II of the case are discussed in greater depth. Finally, submission guidelines are provided. It is critical for each group to follow the submission guidelines exactly - points will be deducted for not following the guidelines
NOTE: Professionally presented documents are expected. A sloppy, unprofessional deliverable will be graded as such and the team will have significant point deductions. Complete the deliverable as if you were providing it to upper management in your cu
ent (future) working environment(s). Upper management would expect only the best deliverable…so does your instructor!
TABLET, INC. – FINANCIAL MODELING CASE
Congratulations - You’re Hired!
Karen Pearson formed Tablet, Inc. (TAB) in 2012 when she obtained an exclusive franchise to nationally distribute a hand-held computer device that provides effortless electronic communication with standard personal computers and other electronic devices. Recent high sales growth of the base model pen-based input device (BASE), along with expected sales growth for a new premium model (PREM), requires adding new management team members. The Company hires you to assume direct responsibility for financial planning and analysis (FP&A) activities. Your first assignment is to prepare a financial plan for the next three months, starting July 1, XXXXXXXXXXSince you are anxious to make a favorable impression on Ms. Pearson, the President of TAB, you immediately begin to assemble relevant information.
Ms. Pearson is keenly aware of other growth-oriented companies within the high technology sector that have run out of cash and gone bankrupt. Having seen potentially viable businesses fail in the past particularly concerns Ms. Pearson and, consequently, she wants to ensure that sufficient cash will be available to accommodate TAB’s expected growth. Thus, on your first day, the President meets with you to emphasize why the Company must thoroughly assess the impact of TAB’s planned growth on cash flow. She would like to present the financial plan to TAB’s board of directors and has requested that you construct a financial model to support sensitivity analyses and address questions from top management and board members.
Other Company Information
You recall from ACTG 552 at Clarion University that the starting point for a financial plan is a reliable sales forecast. Thus, you consult with Phil Davis, the sales manager, and an outside market researcher. Phil has studied sales and economic trends, as well as changes within the highly competitive handheld computing industry to establish the unit sales forecast, as presented in Exhibit 1 (next page). Also included in this sales forecast are the sales mix and sales price information and sensitivities.
As shown in Exhibit 1, total monthly unit sales volume should continue increasing through the third quarter and into the fourth, but with sales mix shifting toward the new premium model. In addition, you determine through discussions with the accounts receivable manager that all sales to retailers are on account, with no discount, and payable within 15 days. However, the manager has found that 20% of a month’s sales are collected by month-end. An additional 50% is collected in the month following sale, and the remaining 30% is collected in the second month following sale. Thus far, bad debts have been negligible.
Since TAB’s policy is to never stock out of inventory, and potentially forfeit market share to competitors, the Company maintains solid inventory levels - desired ending inventories are equal to 75% of the next month’s sales in units. Prior to June, TAB sold only the basic model (BASE) at a price of $215 per unit. The BASE model costs TAB, Inc. $145 each from a contracted, overseas manufacturer and it pays for purchases as follows: 50% in the month of purchase and the remaining 50% the following month. In June, TAB began ca
ying a premium model (PREM), which sells for $350 per unit and costs $200 from the overseas manufacturer. The cu
ent sales mix is 80 percent BASE and 20 percent PREM. However, going forward the Company expects this mix to shift toward the premium model, along with ongoing competitive pricing pressure (as reflected in Exhibit 1). The inventory cost flow assumption is first-in, first-out (FIFO method). NOTE: this must be modeled when determining COGS.
EXHIBIT 1
TAB, Inc.’s Sales Forecast and Other Sales Information
Unit Sales
Actual Sales Volume
Forecast Sales Volume
April
May
June
July
August
Septembe
Octobe
Total
7,650
8,400
9,200
10,200
11,400
12,700
13,200
BASE
7,650
8,400
7,360
7,956
8,550
9,017
9,240
PREM
n/a
n/a
1,840
2,244
2,850
3,683
3,960
Sales Mix
Sensitivity
2%
3%
4%
1%
Sales Mix %
BASE
80%
78%
75%
71%
70%
PREM
20%
22%
25%
29%
30%
Sales Price
Sensitivity
-2.00%
-2.00%
-2.00%
-2.00%
Sales Price
BASE
$ 215
$ 211
$ 206
$ 202
$ 198
PREM
$ 350
$ 343
$ 336
$ 329
$ 323
Unit Cost
BASE
$ 145
$ 145
$ 145
$ 145
$ 145
PREM
$ 200
$ 200
$ 200
$ 200
$ 200
Exhibit 2 presents the Company’s monthly operating expenses (organized by cost behavior). TAB pays all operating expenses during the month incu
ed, with the exception of depreciation and insurance expenses.
EXHIBIT 2
TAB’s Planned Operating Expenses per Month
Variable:
Sales commissions (% of sales dollars)
XXXXXXXXXX% of sales
Shipping & handling (per unit sold)
$15.00 per unit
Fixed:
(per month)
Wages and salaries