Question

(2) Schedules
Complete the sales and merchandise purchase plans with supporting schedules:
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 (2 schedules).
b) An inventory purchases plan in units and in dollars, including a schedule of projected cash payments for purchases, by month and in total (3 schedules).
NOTE: All schedules and budgets should be calculated based on information entered into the (1) Assumptions and other schedules. No hardcoding of numbers should be included on these schedules. All schedules should automatically update given changes to the assumptions.

Donna McMillan formed TABLETS, Inc. (TABS) in 2018 when she obtained an exclusive franchise to nationally distribute a tablet-based computer-type device that provides effortless electronic communication with standard personal computers and other electronic devices. Recent high sales growth of the base model device (BASE), along with expected sales growth for a new premium model (PREM), requires adding new management team members. The Company hires you in mid-2019 to assume direct responsibility for financial planning activities and provides you the title of Director of Financial and Cost Management. Your first assignment is to prepare a financial plan for the next three months, starting July 1, 2019. Since you are anxious to make a favorable impression on Ms. McMillan, the President of TABS, you immediately begin to assemble relevant information.
Ms. McMillan 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. McMillan and, consequently, she wants to ensure that sufficient cash will be available to accommodate TABS expected growth. Thus, on your first day, the President meets with you to emphasize why the Company must thoroughly assess the impact of TABS planned growth on cash flow. She would like to present the financial plan to TABS board of directors and has requested that you construct a financial model that can be used to perform sensitivities and will address questions from top management and board members.
Other Company Information
Thus, you consult with Jeffrey Cooper, the sales manager, and an outside market researcher. Jeffrey has studied sales and economic trends, as well as changes within the highly competitive handheld device computing industry to establish the unit sales forecast, which is presented in
Exhibit 1 . 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 is expected to 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 only 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 TABS policy is to never stock out of its inventory, and potentially forfeit market share to competitors, the Company maintains fairly high inventory levels. Therefore, desired ending inventories are equal to 75% of the next month’s sales in units. Prior to June, TABS sold only the basic model (BASE) at a price of $215 per unit. The BASE model costs TABS $145 each from a contracted, overseas manufacturer (the supplier) and it pays for purchases as follows: 50% in the month of purchase and the remaining 50% the following month. In June, TABS began carrying a premium model (PREM), which sells for $350 per unit and costs $200 from the overseas manufacturer. The current 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). Inventory is assumed to be sold on a first-in, first-out (FIFO) method.

EXHIBIT 1 TABLETS, Inc.'s Sales Forecast and Other Sales Information Unit Sales Total BASE PREM Actual Sales Volume April May June 7,650 8.400 9.200 7.650 8.400 7.360 n/a n/a 1.840 Forecast Sales Volume July August September 10.200 11,400 12.700 7,956 8,550 9,017 2.244 2.850 3,683 October 13,200 9,240 3,960 0 Sales Mix > Sales Mix % BASE Sensitivity 80% 20% 2% 78% 22% 3% 75% 25% 4% 71% 29% 1% 70% 30% PREM Sales Price Sales Price BASE PREM Sensitivity $ 215 $ 350 -2.00% $ 211 $ 343 -2.00% $ 206 $ 336 -2.00% $ 202 $ 329 -2.00% $ 198 $ 323 Unit Cost BASE PREM $ 145 $ 200 $ 145 $ 200 $ 145 $ 200 $ 145 $ 200 $ 145 $ 200

The company’s monthly operating expenses (organized by cost behavior) are provided in Exhibit 2. 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 $100,000 cash. Since the first month of depreciation expense will be negligible for this asset purchase, ignore a depreciation calculation. The Company, which is privately owned with Ms. McMillan as the majority shareholder, declares dividends of $20,000 at the end of each quarter, payable in the first month of the following quarter. TABS actual balance sheet at June 30 is provided at Exhibit 3 below.

Although TABS currently has no debt financing on its balance sheet, the Company has recently established a revolving line-of-credit through which it can borrow from Fifth Fourth Bank at 4% annual interest. For simplicity, assume that interest expense is recognized during the month incurred, while cash payments for interest occur one month in arrears. Required borrowings are made at the beginning of a month, and repayments at the end of a month in any dollar amount. TABS expects to use any excess cash to pay off loan principal as rapidly as possible. However, the Company also desires a minimum ending cash balance each month of $40,000 to meet regular operating expenses. Assume no tax consequences for the entire project.
Industry Benchmarks
One of Donna McMillan’s priorities as President is to ensure that TABS remains highly competitive within the industry and she has contacted a benchmarking consultant to provide data regarding the performance level of other companies within the industry. Selected benchmarking data are provided in Exhibit 4. The consultant recommends that TABS use this benchmarking data to assess its competitive position and for achieving ongoing operational improvements. Selected benchmarking data for assessing profitability and working capital management, relative to competing firms, is provided below.


For average sales per day use Total Quarterly Sales ÷ 90 days
For average inventory or average total assets take [(June 30 balance + Sept 30 balance) ÷ 2]
Inventory & Asset Turnover must be annualized – multiply the quarterly numerator figure by 4.
Ms. McMillan requests that you, as part of the financial plan, assess TABS performance relative to its key competitor, as well as the industry for the selected financial metrics. She also desires recommendations for improvements where the metrics reveal sub-par performance for TABS.

Create the following spreadsheet with Unit sales, Unit Volume Sensitivity, Forecast, Overall Sales Mix Sensitivity. Operating Assumptions, Operating expenses, Balance Sheet as of June 30, Budget Schedule, Projected Statements (income, cash, balance) ,

Answer 1

I have prepared Tables Inc's Sales Plan, Schedule of projected cash collections from sales and Accounts Receivable balance on each month end by using the information provided in the table Exhibit 1.

2 3 1.Sales Forecast for the month of July, August, September and October Sales Sales Sales Mix Volume Price Sales Revenue 7956 78% 22% 211 1678716 343 769692 2244 10200 2448408 8550 206 1761300 75% 25% 336 957600 Month & 5 Products 6 July 7 BASE 8 PREM 9 Total 10 August 11 BASE 12 PREM 13 Total 14 September 15 BASE 16 PREM 17 Total 18 October 19 BASE 20 PREM 21 Total 22 2850 11400 2718900 71% 9017 202 329 29% 3683 12700 1821434 1211707 3033141 70% 9240 198 1829520 1279080 30% 3960 323 13200 3108600 47500 11309049 23 Grand Total 24

2. Schedule of Projected cash collections from sales 3.Account Receivable Balance month end July August September October Total Accounts Receivable Balance as on month end 31, July 2626646 (June & July Sales Receivable) 31, August 2909642 (July & August Sales Receivable) 30, September 3242183|( August & Sept Sales Receivable) 31, October 3396822 (Sept & October Sales Receivable) 541800 1113200 489682 6 April Sales 7 May Sales 8 June Sales 9 July Sales 10 August Sales 11 September Sales 12 October Sales 13 Total 667920 1224204 543780 0 0 734522 1359450 606628 541800 0 1781120 0 2448408 815670 2718900 1516571| 2123199 621720 621720 2953961 10235147| Note: refer workings of cash collections of each month's sale 2144682 2435904 2700601 14

17 Workings: 18 Note: Every month's sale collections are made 20% by the month end, 50% in the month following the sale 19 and 30% in the second month following the sale 20 Collections 20% 50% 30% Sales in April in May in June Sales April Sales of 7650 units @ 215 1644750 328950 822375 493425 August Sales 2718900 21 Collections 20% 50% in August | in Sept 30% in Oct 5 43780 13594501 815670 24 25 Sales 3033141 in Sept 606628 in Oct 1516571 in Nov 909942|| 26 Sales september Sales in May in June in July 1806000 361200903000541800 Sales 3108600 in Oct 621720 in Nov 1554300 in Dec 932580 Sales in June in July | in August October Sales May Sales of 8400 units 27 @ 215 28 29 30 June Sales 31 7360 units @215 32 1840 units @ 350 33 Total 34 35 36 July Sales 1582400 316480 7912001 6440001 128800322000 2226400 445280 1113200 474720 193200 667920 Sales 2448408 in July 489682 in August 1224204 in Sept | 734522 37 38