INFORMATION FOR K-PEK INC. K-PEK Inc. is a company that sells a highly specialized product, an: "Advanced Al system and astronavigation recursive analyzer module." A small Japanese engineering firm produces the item exclusively for K-PEK Inc; Therefore, K-PEK Inc. has no manufacturing related costs. PRODUCT COSTS In Nov of 2022, each module costs K-PEK Inc. $700. Per an existing contract, the cost of each unit is scheduled to increase by 5% on May 1, 2023. Rare carth minerals are used in production of the item. Analysis has shown that there will be a shortage of these rare earth minerals due to a spike in their demand from production of electric vehicles. The cost is anticipated to increase by an additional 12% on Sept 1, 2023 PRODUCT SALES K-PEK Inc. sells cach Module for $1,700 per unit. Projected Sales units: Year 2022 2023 Month Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Dec Jan Amt 55 S2 32 50 60 75 78 72 63 SI 46 38 78 65 47 2024 To offset increasing costs of modules, the company plans to raise the sales price to $1,980 per unit beginning Sept 1, 2022. The sales forecast (i.c., estimated sales in units) takes this price increase into account CREDIT SALES Monthly sales are 32% cash sales, 68% credit sales. 27% of credit sales are collected in the month of sale, 68% are collected the following month, and 4% are collected the 20 month after sale. The remaining receivables are deemed uncollectible at the end of the 2 month after sale. Bad debts are written off in the month the debt is deemed uncollectible PRODUCT INVENTORY The firm's policy regarding inventory is to maintain their stock (1.e. have in ending inventory) at 12% of the forecasted sales in units for the next month. K-PEK Inc. uses the first-in, first-out (FIFO) method in accounting for inventories 43% of the inventory purchases are paid in the month of purchase with the remaining 57% paid the following month EQUIPMENT A Note payment of $185,000 for facilities and equipment previously purchased is due in January, then payments of $40,000 per month February through October 2023. There are no Note Payables at the end of 2023. OPERATING EXPENSES Monthly Cash expenses are paid when incurred Salary and Wage Exp $ 8,500 Sales Commissions Exp 5% of sales revenue Rent Exp $ 3.800 Gen & Admin Variable Exp 3% of sales revenue Supplies Exp $ 480 Gen & Admin Fixed Exp $ 800 Gen & Admin Depreciation Exp $ 960 (Cash expenses are paid when incurred) CASH MANAGEMENT The company must maintain a minimum cash balance of $75,000. Borrowing can make up shortfalls. For simplicity, assume that the bank will only lend (and accept repayments) in $1,000 increments. (Ignore interest on the loans, but minimize the amount borrowed and pay off any loans as soon as possible.) DIVIDENDS Dividends of $60,000 are paid in Nov. BALANCE SHEET Balance Sheet balances as of December 31, 2022 Cash $ 235,000 Accounts Receivable $ 47,061 Supplies $ 850 Merchandise Inventory $ 2,688 Buildings and Equipment $ 995,900 Accumulated Depreciation $ 380,000 Accounts Payable $ 19,790 Notes Payable S 545,000 Capital Stock $ 200,000 Retained Earnings S 136,708 *same balance in 2023 CHECK FIGURES Cash Collections Budget Purchase Budget Operating Expense Budget Cash Budget Budgeted Income Statement Budgeted Balance Sheet Total Cash Collections for January. Total Cash Collections 2023: Bad Debt Exp for January: Bad Debt Exp, Total 2023: Cost of Purchases for Dec 2023: Cash disbursements for Op Exp for Dec: Total Cash available, Summary: Total Cash Disbursements, Summary: Ending Cash Balance for Dec: Cost of Goods Sold Total: Gross Profit, Total: Net Income, Total: Total Assets Dec 2023: $ 70,815 $ 1,237,155 $ 636 $ 7,928 $ $1,730 $ 23,876 $ 1,472,155 $ 1,393,584 $ 78,571 $ 531,992 $ 735,168 451,387 $ $ 757,581 Objective: To understand and apply the basic concepts of profit planning and gain experience in MS Excel. Required: You need to prepare a comprehensive 12-month budget, including supporting schedules and a report for the period January 1, 2023 to December 31, 2023 for K-PEK Inc. This project includes a complete budget template that makes up the Master Budget. Notes and Tips 1. All parts must be completed before I grade the project, but you can ask questions. 2. The completed Master Budget must be uploaded to Blackboard. I will not accept email submissions The budget must be turned in as an Excel workbook. It is fine if you open the template and use Google Sheets or some other spreadsheet software to complete it, as long as you convert it back to Excel before you upload it to Blackboard. If you do use some other software, make sure all your cells are still formulas 3. If you need to upload a corrected copy or make changes after uploading, Blackboard will allow you to upload it multiple times. I will only look at the last upload unless you tell me otherwise. 4. The template I have prepared must be used as-is. DO NOT ADD/DELETE COLUMNS OR ROWS!!! DO NOT CHANGE ANY CELL FORMATTING! 5. This project demonstrates your proper use of Excel (spreadsheets). You may only input a "hard number" into a pink cell. All yellow cells must be formulas (no numbers should be included within formula. Every variable in the formula should use appropriate cell referencing instead of a number.). 6. You can toggle the spreadsheet display between "normal" and "show formulas" with Ctrl+ (the button left of the number 1 on a standard keyboard) 7. I recommend constructing the formulas for one month and then copying the formulas over to the remaining months using copy/paste "fx only'. Try to keep cell formats as they currently are format; i.e. S.%, borders, are preset. 8. The budget template and a copy of this instruction sheet are located on the course materials page under Project 2. Make sure you download the file as Excel and then open the file through Excel (so it doesn't open in your web browser). If you want to use Excel but do not have it, you can use the school computers which all do or you can also it is free for students (Microsoft Office). 9. I am providing check figures for you to check your accuracy at different points. 10. The total column at the right of each table should be annual totals if that is appropriate. Some cells don't make sense to have a total, such as the price per unit row. Another example is Cash disbursement total column: the first line beginning cash wouldn't be total of all the beginning months, it would be the Jan "amount. Use logic to determine what amount should be reported in these total columns, NOTE: some budgets have 2022 months in them, DO NOT include those months in the Totals for 2023 Balget Imam w Sere +Te URI NATY 20:21 mat P LIE GAL H21 Lad Ready Auge STUDENT BLANK TEMPLATE Match of up GOL Spanis de MACER KINGK Galle Conforma Forming Table CH 0 M 1. F FIES Select DEN A Da A ME 1 W www. C LARM Ready Impuly STUDENT BLANK TEMPLATE SEMARY Pertem 1000 e 3000 het All Tomatinga [PAYPAY 4 10 Ser d A
