Lantern’s summer workbook

Fictional case

It is July 2026. Lantern Coffee Co.’s online shop has just relaunched with free delivery on orders over $35, and Ana Lim wants a workbook that answers her questions before she commits to the summer: which spring orders actually made money, how her catering prices hang together, how many orders a month the relaunched shop needs, and how much of each coffee to order from Kilnworth Roasting for the first fortnight of July. You are building that workbook. Lantern, Ana, Kilnworth and every figure are fictional.

You can work in the worksheets on this page, or in Excel: download Lantern’s workbook (the same data on separate sheets: Orders, Products, Bands, Events, Stock plan and Shop model). If you work in Excel, type your answers to the questions below; for the formula tasks, enter your formula in the worksheet here so it can be checked.

What you have:

Orders: the shop’s 231 orders for 1 March – 30 June 2026 after B02’s cleaning (order value $9,096.15). Subscription orders carry 10% off; other discounts are a $5 promotion code; LC-10144 is a 120-bag office order with 10% off.
Products: Kilnworth Roasting’s spring price list to Lantern (unit_cost, what Lantern pays per bag or box) and Lantern’s shop price for each of the 11 products, with each product’s line (Blend, Single origin, Decaf, Cold brew, Gift).
Bands: the July delivery rule as a lookup table: orders up to $35.00 pay $4.95; orders from $35.01 travel free.
Events: Lantern’s 76 catering events in 2026 with each event’s guests, hours of service, whether a pastry platter was added, the number of baristas sent and the invoice.
Shop model: one month of the relaunched shop (B01’s July figures and the cost rates from Lantern’s books), and Stock plan: Kilnworth’s allocation, Ana’s stock budget, her packing time and her sales forecast for 1–14 July.

What you will produce

An order sheet with three new columns: each order’s unit cost, gross margin and July delivery charge, all calculated by formulas that fill down.
Ana’s answers: the spring gross margin and margin rate, where the margin comes from (product lines, regions, subscriptions) and how many spring orders would have paid delivery.
A reading of the catering regression and a price for a new quote.
The relaunched shop’s break-even order count, a profit table and the average order value Ana needs for her profit goal.
The July stock order from Solver, with the limit that binds, what five more kilograms would be worth and what Ana’s House Blend rule costs her.
A short memo recommending what Ana’s shop dashboard should show.

Loading the project…